CREATE PACKAGE BODY v14

Name

CREATE PACKAGE BODY -- define a new package body.

Synopsis

CREATE [ OR REPLACE ] PACKAGE BODY <name>
{ IS | AS }
  [ declaration; ] | [ forward_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> ]

Where forward_declaration:=

[ { PROCEDURE <proc_name>
  [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...])
  ] ; ]
|
[ { FUNCTION <func_name>
  [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...])
  ]
  RETURN <rettype> [ DETERMINISTIC ]; ]

Description

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

If a schema name is included, then the package body is created in the specified 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. The new package body name must not match any existing package body in the same schema unless the intent is to update the definition of an existing package body, in which case use CREATE OR REPLACE PACKAGE BODY.

Parameters

name

The name (optionally schema-qualified) of the package body to create.

declaration

A private variable, type, cursor, or REF CURSOR declaration.

forward_declaration

The forward declaration of a procedure or function appears within a package body and is declared in advance of the actual body definition. In a block, you can create multiple subprograms; if they invoke each other, each one requires a forward declaration. A subprogram must be declared before it can be invoked. You can use a forward declaration to declare a subprogram without defining it. The forward declaration and its corresponding definition must reside in the same block.

proc_name

The name of a public or private procedure. If proc_name exists in the package specification with an identical signature, then it is public, otherwise it is private.

argname

The name of an argument.

IN | IN OUT | OUT

The argument mode.

argtype

The data type(s) of the program’s arguments.

DEFAULT value

Default value of an input argument.

STRICT

The STRICT keyword specifies for the function not to execute if called with a NULL argument; instead the function returns NULL.

LEAKPROOF

The LEAKPROOF keyword specifies for the function not to reveal any 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.

  • When set to UNSAFE, the procedure or function cannot be executed in parallel mode. The presence of such a procedure or function forces a serial execution plan. This is the default setting if the PARALLEL clause is omitted.

  • When set to RESTRICTED, the procedure or function can be executed in parallel mode, but the execution is restricted to the parallel group leader. If the qualification for any particular relation has anything that is parallel restricted, that relation won't be chosen for parallelism.

  • When set to SAFE, the procedure or function can be executed in parallel mode with no restriction.

execution_cost

execution_cost specifies a positive number giving the estimated execution cost for the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. The default is 0.0025.

result_rows

result_rows is the estimated number of rows that the query planner should expect the function to return. The default is 1000.

SET

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 guarantees that the parameter value is restored when the function ends.

program_body

The pragma, declarations, and SPL statements that comprise the body of the function or procedure.

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

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

func_name

The name of a public or private function. If func_name exists in the package specification with an identical signature, then it is public, otherwise it is private.

rettype

The return data type.

DETERMINISTIC

Include DETERMINISTIC to specify for the function to always return the same result when given the same argument values. A DETERMINISTIC function must not modify the database.

Note: The DETERMINISTIC keyword is equivalent to the PostgreSQL IMMUTABLE option. If DETERMINISTIC is specified for a public function in the package body, it must also be specified for the function declaration in the package specification. For private functions, there is no function declaration in the package specification.

statement

An SPL program statement. Statements in the package initialization section are executed once per session the first time the package is referenced.

Note

The STRICT, LEAKPROOF, PARALLEL, COST, ROWS and SET keywords provide extended functionality for EDB Postgres Advanced Server and are not supported by Oracle.

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 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

The following example demonstrates the use of a forward declaration within a package body. The example displays the name and number of employees whose salary falls in the specified range.

CREATE OR REPLACE PACKAGE empinfo IS
  FUNCTION emp_comp (p_sal_range INTEGER) RETURN INTEGER;
END empinfo;

CREATE OR REPLACE PACKAGE BODY empinfo IS
  FUNCTION sal_range (p_sal_range INTEGER) RETURN INTEGER;
  PROCEDURE list_emp (p_sal_range INTEGER);

  FUNCTION emp_comp (p_sal_range INTEGER) RETURN INTEGER IS
  BEGIN
    dbms_output.put_line ('Employee details');
    return sal_range(p_sal_range);
  END;

  FUNCTION sal_range (p_sal_range INTEGER) RETURN INTEGER IS
    emp_cnt INTEGER;
  BEGIN
    select count(*) into emp_cnt from emp where sal <= p_sal_range;
    dbms_output.put_line('Number of employees in the salary range ' ||
p_sal_range|| ' is :'|| emp_cnt);
    list_emp(p_sal_range);
    return emp_cnt;
  END;

  PROCEDURE list_emp (p_sal_range IN INTEGER) IS
  BEGIN
    FOR i IN select ename from emp where sal <= p_sal_range
    LOOP
       dbms_output.put_line (i);
    END LOOP;
  END;

END empinfo;


SELECT empinfo.emp_comp(1500);
Employee details
Number of employees in the salary range 1500 is :7
(SMITH)
(WARD)
(MARTIN)
(TURNER)
(ADAMS)
(JAMES)
(MILLER)
emp_comp
----------
        7
(1 row)

See also

CREATE PACKAGE, DROP PACKAGE