oracle tips & tricks

Oracle Tips & Tricks

 
Monday, 17 March 2008
Oracle Package Initialization
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.

Labels:

posted by popo @ 23:53  
0 Comments:
Post a Comment
<< Home
 
 
Search

Google
Previous Post
Category
Blogrush
Blogroll
Legal
Archives
Template by
Free Blogger templates