Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Purpose
Use the CREATE
PACKAGE
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 package body defines these objects. The package specification, defined in an earlier CREATE
PACKAGE
statement, declares these objects.
Packages are an alternative to creating procedures and functions as standalone schema objects.
See Also:
CREATE FUNCTION and CREATE PROCEDURE for information on creating standalone functions and procedures
CREATE PACKAGE for a discussion of packages, including how to create packages
"Examples" for some illustrations
ALTER PACKAGE for information on modifying a package
DROP PACKAGE for information on removing a package from the database
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 CREATE
PROCEDURE
system privilege. To create a package in another user's schema, you must have CREATE
ANY
PROCEDURE
system privilege.
To embed a CREATE
PACKAGE
BODY
statement inside an Oracle Database precompiler program, you must terminate the statement with the keyword END-EXEC
followed by the embedded SQL statement terminator for the specific language.
See Also:
PL/SQL User's Guide and ReferenceSyntax
create_package_body::=
Semantics
Specify OR
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, then Oracle Database recompiles it.
Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges.
See Also:
ALTER PACKAGE for information on recompiling package bodiesschema
Specify the schema to contain the package. If you omit schema
, then the database creates the package in your current schema.
package
Specify the name of the package to be created.
pl/sql_package_body
Specify the package body, which can contain PL/SQL subprogram bodies or call specifications, which are declarations of a C or Java routine expressed in PL/SQL.
See Also:
Oracle Database Application Developer's Guide - Fundamentals for more information on writing PL/SQL or C package program units
Oracle Database Java Developer's Guide for information on Java package program units
"Restrictions on User-Defined Functions" for a list of restrictions on user-defined functions in a package
Examples
Creating a Package Body: Example This SQL statement creates the body of the emp_mgmt
package created in "Creating a Package: Example". The PL/SQL is shown in italics:
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS tot_emps NUMBER; tot_depts NUMBER; FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER, salary NUMBER, commission_pct NUMBER, department_id NUMBER) RETURN NUMBER IS new_empno NUMBER; BEGIN SELECT employees_seq.NEXTVAL INTO new_empno FROM DUAL; INSERT INTO employees VALUES (new_empno, 'First', 'Last','first.last@oracle.com', '(123)123-1234','18-JUN-02','IT_PROG',90000000,00, 100,110); tot_emps := tot_emps + 1; RETURN(new_empno); END; FUNCTION create_dept(department_id NUMBER, location_id NUMBER) RETURN NUMBER IS new_deptno NUMBER; BEGIN SELECT departments_seq.NEXTVAL INTO new_deptno FROM dual; INSERT INTO departments VALUES (new_deptno, 'department name', 100, 1700); tot_depts := tot_depts + 1; RETURN(new_deptno); END; PROCEDURE remove_emp (employee_id NUMBER) 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; END; PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS curr_sal NUMBER; 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 + salary_incr WHERE employee_id = employee_id; END IF; END; PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS curr_comm NUMBER; 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:
The functions hire
and create_dept
The procedures remove_emp
, remove_dept
, increase_sal
, and increase_comm
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 increase_comm
procedure.
These objects are defined in the package body, so you can change their definitions without causing Oracle Database to invalidate dependent schema objects. For example, if you subsequently change the definition of hire
, then the database 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
. 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
.