The implementation details of the package are in the package body. The package body may contain objects that are not visible to the package user.
The following syntax is used to define a package body.
CREATE [ OR REPLACE ] PACKAGE BODY package_name
{ IS | AS }
[ private_declaration; ] ...
[ procedure_or_function_definition ] ...
[ package_initializer ]
[ package_name ] ;
Where
procedure_or_function_definition :=
procedure_definition | function_definition
Where
procedure_definition :=
PROCEDURE proc_name[ argument_list ]
[ options_list ]
{ IS | AS }
procedure_body
END [ proc_name ] ;
Where
procedure_body :=
[ declaration; ] [, ...]
BEGIN
statement; [...]
[ EXCEPTION
{ WHEN exception [OR exception] [...]] THEN statement; }
[...]
]
Where
function_definition :=
FUNCTION func_name [ argument_list ]
RETURN rettype [DETERMINISTIC]
[ options_list ]
{ IS | AS }
function_body
END [ func_name ] ;
Where
function_body :=
[ declaration; ] [, ...]
BEGIN
statement; [...]
[ EXCEPTION
{ WHEN exception [ OR exception ] [...] THEN statement; }
[...]
]
Where
argument_list :=
( argument_declaration [, ...] )
Where
argument_declaration :=
argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
Where
options_list :=
option [ ... ]
Where
option :=
STRICT
LEAKPROOF
COST execution_cost
ROWS result_rows
SET config_param { TO value | = value | FROM CURRENT }
Where
package_initializer :=
BEGIN
statement; [...]
END;
Parameters
Parameter | Description |
---|---|
package_name | The name of the package to which this package body belongs. You must have defined the package specification with this name. |
private_declaration | The identifier of a private variable that can be accessed by any procedure or function
within the package. There can be zero, one, or more private variables. Valid values:
|
proc_name | The name of the procedure to be created. |
PRAGMA AUTONOMOUS_TRANSACTION | The command that sets the function as an autonomous transaction. |
declaration | A variable, type, REF CURSOR, or subprogram declaration. If subprogram declarations are included, they must be declared after all other variable, type, and REF CURSOR declarations. |
statement | A Structured Process Language (SPL) program statement. The DECLARE - BEGIN - END block is considered as a part of the SPL statement. Therefore, the function body may contain nested blocks. |
exception | The exception condition name. For example: NO_DATA_FOUND, OTHERS. |
func_name | The name of the function to be created. |
rettype | The data type returned, which may be any of the types listed by argtype. As for argtype, a length cannot be specified for rettype. |
DETERMINISTIC | Specifies that the function always returns the same result when you input the same
argument values. You cannot use the DETERMINISTIC function to modify the database.
Note
|
PRAGMA AUTONOMOUS_TRANSACTION | The command that sets the function as an autonomous transaction. |
declaration | A variable, type, REF CURSOR, or subprogram declaration. If subprogram declarations are included, they must be declared after all other variable, type, and REF CURSOR declarations. |
argname | The name of a formal argument. The argument is referenced by this name within a procedure body. |
IN | IN OUT | OUT | The argument mode. IN: The argument is only used for input. This is the default value. IN OUT: The argument is used to receive a value and return a value. OUT: The argument is only used for output. |
argtype | The data types of an argument. An argument type can be a basic data type, a copy of
the type of an existing column that uses %TYPE, or a user-defined type such as a nested
table or an object type. The basic data type cannot be specified a length. For example,
you must specify VARCHAR2 instead of VARCHAR2(10) as the data type.
You can write tablename.columnname%TYPE to reference the type of a column. This enables a procedure be independent of changes of the definition of a table sometimes. |
DEFAULT value | If the input argument is not provided when you call the procedure, the DEFAULT clause
provides a default value for the input argument. You cannot specify DEFAULT for arguments
that are in the IN OUT or OUT modes.
Note The following options are not compatible with Oracle databases. They are only extensions
to Oracle package syntax provided by ApsaraDB PolarDB.
|
STRICT | The STRICT keyword specifies that the function is not executed if you call the function with a NULL argument. The function returns NULL instead. |
LEAKPROOF | The LEAKPROOF keyword specifies that except for the return value, the function will not reveal any information about arguments. |
PARALLEL { UNSAFE | RESTRICTED | SAFE } | The PARALLEL clause allows you to use parallel sequential scans in the parallel mode.
Different from a serial sequential scan, a parallel sequential scan uses multiple
workers to scan a relation in parallel during a query. Valid values:
|
execution_cost | This parameter specifies estimated execution cost for the function. The value must be a positive number. Unit: cpu_operator_cost. If the function returns a set, this is the collection of execution costs for per returned row. The default value is 0.0025. |
result_rows | The estimated number of rows that the query planner expects the function to return. The default value is 1000. |
SET | The SET clause helps you set a parameter value for the duration of the function.
|
package_initializer | The statements in the package_initializer are executed once for each of your session when the package is first referenced |
Note The STRICT, LEAKPROOF, PARALLEL, COST, ROWS and SET keywords can provide extended
functionality for ApsaraDB PolarDB but are not supported by Oracle.