You want to execute some code each time a particular PL/SQL package is instantiated in a session.
Create an initialization block for the package in question. By doing so, you will have the ability to execute code each time the package is initialized. The following example shows the same package that was constructed in this article. However, this time the package contains an initialization block.
CREATE OR REPLACE PACKAGE BODY process_employee_time IS PROCEDURE grant_raises (pct_increase IN NUMBER, upper_bound IN NUMBER) as CURSOR emp_cur is SELECT employee_id, first_name, last_name FROM employees; BEGIN -- loop through each record in the employees table FOR emp_rec IN emp_cur LOOP DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name); increase_wage(emp_rec.employee_id, pct_increase, upper_bound); END LOOP; END grant_raises; PROCEDURE increase_wage (empno_in IN NUMBER, pct_increase IN NUMBER, upper_bound IN NUMBER) AS emp_count NUMBER := 0; emp_sal employees.salary%TYPE; Results VARCHAR2(50); BEGIN SELECT count(*) INTO emp_count FROM employees WHERE employee_id = empno_in; IF emp_count > 0 THEN -- IF EMPLOYEE FOUND, THEN OBTAIN RECORD SELECT salary INTO emp_sal FROM employees WHERE employee_id = empno_in; IF emp_sal < upper_bound AND round(emp_sal + (emp_sal * pct_increase), 2) < upper_bound THEN UPDATE employees SET salary = round(salary + (salary * pct_increase),2) WHERE employee_id = empno_in; results := 'SUCCESSFUL INCREASE'; ELSE results := 'EMPLOYEE MAKES TOO MUCH, DECREASE RAISE PERCENTAGE'; END IF; ELSE Results := 'NO EMPLOYEE FOUND'; END IF; DBMS_OUTPUT.PUT_LINE(results); END increase_wage; BEGIN DBMS_OUTPUT.PUT_LINE('EXECUTING THE INITIALIZATION BLOCK'); END;
The initialization block in this example is the last code block within the package body. In this case, that block lies in the final three lines.
How It Works
The initialization block for the package in the solution displays a line of text to indicate that the initialization block has been executed. The initialization block will execute once per session, the first time the package is used in that session. If you were to create this package in your session and invoke one of its members, you would see the message print. Although an initialization message is not very useful, there are several good reasons to use an initialization block. One such reason is to perform a query to obtain some data for the session.