Did you notice 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;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.
