PolarDB:CREATE PACKAGE

Syntax

CREATE [ OR REPLACE ] PACKAGE <package_name>
    [ AUTHID { DEFINER | CURRENT_USER } ]
    { IS | AS }
    [ <declaration>; ] [, ...]
[   {
        PROCEDURE <proc_name>
        [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ]
        [, ...]) ];
        [ PRAGMA RESTRICT_REFERENCES(<proc_name>,
        { RNDS | RNPS | TRUST | WNDS | WNPS } [, ... ] ); ]
        |
        FUNCTION <func_name>
        [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ]
        [, ...]) ]
        RETURN <rettype> [ DETERMINISTIC ];
        [ PRAGMA RESTRICT_REFERENCES(<func_name>,
        { RNDS | RNPS | TRUST | WNDS | WNPS } [, ... ] ); ]
    }
] [, ...]
END [ <name> ]

Parameters

OR REPLACE behavior

When OR REPLACE is specified:

• If the package does not exist, it is created.

• If the package already exists, its specification is replaced and recompiled.

Without OR REPLACE, executing CREATE PACKAGE when the package already exists returns an error.

Package specification structure

The specification defines the public interface of the package—the elements that external code can reference. Everything declared here (constants, variables, types, procedures, and functions) is visible outside the package.

CREATE OR REPLACE PACKAGE Employee_Pkg AS
    -- Constant declaration
    MAX_SALARY CONSTANT NUMBER := 100000;

    -- Procedure declarations
    PROCEDURE Add_Employee(
        p_emp_id     IN NUMBER,
        p_first_name IN VARCHAR2,
        p_last_name  IN VARCHAR2,
        p_salary     IN NUMBER
    );

    PROCEDURE Update_Salary(
        p_emp_id     IN NUMBER,
        p_new_salary IN NUMBER
    );

    -- Function declaration
    FUNCTION Get_Employee_Name(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2;
END Employee_Pkg;

The package body provides the implementation for each declared procedure and function. For the package body syntax, see CREATE PACKAGE BODY.

Schema and ownership

• If a schema name is included in package_name, the package is created in that schema. Otherwise, it is created in the current schema.

• Package names must be unique within a schema.

• The user that creates the package is its owner.

Call procedures and functions in a package

After the package is created and compiled, reference its members using the package_name.member_name notation.

Call a procedure:

BEGIN
    Employee_Pkg.Add_Employee(
        p_emp_id     => 1001,
        p_first_name => 'John',
        p_last_name  => 'Smith',
        p_salary     => 50000
    );
END;

Call a function:

DECLARE
    v_emp_name VARCHAR2(100);
BEGIN
    v_emp_name := Employee_Pkg.Get_Employee_Name(1001);
    DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_emp_name);
END;

Function overloading

PolarDB for PostgreSQL (Compatible with Oracle) supports multiple functions with the same name but different parameter signatures in a single package.

Starting from minor engine version 2.0.14.26.0, you can also define functions that share the same name and the same IN parameters but have different OUT parameters. Clusters running an earlier version must be upgraded to 2.0.14.26.0 or later. For upgrade instructions, see Version management.

CREATE OR REPLACE PACKAGE Employee_Pkg AS
    MAX_SALARY CONSTANT NUMBER := 100000;

    -- Two functions with the same name, different parameters
    FUNCTION Get_Employee_Name(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2;

    FUNCTION Get_Employee_Name(
        p_emp_id IN NUMBER,
        res_info OUT text
    ) RETURN VARCHAR2;
END Employee_Pkg;

Package variables as default parameter values

Starting from minor engine version 2.0.14.26.0, functions in a package can use variables from the same package as default parameter values. Clusters running an earlier version must be upgraded to 2.0.14.26.0 or later. For upgrade instructions, see Version management.

CREATE OR REPLACE PACKAGE test_package AS
    a NUMBER := 1;
    b NUMBER := 2;
END my_package;

CREATE OR REPLACE PACKAGE my_package AS
    FUNCTION add_numbers(x NUMBER default test_package.a, y NUMBER default test_package.b) RETURN NUMBER;
    PROCEDURE print_message(msg VARCHAR2);
END my_package;

Update a package

To add a new publicly accessible object to a package, update both the package specification and the package body.

Modifying the package specification invalidates all dependent objects, which must be recompiled before use. If you only modify the package body without changing the specification, dependent objects remain valid.

The following example adds a function to an existing package.

1. Update the package specification.

   CREATE OR REPLACE PACKAGE Employee_Pkg AS
       -- Existing declarations
       FUNCTION Get_Employee_Department(p_emp_id IN NUMBER) RETURN VARCHAR2;
   END Employee_Pkg;

2. Update the package body.

   CREATE OR REPLACE PACKAGE BODY Employee_Pkg AS
       -- Existing implementation
   
       FUNCTION Get_Employee_Department(p_emp_id IN NUMBER) RETURN VARCHAR2 IS
           v_department VARCHAR2(100);
       BEGIN
           SELECT department
           INTO v_department
           FROM employees
           WHERE emp_id = p_emp_id;
           RETURN v_department;
       EXCEPTION
           WHEN NO_DATA_FOUND THEN
               RETURN 'Not Assigned';
       END Get_Employee_Department;
   END Employee_Pkg;

Usage notes

• Dependency management: Modifying the package specification invalidates all dependent objects. Recompile them before use. Plan specification changes carefully in production environments.

• Exception handling: Handle exceptions inside packages as early as possible to prevent unhandled exceptions from interrupting program flow.

• Performance: Use private variables and package-level caching to reduce redundant database lookups.

• Access control: Use AUTHID and package-level permissions to restrict access to sensitive data and operations.

• Naming conventions: Use consistent naming conventions across packages to improve readability and maintainability.

What's next

• CREATE PACKAGE BODY — implement the procedures and functions declared in the package specification, including worked examples.

• Version management — query and upgrade the minor engine version of your cluster.