You can use the CREATE PACKAGE BODY statement to create or replace the body of a program package.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
The package is an encapsulated collection of related stored procedures, functions, and other program objects that are stored as one unit in the database. The package body defines these objects. The package specification defined in the CREATE PACKAGE statement has declared these objects.
Prerequisites
To create or replace a program package in your own schema, you must have the CREATE PACKAGE system privilege. To create or replace a program package in the schema of other users, 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
The following shows more details:
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 | Re-creates this package body (if any) and recompiles it. Before the package body is redefined, users granted the access privilege can still access this program package without the need to obtain the access privilege again. |
| plsql_package_body_source | schema | The name of the schema where the program package is located. The default value is your schema. |
| plsql_package_body_source | package_name | The name of the program package to be created. |
| plsql_package_body_source | declare_section | Defines the declaration of each cursor and subprogram in the package specification. The declaration and definition must exactly match except spaces. |
| initialize_section | - | Initializes variables and sets one-time steps. |
Examples
Create a package body for the obdemo_pack program package to implement the declared package specification.
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 returned when the unique constraint condition is violated.
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 numbered '||dept_no||' in the database.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Program runtime error!')
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
END query_obdept;
BEGIN
Null;
END obdemo_pack;
/