Packages

Creating a Package

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.

-- PACKAGE can be used only in an ORA-compatible database.
gaussdb=# CREATE DATABASE ora_testdb DBCOMPATIBILITY 'ORA';
CREATE DATABASE

gaussdb=# \c ora_testdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora_testdb" as user "omm".

-- Create the emp table.
ora_testdb=# CREATE TABLE emp(
    id INT,             -- Employee ID
    name VARCHAR(50),   -- Employee name
    depno INT,          -- Department ID
    sal FLOAT           -- Salary
);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

-- Create a package header.
ora_testdb=# 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.
ora_testdb=# 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.
ora_testdb=# CALL pkg_emp_mgr.emp_add(1, 'scott', 10, 4000.00);
ora_testdb=# CALL pkg_emp_mgr.emp_add(2, 'july', 20, 3000.00);
-- Query table data.
ora_testdb=# SELECT * FROM emp;
 id | name  | depno | sal  
----+-------+-------+------
  1 | scott |    10 | 4000
  2 | july  |    20 | 3000
(2 rows)

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

-- Call pkg_emp_mgr.emp_del.
ora_testdb=# CALL pkg_emp_mgr.emp_del(2);
-- Query data.
ora_testdb=# SELECT * FROM emp;
 id | name  | depno | sal  
----+-------+-------+------
  1 | scott |    10 | 4000
(1 row)

-- Drop the package.
ora_testdb=# 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.
ora_testdb=# DROP TABLE emp;
DROP TABLE

-- Drop the database.
ora_testdb=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".

gaussdb=# DROP DATABASE ora_testdb;
DROP DATABASE