Did you know that Oracle PL/SQL packages can have an initialization section? The initialization part of a package is run only once, the first time you reference the package. Here is an example:
CREATE PACKAGE emp_actions AS /* Declare externally callable subprograms. */ FUNCTION hire_employee ( ename VARCHAR2, job VARCHAR2, mgr REAL, sal REAL, comm REAL, deptno REAL) RETURN INT; END emp_actions; CREATE PACKAGE BODY emp_actions AS number_hired INT; -- visible only in this package /* Fully define subprograms specified in package. */ FUNCTION hire_employee ( ename VARCHAR2, job VARCHAR2, mgr REAL, sal REAL, comm REAL, deptno REAL) RETURN INT IS new_empno INT; BEGIN SELECT empno_seq.NEXTVAL INTO new_empno FROM dual; INSERT INTO emp VALUES (new_empno, ename, job, mgr, SYSDATE, sal, comm, deptno); number_hired := number_hired + 1; RETURN new_empno; END hire_employee; BEGIN -- initialization part starts here INSERT INTO emp_audit VALUES (SYSDATE, USER, 'EMP_ACTIONS'); number_hired := 0; END emp_actions;
Remember, the initialization part of a package is run just once, the first time you reference the package. So, in the last example, only one row is inserted into the database table
emp_audit. Likewise, the variable
number_hired is initialized only once.
Every time the procedure hire_employee is called, the variable
number_hired is updated. However, the count kept by
number_hired is session specific. That is, the count reflects the number of new employees processed by one user, not the number processed by all users.