|Oracle8i SQL Reference
Release 2 (8.1.6)
SQL Statements (continued), 18 of 20
To create the body of a stored package. A package is an encapsulated collection of related procedures, stored functions, and other program objects stored together in the database. The body defines these objects. For information on creating standalone functions and procedures, see "CREATE FUNCTION" and "CREATE PROCEDURE".
Packages are an alternative to creating procedures and functions as standalone schema objects. For a discussion of packages, including how to create packages, see "CREATE PACKAGE". For some illustrations, see "Examples".
Before a package can be created, the user SYS must run the SQL script
DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.
To create a package in your own schema, you must have
PROCEDURE system privilege. To create a package in another user's schema, you must have
PROCEDURE system privilege.
To embed a
BODY statement inside an Oracle precompiler program, you must terminate the statement with the keyword
END-EXEC followed by the embedded SQL statement terminator for the specific language.
re-creates the package body if it already exists. Use this clause to change the body of an existing package without dropping, re-creating, and regranting object privileges previously granted on it. If you change a package body, Oracle recompiles it. For information on recompiling package bodies, see "ALTER PACKAGE".
Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges.
is the schema to contain the package. If you omit schema, Oracle creates the package in your current schema.
is the name of the package to be created.
For a list of restrictions on user-defined functions in a package, see "Restrictions on User-Defined Functions".
This SQL statement creates the body of the
CREATE PACKAGE BODY emp_mgmt AS tot_emps NUMBER; tot_depts NUMBER; FUNCTION hire (ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) RETURN NUMBER IS new_empno NUMBER(4); BEGIN SELECT empseq.NEXTVAL INTO new_empno FROM DUAL; INSERT INTO emp VALUES (new_empno, ename, job, mgr, sal, comm, deptno, tot_emps := tot_emps + 1; RETURN(new_empno); END; FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2) RETURN NUMBER IS new_deptno NUMBER(4); BEGIN SELECT deptseq.NEXTVAL INTO new_deptno FROM dual; INSERT INTO dept VALUES (new_deptno, dname, loc); tot_depts := tot_depts + 1; RETURN(new_deptno); END; PROCEDURE remove_emp(empno NUMBER) IS BEGIN DELETE FROM emp WHERE emp.empno = remove_emp.empno; tot_emps := tot_emps - 1; END; PROCEDURE remove_dept(deptno NUMBER) IS BEGIN DELETE FROM dept WHERE dept.deptno = remove_dept.deptno; tot_depts := tot_depts - 1; SELECT COUNT(*) INTO tot_emps FROM emp; /* In case Oracle deleted employees from the EMP table to enforce referential integrity constraints, reset the value of the variable TOT_EMPS to the total number of employees in the EMP table. */ END; PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER) IS curr_sal NUMBER(7,2); BEGIN SELECT sal INTO curr_sal FROM emp WHERE emp.empno = increase_sal.empno; IF curr_sal IS NULL THEN RAISE no_sal; ELSE UPDATE emp SET sal = sal + sal_incr WHERE empno = empno; END IF; END; PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER) IS curr_comm NUMBER(7,2); BEGIN SELECT comm INTO curr_comm FROM emp WHERE emp.empno = increase_comm.empno IF curr_comm IS NULL THEN RAISE no_comm; ELSE UPDATE emp SET comm = comm + comm_incr; END IF; END; END emp_mgmt;
This package body corresponds to the package specification in the example of the "CREATE PACKAGE" statement earlier in this chapter. The package body defines the public program objects declared in the package specification:
These objects are declared in the package specification, so they can be called by application programs, procedures, and functions outside the package. For example, if you have access to the package, you can create a procedure
INCREASE_ALL_COMMS separate from the
EMP_MGMT package that calls the
These objects are defined in the package body, so you can change their definitions without causing Oracle to invalidate dependent schema objects. For example, if you subsequently change the definition of
HIRE, Oracle need not recompile
INCREASE_ALL_COMMS before executing it.
The package body in this example also declares private program objects, the variables
TOT_DEPTS. These objects are declared in the package body rather than the package specification, so they are accessible to other objects in the package, but they are not accessible outside the package. For example, you cannot develop an application that explicitly changes the value of the variable
TOT_DEPTS. However, the function
CREATE_DEPT is part of the package, so
CREATE_DEPT can change the value of