Packages

Overview

A package is a group of related subprograms, including cursors and variables used by the package. A package is stored in the database and can be used as a unit. Packaged subprograms can be explicitly called by applications or users. It has the following features:

• Modular programming: Related functions and stored procedures are encapsulated in a package to facilitate modular programming. Modularization improves code readability and maintainability.
• Dependency chain disconnection: Modifying a database object (for example, modifying the table structure) does not cause all other objects (such as stored procedures, functions, and views) that depend on the object to become invalid. This avoids high-cost recompilation.
• Performance optimization: Packages are stored in the database in compilation mode. When a package is called, the database does not need to compile code again, improving the execution efficiency.
• Namespace management: A package provides a namespace to avoid naming conflicts between different modules. In the same package, different functions and procedures can coexist without conflict, even if they have the same name in other contexts.
• Security: Packages can be used to better control access to database objects.

Examples

Creating a package requires the package header and body. The header needs to declare all public structures, and the body needs to define all structures of the package, including the public part and private part.

In the following example, the emp table and pkg_emp_mgr package are created. The package contains three stored procedures.

• pkg_emp_mgr.emp_add inserts employee information into the emp table.
• pkg_emp_mgr.emp_del deletes employee information from the emp table.
• pkg_emp_mgr.emp_res modifies information in the emp table.

-- Create the emp table.
gaussdb=# CREATE TABLE emp(
    id INT,             -- Employee ID
    name VARCHAR(50),   -- Employee name
    depno INT,          -- Department ID
    sal FLOAT           -- Salary
);
CREATE TABLE

-- Create a package header.
gaussdb=# CREATE OR REPLACE PACKAGE pkg_emp_mgr AS 
    PROCEDURE emp_add(v_id INT, v_name VARCHAR, v_depno INT, v_sal FLOAT);
    PROCEDURE emp_del(v_id INT);
    PROCEDURE emp_res(v_id INT, v_depno INT, v_sal VARCHAR);
END pkg_emp_mgr;
/

-- Create a package body.
gaussdb=# CREATE OR REPLACE PACKAGE BODY pkg_emp_mgr AS 
    PROCEDURE emp_add(v_id INT, v_name VARCHAR, v_depno INT, v_sal FLOAT) AS 
    BEGIN
        INSERT INTO emp VALUES (v_id, v_name, v_depno, v_sal);
    END;
    PROCEDURE emp_del(v_id INT) AS 
    BEGIN
        DELETE FROM emp WHERE id = v_id;
    END;
    PROCEDURE emp_res(v_id INT, v_depno INT, v_sal VARCHAR) AS
    BEGIN
        UPDATE emp SET depno = v_depno, sal = v_sal WHERE id = v_id;
    END;
END pkg_emp_mgr;
/

-- Call pkg_emp_mgr.emp_add to add employee information.
gaussdb=# CALL pkg_emp_mgr.emp_add(1, 'scott', 10, 4000.00);
gaussdb=# CALL pkg_emp_mgr.emp_add(2, 'july', 20, 3000.00);
-- Query table data.
gaussdb=# SELECT * FROM emp;
 id | name  | depno | sal  
----+-------+-------+------
  1 | scott |    10 | 4000
  2 | july  |    20 | 3000
(2 rows)

-- Call pkg_emp_mgr.emp_res to modify employee information.
gaussdb=# CALL pkg_emp_mgr.emp_res(2, 30, 6000.00);
-- Query data.
gaussdb=# SELECT * FROM emp;
 id | name  | depno | sal  
----+-------+-------+------
  1 | scott |    10 | 4000
  2 | july  |    30 | 6000
(2 rows)

-- Call pkg_emp_mgr.emp_del to delete employee information.
gaussdb=# CALL pkg_emp_mgr.emp_del(2);

-- Query data.
gaussdb=# SELECT * FROM emp;
 id | name  | depno | sal  
----+-------+-------+------
  1 | scott |    10 | 4000
(1 row)

-- Drop the package.
gaussdb=# DROP PACKAGE pkg_emp_mgr;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to function public.emp_add(integer,character varying,integer,double precision)
drop cascades to function public.emp_del(integer)
drop cascades to function public.emp_res(integer,integer,character varying)
DROP PACKAGE

-- Drop the table.
gaussdb=# DROP TABLE emp;
DROP TABLE