PolarDB:CREATE PACKAGE BODY

Syntax

CREATE [ OR REPLACE ] PACKAGE BODY name
{ IS | AS }
  [ declaration; ] [, ...]
  [ { PROCEDURE proc_name
      [ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
        [, ...]) ]
      [ STRICT ]
      [ LEAKPROOF ]
      [ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]
      [ COST execution_cost ]
      [ ROWS result_rows ]
      [ SET config_param { TO value | = value | FROM CURRENT } ]
    { IS | AS }
        program_body
      END [ proc_name ];
    |
      FUNCTION func_name
      [ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
        [, ...]) ]
      RETURN rettype [ DETERMINISTIC ]
      [ STRICT ]
      [ LEAKPROOF ]
      [ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]
      [ COST execution_cost ]
      [ ROWS result_rows ]
      [ SET config_param { TO value | = value | FROM CURRENT } ]
    { IS | AS }
        program_body
      END [ func_name ];
    }
  ] [, ...]
  [ BEGIN
      statement; [, ...] ]
  END [ name ]

Description

CREATE PACKAGE BODY creates a package body. CREATE OR REPLACE PACKAGE BODY creates a new package body or replaces an existing one.

If you specify a schema name, the package body is created in that schema. Otherwise, it is created in the current schema. The name of the new package body must match an existing package specification in the same schema, and it cannot duplicate the name of an existing package body in the same schema. To update an existing package body definition, use CREATE OR REPLACE PACKAGE BODY.

The STRICT, LEAKPROOF, PARALLEL, COST, ROWS, and SET keywords provide extended functionality for PolarDB for PostgreSQL (Compatible with Oracle) and are not supported by Oracle databases.

Parameters

Examples

The following example creates the package body for the empinfo package. The package body declares a private variable v_counter, implements the get_name procedure and display_counter function, and initializes v_counter in the package initialization section.

CREATE OR REPLACE PACKAGE BODY empinfo
IS
    v_counter       INTEGER;
    PROCEDURE get_name (
        p_empno     NUMBER
    )
    IS
    BEGIN
        SELECT ename INTO emp_name FROM emp WHERE empno = p_empno;
        v_counter := v_counter + 1;
    END;
    FUNCTION display_counter
    RETURN INTEGER
    IS
    BEGIN
        RETURN v_counter;
    END;
BEGIN
    v_counter := 0;
    DBMS_OUTPUT.PUT_LINE('Initialized counter');
END;

v_counter is a private variable — it is accessible to all subprograms inside the package body, but cannot be read or modified directly from outside the package. The public variable emp_name and the public function display_counter expose controlled access to package state.

The following two anonymous blocks call the stored procedure and function in the empinfo package and display the public variable.

BEGIN
    empinfo.get_name(7369);
    DBMS_OUTPUT.PUT_LINE('Employee Name    : ' || empinfo.emp_name);
    DBMS_OUTPUT.PUT_LINE('Number of queries: ' || empinfo.display_counter);
END;

Output:

Initialized counter
Employee name: SMITH
Number of queries: 1

BEGIN
    empinfo.get_name(7900);
    DBMS_OUTPUT.PUT_LINE('Employee Name    : ' || empinfo.emp_name);
    DBMS_OUTPUT.PUT_LINE('Number of queries: ' || empinfo.display_counter);
END;

Output:

Employee name: JAMES
Number of queries: 2

The initialization section (BEGIN v_counter := 0;) runs once when the package is first referenced in a session. Subsequent calls within the same session skip initialization, which is why the query counter increments across both anonymous blocks.

What's next

• CREATE PACKAGE — define the package specification that this package body implements

• DROP PACKAGE — remove a package and its body