Mastering Packages in GBase 8s Database

Packages? What packages? Backpacks or soup dumplings?

None of the above. We are talking about database packages. In GBase 8s database, packages provide features such as code encapsulation, information hiding, and subprogram overloading, improving code maintainability and reusability. This article explores the concept, creation, usage, and deletion of packages in GBase 8s, helping you gain a deeper understanding.

Package Concepts

A package is a collection of related PL/SQL program elements such as procedures, functions, variables, constants, types, cursors, and exceptions. Packages exhibit object-oriented design characteristics by encapsulating these PL/SQL elements. Acting like a container or namespace, a package groups logically related procedures, functions, variables, constants, types, cursors, and exceptions together, providing well-organized units for developers building large, complex applications. Once defined, applications can access different functional units through the package without worrying about scattered subprograms leading to loose code. Packages help simplify application design, improve performance, enable information hiding, and support subprogram overloading. Unless otherwise specified, the usage and behavior of PL/SQL elements such as procedures, functions, variables, constants, types, cursors, and exceptions within a package remain consistent with their standard behavior.

Package Creation

A package consists of a package specification (header) and a package body. The header primarily contains definitions, while the body contains the corresponding implementations. Subprograms and cursors declared in the header must have their implementations provided in the body. If no subprograms or cursors are declared in the header, the body can be omitted. Objects defined in the header are public and can be called externally, while objects defined only in the body are private and can only be used within the package.

The syntax for creating a package header is as follows:

item_list_1 represents the objects defined, including stored procedures, functions, variables, constants, types, cursors, and exceptions.

The syntax for creating a package body is as follows:

Variables, constants, and cursors declared in the header can be initialized only once in the body.

Cursor names declared in the header must not be the same as a procedure or function routine name.

Package Deletion

You can drop only the package body using DROP PACKAGE BODY pkg_name, or drop both the package and body using DROP PACKAGE pkg_name. After dropping the body, the header remains valid.

Package Invocation

A package is instantiated and its initialize_section is executed upon first reference. We demonstrate common usage through examples. Note that you need to set SQLMODE to 'ORACLE', and if output is required, set SERVEROUTPUT to ON.

Example of Calling Objects in a Package

The following example creates a package containing types, variables, constants, exceptions, stored procedures, functions, and a cursor.

set environment sqlmode 'oracle';
set serveroutput on;
/**********************************************
* Type type_1
* Cursor cur_1
* Exception err
* Function sub: subtracts two integers
* Procedure add_1: adds two integers
* add_2: calls cur_1 and add_1 to add two columns of table t1 and return results
* p_1: calls constants, variables, and function sub
* p_2: modifies values in table tint (used with add_1)
**********************************************/
/********Test Setup********/
drop table if exists tint;
create table tint(id int,val1 int,val2 int); --populate data
insert into tint values(1,10,1);
insert into tint values(2,20,2);
insert into tint values(3,30,3);
insert into tint values(4,40,4);
insert into tint values(5,50,5);
insert into tint values(6,60,6);
insert into tint values(7,70,7);
insert into tint values(8,80,8);
insert into tint values(9,90,9);

/********Package Creation********/
-- Create package header
create package all_object as
--1. Variables, Constants
v_1 int;
v_2 int;
v_3 int:=1;
c_1 constant varchar2(100):='Constant test';
--2. Exception err
err EXCEPTION;
--3. Function sub, subtracts two integers
function sub(a in int,b in int,c out int) return int;
--4. Cursor cur_1
cursor cur_1 return tint%rowtype;
--5. Procedure add_1, adds two integers
procedure add_1(a in int,b in int,c out int);
--6. Procedure add_2, calls cursor cur_1 and procedure add_1 to add two columns of table t1 and return results
procedure add_2(d int);
--7. Procedure p_1, calls variables, constants, exceptions, and functions
procedure p_1;
--8. Procedure p_2, modifies data in the table
procedure p_2(a int,b int,c int);
end all_object;
/

-- Create package body
create package body all_object as
--4. Create cursor
cursor cur_1 return tint%rowtype is select id,val1,val2 from tint;
--3. Create function sub, subtracts two integers
function sub(a in int,b in int,c out int) return int is
begin
if(a>b) then
c:=a-b;
else
raise err;
end if;
return 0;

exception
when err then dbms_output.put_line('a < b');
end;
--5. Create procedure add_1, adds two integers
procedure add_1(a in int,b in int,c out int) is
begin
c:=a+b;
end;
--6. Create procedure add_2, adds results from two columns
procedure add_2(d int) as
type type_1 is record(
v0 int,
v1 int,
v2 int
);
ty_1 type_1;
v3 int;
begin
open cur_1;
for i in 1..d loop
fetch cur_1 into ty_1;
add_1(ty_1.v1,ty_1.v2,v3);
dbms_output.put_line(i||' is:'||v3);
end loop;
close cur_1;
end;
--7. Create procedure p_1, calls variables, constants, and functions
procedure p_1 as
v_4 int;
begin
all_object.v_1:=100;
all_object.v_2:=20;
v_4:=sub(v_1,v_2,v_3);
dbms_output.put_line(v_1||' - '||v_2||' = '||v_3);
dbms_output.put_line('constant c_1 is: '||all_object.c_1);
end;
--8. Create procedure p_2, modifies table data
procedure p_2(a int,b int,c int) as
begin
update tint set val1=a,val2=b where id=c;
end;
end all_object;
/
/********Object Invocation in Package********/
-- Modify the row with id=1 in table tint to (100, 50)
call all_object.p_2(100,50,1);
-- Add columns 2 and 3 of table tint, returning results; 3 means fetch the first 3 results
call all_object.add_2(3);
-- Subtract two variables and return result; call public constant and return result
call all_object.p_1();

/********Cleanup********/
drop package all_object;
drop table tint;

Execution results:

> call all_object.p_2(100,50,1);
Routine executed.
> call all_object.add_2(3);
Routine executed.
1 is:150
2 is:22
3 is:33
> call all_object.p_1();
Routine executed.
100 - 20 = 80
constant c_1 is: Constant test

Now, do you understand database packages?

We hope this article helps you better understand and use packages in GBase 8s database, empowering your database development. If you have any questions about packages in GBase 8s or want to unlock more technical knowledge about GBase databases, feel free to visit the GBASE Technical Community (www.gbase.cn) to share and discuss.