A package consists of a package specification (PACKAGE SPECIFICATION) and a package body (PACKAGE BODY). The package specification defines the package header, and the package body defines the package body.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
The package specification (PACKAGE SPECIFICATION) declares the data types, variables, constants, cursors, subprograms, and exception handling in the package. These elements are public elements of the package. The package body (PACKAGE BODY) 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 of the database.
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 an 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. The package contains a record variable named 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:
```sql
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 of a package
To call variables, constants, functions, or methods in a package, you must specify the package name in the object name and separate it from the element name with a period (.). That is, the syntax for calling a public element of a package is as follows: package_name.element_name.
The following example calls the functions of 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!
900---Administration---Beijing The record is deleted successfully!
