A package consists of a package specification (PACKAGE SPECIFICATION) and a package body (PACKAGE BODY). The package specification and package body are defined separately.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
The package specification section declares data types, variables, constants, cursors, subprograms, and exception handling in the package. These elements are public elements of the package. The package body section implements the package specification and defines the cursors and subprograms declared in the package specification. In the package body, you can also declare private elements of the package.
The package specification and package body are compiled separately and stored as two separate objects in the USER_SOURCE, ALL_SOURCE, and DBA_SOURCE views.
Create a package
The syntax for creating a package specification is as follows:
CREATE [OR REPLACE] PACKAGE package_name
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS}
[public_type_declarations[public_type_declarations]...]
[public_cursor_declarations[public_cursor_declarations]...]
[public_variable_declarations[public_variable_declarations]...]
[function_declarations[function_declarations]...]
[procedure_specifications[procedure_specifications]...]
END [package_name]
The AUTHID CURRENT_USER and AUTHID DEFINER options specify the privilege mode used by the application when it calls a function.
The syntax for creating a package body is as follows:
CREATE [OR REPLACE] PACKAGE BODY package_name
{AS|IS}
[private_type_declarations[private_type_declarations]...]
[private_variable_declarations[private_variable_declarations]...]
[private_function_declarations[function_declarations]...]
[private_procedure_specifications[procedure_specifications]...]
[public_cursor_declarations[public_cursor_declarations]...]
[public_function_declarations[function_declarations]...]
[public_procedure_specifications[procedure_specifications]...]
BEGIN
PL Statement
END [package_name]
Examples
The following example creates a package named obdemo_pack, which contains a record variable obDeptRec, two functions, and a stored procedure.
obclient> CREATE TABLE obdept(
deptno NUMBER(10,0),
dname VARCHAR(15),
loc VARCHAR(20)
);
Query OK, 0 rows affected
obclient> CREATE OR REPLACE PACKAGE obdemo_pack
IS
obDeptRec obdept %ROWTYPE;
FUNCTION add_obdept(dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)
RETURN NUMBER;
FUNCTION remove_obdept(dept_no NUMBER)
RETURN NUMBER;
PROCEDURE query_obdept(dept_no IN NUMBER);
END obdemo_pack;
/
Query OK, 0 rows affected
The following example creates a package body that implements the package specification declared in the previous example:
obclient> 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 the 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 the code '||dept_no||' exists in the database.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Runtime error!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
END query_obdept;
BEGIN
Null;
END obdemo_pack;
/
Query OK, 0 rows affected
In OceanBase Database, you can also create synonyms for a package. Here is an example:
obclient> CREATE OR REPLACE SYNONYM syn_pag FOR obdemo_pack;
Query OK, 0 rows affected
obclient> CALL syn_pag.query_obdept('123');
Query OK, 0 rows affected
Call functions and methods in a package
To call variables, constants, functions, and methods in a package, you must specify the package name in the object name and separate the package name and the element name with a period (.). That is, the calling syntax for public elements of a package is package_name.element_name.
The following example calls functions in the obdemo_pack package to insert, query, and modify data in the obdept table. It also displays the queried database information by using the record variable obDeptRec in the obdemo_pack package.
obclient> DECLARE
Var NUMBER;
BEGIN
Var := obdemo_pack.add_obdept(900,'Administration', 'Beijing');
IF var =-1 THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
ELSIF var =0 THEN
DBMS_OUTPUT.PUT_LINE('The department record already exists!');
ELSE
DBMS_OUTPUT.PUT_LINE('The record is added successfully!');
obdemo_pack.query_obdept(900);
DBMS_OUTPUT.PUT_LINE(obdemo_pack.obDeptRec.deptno||'---'||obdemo_pack.obDeptRec.dname||'---'||obdemo_pack.obDeptRec.loc);
var := obdemo_pack.remove_obdept(900);
IF var =-1 THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
ELSIF var=0 THEN
DBMS_OUTPUT.PUT_LINE('The department record does not exist!');
ELSE
DBMS_OUTPUT.PUT_LINE('The record is deleted successfully!');
END IF;
END IF;
END;
/
Query OK, 0 rows affected
The record is added successfully!
900---Administration---Beijing
The record is deleted successfully!