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 provides only MySQL mode.
A package is a collection of stored procedures, functions, and other program objects (stored as a 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 it was redefined can still access the package without needing to re-grant privileges. |
| 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 create. | | plsql_package_body_source | declare_section | Defines each cursor and subprogram declared in the package header. 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||' found 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;
/
```
Null;
END obdemo_pack;
/