CREATE PACKAGE BODY

Purpose

Prerequisites

To create a package in your own schema, you must have CREATE PROCEDURE system privilege. To create a package in another user's schema, you must have CREATE ANY PROCEDURE system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, you can only create a package in another user's schema if your DBMS label dominates the creation label of the other user.

To create a package, you must be using Oracle7 with PL/SQL installed. For more information, see PL/SQL User's Guide and Reference.

Syntax

Keywords and Parameters

OR REPLACE

recreates the package body if it already exists. You can use this option to change the body of an existing package without dropping, recreating, and regranting object privileges previously granted on it. If you change a package body, Oracle7 recompiles it. For information on recompiling package bodies, see the ALTER PACKAGE BODY command .

Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges.

schema

is the schema to contain the package. If you omit schema, Oracle7 creates the package in your current schema.

package

is the name of the package to be created.

pl/sql_package_ body

is the package body. The package body can declare and define program objects. Package bodies are written in PL/SQL. For information on PL/SQL, including writing package bodies, see PL/SQL User's Guide and Reference.

To embed a CREATE PACKAGE 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.

Packages

Example

This SQL statement creates the body of the EMP_MGMT package:

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 Oracle7 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:

• the functions HIRE and CREATE_DEPT

• the procedures REMOVE_EMP, REMOVE_DEPT, INCREASE_SAL, and INCREASE_COMM

Since these objects are defined in the package body, you can change their definitions without causing Oracle7 to invalidate dependent schema objects. For example, if you subsequently change the definition of HIRE, Oracle7 need not recompile INCREASE_ALL_COMMS before executing it.

The package body in this example also declares private program objects, the variables TOT_EMPS and TOT_DEPTS. Since these objects are declared in the package body rather than the package specification, 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, since the function CREATE_DEPT is part of the package, CREATE_DEPT can change the value of TOT_DEPTS.

Related Topics