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:
  • Variable declaration
  • Record declaration
  • Collection declaration
  • REF CURSOR and CURSOR variable declaration
  • TYPE definitions for records, collections, and REF CURSORs
  • Exception
  • Object variable declaration
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
  • The DETERMINISTIC keyword is equivalent to the IMMUTABLE option in PostgreSQL.
  • If you specify DETERMINISTIC for a public function in the package body, you must also specify DETERMINISTIC for the function declaration in the package specification. For private functions, there is no function declaration in the package specification.
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:
  • UNSAFE: The procedure or function cannot be executed in parallel mode. In this case, a serial execution plan is implemented. This is the default value if you omit the PARALLEL clause.
  • RESTRICTED: The procedure or function can be executed in the parallel mode, but the execution is restricted to the parallel group leader. If the qualification for any particular relation has and parallel restrictions, that relation cannot be chosen for parallelism.
  • SAFE: The procedure or function can be executed in the parallel mode without any restriction.
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.
  • config_param: Specifies the parameter name.
  • value: Specifies the value of the parameter.
  • FROM CURRENT: Guarantees that the parameter value is restored when the function ends.
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.