|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
CREATE LIBRARY to CREATE SPFILE, 8 of 16
BODY statement to create the body of a stored package, which is an encapsulated collection of related procedures, stored functions, and other program objects stored together in the database. The body defines these objects.
Packages are an alternative to creating procedures and functions as standalone schema objects.
Before a package can be created, the user
SYS must run a SQL script commonly called
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.
REPLACE to re-create 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.
Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges.
ALTER PACKAGE for information on recompiling package bodies
Specify the schema to contain the package. If you omit
schema, Oracle creates the package in your current schema.
Specify the name of the package to be created.
Specify the package body, which can contain PL/SQL subprogram bodies or call specifications (declarations of a C or Java routine expressed in PL/SQL).
This SQL statement creates the body of the
emp_mgmt package created in "CREATE PACKAGE Example" (PL/SQL is shown in italics):
CREATE PACKAGE BODY emp_mgmt AS tot_emps NUMBER; tot_depts NUMBER; FUNCTION hire (last_name VARCHAR2(20), job_id VARCHAR2(10), manager_id NUMBER(6), salary NUMBER(8,2), commission_pct NUMBER(2,2), department_id NUMBER(4) RETURN NUMBER IS new_empno NUMBER(4); BEGIN SELECT employees_seq.NEXTVAL INTO new_empno FROM DUAL; INSERT INTO employees VALUES (new_empno, last_name, job_id, manager_id, salary, commission_pct, department_id, tot_emps := tot_emps + 1; RETURN(new_empno); END; FUNCTION create_dept(department_id NUMBER(4), location_id NUMBER(4)) RETURN NUMBER IS new_deptno NUMBER(4) BEGIN SELECT departments_seq.NEXTVAL INTO new_deptno FROM dual; INSERT INTO departments VALUES (new_deptno, department_id, location_id); tot_depts := tot_depts | 1; RETURN(new_deptno); END; PROCEDURE remove_emp (employee_id NUMBER(6)) IS BEGIN DELETE FROM employees WHERE employees.employee_id = remove_emp.employee_id; tot_emps := tot_emps - 1; END; PROCEDURE remove_dept(department_id NUMBER ($)) IS BEGIN DELETE FROM departments WHERE departments.department_id = remove_dept.department_id; tot_depts := tot_depts - 1; SELECT COUNT(*) INTO tot_emps FROM employees; /* In case, oracle deleted employees from the EMPLOYEES table to enforce referential integrity constraints, reset the value of the variable TOT_EMPS to the total number of employees in the EMPLOYEES table. */ END; PROCEDURE increase_sal (employee_id NUMBER(6), sal_incr NUMBER) IS curr_sal NUMBER(7,2); BEGIN SELECT salary INTO curr_sal FROM employees WHERE employees.employee_id = increase_sal.employee_id; IF curr-Sal IS NULL THEN RAISE no_sal; ELSE UPDATE employees SET salary = salary + sal_incr WHERE employee_id = employee_id; ENDIF; END; PROCEDURE increase_comm(employee_id NUMBER(6), comm_incr NUMBER) IS curr_comm NUMBER(7,2); BEGIN SELECT commission_pct INTO curr_comm FROM employees WHERE employees.employee_id = increase_comm.employee_id IF curr_comm IS NULL THEN RAISE no_comm; ELSE UPDATE employees SET commission_pct = commission_pct + comm_incr; END IF; END; END emp_mgmt;
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 tot_depts.