A program package consists of a package specification and a package body. The definition of a program package consists of two parts: definition of the package specification and definition of the package body.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
The package specification declares the public elements of the package, such as the data types, variables, constants, cursors, subprograms, and exceptions. The package body is the specific implementation of the package specification, and defines the cursors and programs declared in the package specification. The package body 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 database dictionary views USER_SOURCE, ALL_SOURCE, and DBA_SOURCE.
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 describe the privileges used by the application to call the 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 the obDeptRec record variable, 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 declared package specification:
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 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;
/
Query OK, 0 rows affected
In OceanBase Database, you can create synonyms for program packages. 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 procedures of a package
To call the variables, constants, functions, and methods in a program package, you need to include the package name in the element name and separate the names with a period (.). Specifically, a public element in a package is called in the format of package name.element name.
The following example calls the functions in the obdemo_pack package to add data to, query, and modify the obdept table, and uses the obDeptRec record variable in the obdemo_pack package to display the query result:
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('Record added!') ');
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('Record deleted!') ');
END IF;
END IF;
END;
/
Query OK, 0 rows affected
Record added!
900---Administration---Beijing
Record deleted!