Table of Contents Previous Next


2 The SQL Language : 2.3 SQL Commands : 2.3.24 CREATE PACKAGE BODY

CREATE BODY PACKAGE -- define a new package body
[ declaration; ] [, ...]
[ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
[ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]
[ COST execution_cost ]
[ ROWS result_rows ]
[ SET config_param { TO value | = value | FROM CURRENT } ]
program_body
END [ proc_name ];
FUNCTION func_name
[ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
RETURN rettype [ DETERMINISTIC ]
[ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]
[ COST execution_cost ]
[ ROWS result_rows ]
[ SET config_param { TO value | = value | FROM CURRENT } ]
program_body
END [ func_name ];
statement; [, ...] ]
END [ name ]
CREATE PACKAGE BODY defines a new package body. CREATE OR REPLACE PACKAGE BODY will either create a new package body, or replace an existing body.
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.
The STRICT keyword specifies that the function will not be executed if called with a NULL argument; instead the function will return NULL.
The LEAKPROOF keyword specifies that the function will not reveal any information about arguments, other than through a return value.
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 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 is the estimated number of rows that the query planner should expect the function to return. The default is 1000.
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.
The declarations and SPL statements that comprise the body of the function or procedure.
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.
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.
Include DETERMINISTIC to specify that the function will 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.
Note: 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.
An SPL program statement. Statements in the package initialization section are executed once per session the first time the package is referenced.
Please Note: The STRICT, LEAKPROOF, PARALLEL, COST, ROWS and SET keywords provide extended functionality for Advanced Server and are not supported by Oracle.

2 The SQL Language : 2.3 SQL Commands : 2.3.24 CREATE PACKAGE BODY

Table of Contents Previous Next