A package in a database is a unit that consists of related subprograms and the cursors and variables that are used by the subprograms.
OceanBase Database provides system packages that extend the capabilities of databases. You can use system packages when you create applications. For example, you can use the DBMS_RANDOM package to generate a random value in a simplified manner.
Advantages of packages
Procedural language (PL) packages facilitate application development through the following advantages:
Encapsulation
Packages have similar features to object-oriented programming languages. You can encapsulate smaller units such as stored procedures, variables, and custom data types in a package to organize and manage these units in an easier way. A package is roughly equivalent to a C++ or Java class in which a variable of the package can be compared to a member variable of the class, and a stored procedure and a function can be compared to a class method. Package encapsulation also simplifies privilege management. A user with the access privilege on a package can access all structures in the package.
Improved data security
You can set the variables, cursors, and stored procedures in a package as public or private units. Public units can be directly accessed by package users, whereas private units cannot.
Assume that a package consists of three variables and five stored procedures. You can set two variables and three stored procedures as private units, so that these variables and stored procedures can be accessed only within the package body. In this case, package users can access only the remaining one variable and two stored procedures in the package.
Create a package
To create a package, you must specify the package header and body. You must declare public structures in the package header and define both private and public structures in the package body.
In the following example, the students table and the students_adm package are created. The package consists of two stored procedures. When a student is enrolled, the students_adm.stu_entrance procedure is called to write information about the student to the students table. When a student graduates, the students_adm.graduate procedure is called to delete information about the student from the students table.
CREATE TABLE students (id INT, name VARCHAR2(100), class INT, grade INT, dorm VARCHAR2(100), year INT);
CREATE OR REPLACE PACKAGE students_adm AS
PROCEDURE stu_entrance(id INT, name VARCHAR2, class INT, grade INT, dorm VARCHAR2, year INT);
PROCEDURE graduate(id INT);
END students_adm;
/
CREATE OR REPLACE PACKAGE BODY students_adm AS
PROCEDURE stu_entrance(id INT, name VARCHAR2, class INT, grade INT, dorm VARCHAR2, year INT) AS
BEGIN
INSERT INTO students VALUES (id, name, class, grade, dorm, year);
END;
PROCEDURE graduate(id INT) AS
BEGIN
DELETE from students s WHERE s.id = id;
END;
END students_adm;
/
obclient> CALL students_adm.stu_entrance(1001, 'Curry', 5, 10, '1-1-3', 12);
Query OK, 0 rows affected
obclient> SELECT * from students WHERE id = 1001;
+------+-------+-------+-------+-------+------+
| ID | NAME | CLASS | GRADE | DORM | YEAR |
+------+-------+-------+-------+-------+------+
| 1001 | Curry | 5 | 10 | 1-1-3 | 12 |
+------+-------+-------+-------+-------+------+
1 row in set
obclient> CALL students_adm.graduate(1001);
Query OK, 0 rows affected
obclient> SELECT * from students WHERE id = 1001;
Empty set