CREATE PACKAGE BODY v17
Name
CREATE PACKAGE BODY
— Define a new package body.
Synopsis
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:
The following two anonymous blocks execute the procedure and function in the empinfo
package and display the public variable:
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.