Creates a 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 REPLACEPACKAGE BODY creates a new package body or replaces an existing body.

If you specify a schema name, the package body is created in the specified schema. Otherwise, the package body is created in the current schema. The name of the new package body must match an existing package specification in the same schema. The name of the new package body cannot be the same as an existing package body in the same schema. If you want to update the definition of an existing package body, you can use the CREATE OR REPLACE PACKAGE BODY command.

Parameters

ParameterDescription
ParameterDescription
nameThe name of the package body to be created. The name can be schema-qualified.
declarationA private variable, type, cursor, or REF CURSOR declaration.
proc_nameThe name of a public stored procedure or private stored procedure. If proc_name with the same signature exists in the package specification, the stored procedure is public. Otherwise, the stored procedure is private.
argnameThe name of an argument.
IN | IN OUT | OUTThe argument mode.
argtypeThe data types of the program arguments.
DEFAULT valueThe default value of an input argument.
STRICTThe STRICT keyword specifies that the function is not executed when a NULL parameter is used to call the function. On the contrary, the function returns NULL.
LEAKPROOFThe LEAKPROOF keyword specifies that the function does not reveal information about arguments, other than through a return value.
PARALLEL { UNSAFE | RESTRICTED | SAFE }The PARALLEL clause enables the use of parallel sequential scans (parallel mode). A parallel sequential scan uses multiple workers to scan a relation in parallel during a query in contrast to a serial sequential scan.
  • If this parameter is set to UNSAFE, the stored procedure or function cannot be executed in the parallel mode. If such a stored procedure or function exists in an SQL statement, a serial execution plan is enforced. If the PARALLEL clause is omitted, this is the default setting.
  • If this parameter is set to RESTRICTED, the stored procedure or function can be executed in the parallel mode, but the execution is restricted to the parallel group leader. If the qualification for a particular relation has content that is parallel restricted, the relation is not selected for parallel execution.
  • If this parameter is set to SAFE, the stored procedure or function can be executed in the parallel mode without restrictions.
execution_costexecution_cost is a positive value that indicates the estimated execution cost of the function. The unit is cpu_operator_cost. If the function returns a set, this is the cost of each returned row. The default value is 0.0025.
result_rowsresult_rows is a positive value that indicates the estimated number of rows that the planner expects the function to return. The default value is 1000.
SETYou can use the SET clause to specify a parameter value for the duration of the function:
  • config_param specifies the parameter name.
  • value specifies the parameter value.
  • FROM CURRENT ensures that the parameter value is restored when the function ends.
program_bodyThe pragma, declarations, and SPL statements that comprise the body of the function or stored procedure.

The pragma can be PRAGMA AUTONOMOUS_TRANSACTION to set the function or stored procedure as an autonomous transaction.

The declarations can include variable, type, REF CURSOR, and subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, type, and REF CURSOR declarations.

func_nameThe name of a public or private function. If func_name with the same signature exists in the package specification, the function is public. Otherwise, the function is private.
rettypeThe return data type.
DETERMINISTICYou can use DETERMINISTIC to specify that the function always returns the same result if the same argument value is specified. A DETERMINISTIC function does not modify the database.
Note
  • The DETERMINISTIC keyword is equivalent to the PostgreSQL IMMUTABLE option.
  • If you have specified the DETERMINISTIC keyword for a public function in the package body, you must also specify this keyword for the function declaration in the package specification. For private functions, no function declarations are included in the package specification.
statementAn SPL program statement. If a package is referenced for the first time, the statements in the package initialization section are executed once for each session.
Note
The STRICT, LEAKPROOF, PARALLEL, COST, ROWS, and SET keywords provide extended functionality for PolarDB for PostgreSQL(Compatible with Oracle). However, these keywords are not supported by Oracle databases.

Examples

The following is the package body for the empinfo package.

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;

The following two anonymous blocks execute 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;

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;

Employee name: JAMES
Number of queries: 2