CREATE PACKAGE BODY v15

Suggest edits

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 you include a schema name, then the package body is created in the specified schema. Otherwise it's 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 can't match any existing package body in the same schema unless you want to update the definition of an existing package body. In that 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 in 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. You must declare a subprogram before you can invoke it. 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's public. Otherwise, it's private.

argname

The name of an argument.

IN | IN OUT | OUT

The argument mode.

argtype

The data types of the program’s arguments.

DEFAULT value

Default value of an input argument.

STRICT

Use the STRICT keyword to specify for the function not to execute if called with a NULL argument. Instead the function returns NULL.

LEAKPROOF

Use the LEAKPROOF keyword to specify 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 default), the procedure or function can't execute in parallel mode. The presence of such a procedure or function forces a serial execution plan.

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

  • When set to SAFE, the procedure or function can execute 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 for the query planner to 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 can be PRAGMA AUTONOMOUS_TRANSACTION to set the function or procedure as an autonomous transaction.

The declarations can include variable, type, REF CURSOR, or subprogram declarations. If you include subprogram declarations, declare them 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's public. Otherwise it's 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 you specify DETERMINISTIC for a public function in the package body, you must also specify it for the function declaration in the package specification. For private functions, there's no function declaration in the package specification.

statement

An SPL program statement. Statements in the package initialization section execute 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 aren't 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

This example uses a forward declaration in 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);
Output
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

← Prev

CREATE PACKAGE

Next →

CREATE PROCEDURE