The CREATE PACKAGE BODY statement is used to create or replace the body of a stored package.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
A package is a collection of related stored procedures, functions, and other program objects that are stored as a single unit in the database. The package body defines these objects. The package header, defined in the CREATE PACKAGE statement, has already declared these objects.
Prerequisites
To create or replace a package in your schema, you must have the CREATE PACKAGE system privilege. To create or replace a package in another user's schema, you must have the CREATE ANY PACKAGE system privilege.
Syntax
The syntax of create_package_body is as follows:
CREATE [ OR REPLACE ] PACKAGE BODY
plsql_package_body_source
where:
The syntax of
plsql_package_body_sourceis as follows:[ schema. ] package { IS | AS } declare_section [ initialize_section ] END [ package_name ] ;The syntax of
initialize_sectionis as follows:BEGIN statement... [ EXCEPTION exception_handler... ]
Semantics
Syntax |
Keyword or syntax node |
Description |
|---|---|---|
| create_package_body | OR REPLACE | Recreates the package body (if it exists) and recompiles it. Users who were granted privileges to the package before the package body was redefined can still access the package without needing to be granted the privileges again. |
| plsql_package_body_source | schema | The schema where the package is located. The default value is the current user's schema. |
| plsql_package_body_source | package_name | The name of the package to be created. |
| plsql_package_body_source | declare_section | Defines each cursor and subprogram declared in the package header. The declarations and definitions must match exactly, except for whitespace. |
| initialize_section | -- | Initializes variables and sets up one-time steps. |
Examples
Create the body of the obdemo_pack package to implement the declared package header.
CREATE OR REPLACE PACKAGE BODY obdemo_pack
IS
FUNCTION add_obdept(dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)
RETURN NUMBER
IS
deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(deptno_remaining, -1); -- -1 is the error code for violating a unique constraint
BEGIN
INSERT INTO obdept VALUES(dept_no, dept_name, location);
IF SQL%FOUND THEN
RETURN 1;
END IF;
EXCEPTION
WHEN deptno_remaining THEN
RETURN 0;
WHEN OTHERS THEN
RETURN -1;
END add_obdept;
FUNCTION remove_obdept(dept_no NUMBER)
RETURN NUMBER
IS
BEGIN
DELETE FROM obdept WHERE deptno=dept_no;
IF SQL%FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END remove_obdept;
PROCEDURE query_obdept (dept_no IN NUMBER)
IS
BEGIN
SELECT * INTO obDeptRec FROM obdept WHERE deptno=dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No department with code'||dept_no||'exists in the database.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Program error!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
END query_obdept;
BEGIN
Null;
END obdemo_pack;
/