PolarDB:CREATE PACKAGE

Overview

The CREATE PACKAGE statement defines the specification of a package. A package is an encapsulated collection of related procedures, functions, and other program objects stored in a database. The package specification declares the objects. The package body defines the specific implementation of the objects.

If you specify a schema name when you create a package, the package is created in the specified schema. Otherwise, the package is created in the current schema. The name of each package must be unique in a schema. To update the specification of a package, use the CREATE OR REPLACE PACKAGE statement. The user that creates a package is the owner of the package.

Packages are an alternative solution to creating procedures and functions as standalone schema objects.

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

Structure

The specification of a package defines the elements that can be referenced outside of the package. The elements include constants, variables, types, procedures, and functions.

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

    -- Procedure declaration
    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;

Usage notes

• Dependency management: If you modify the specification of a package, all objects that are dependent on the package must be recompiled. Proceed with caution when you modify a package specification.

• Error handling: We recommend that you handle exceptions or errors in packages at the earliest opportunity to prevent program interruptions due to uncaught exceptions.

• Performance optimization: We recommend that you properly use private variables and the cache mechanism of packages to improve performance.

• Security: You can limit access to sensitive data and features by using package permissions management.

• Naming conventions: We recommend that you use consistent naming conventions to facilitate better code understanding and maintainability.

Usage

After a package is successfully created and compiled, you can reference the procedures and functions defined within the package in your PL/SQL code.

Call the procedures and functions in a package

• Call a procedure.

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

• Configure 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;

Handle functions that have the same name in a package

Similar to Oracle databases, PolarDB for PostgreSQL (Compatible with Oracle) clusters allow you to define multiple functions that have the same name but different parameters in a package.

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

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

    -- Functions with the same name
    FUNCTION Get_Employee_Name(
        p_emp_id IN NUMBER,
        res_info OUT text
    ) RETURN VARCHAR2;
END Employee_Pkg;

Use package variables as default values of parameters in functions in the same package

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;

Maintain and update packages

Add a function to a package.

1. Modify the package specification.

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

2. Modify 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;

References

For more information about how to create and use a package, see Examples.