Packages consist of two main components:
  • The package specification: This is the public interface. These are the elements which can be referenced outside the package. The specification declares all database objects that are to be a part of the package.
  • The package body: This contains the actual implementation of all the database objects declared within the package specification.

The package body implements the specifications in the package specification. It contains implementation details and private declarations which are invisible to the application. You can debug, enhance, or replace a package body without changing the specifications. Similarly, you can change the body without recompiling the calling programs because the implementation details are invisible to the application.

Package specification syntax

The package specification defines the user interface for a package (the API). The specification lists the functions, procedures, types, exceptions, and cursors that are visible to a user of the package.

The syntax used to define the interface for a package is:
CREATE [ OR REPLACE ] PACKAGE package_name
  [ authorization_clause ]
  { IS | AS }
  [ declaration; ] ...
  [ procedure_or_function_declaration; ] ...
END [ package_name ] ;
Where authorization clause : =
{ AUTHID DEFINER } | { AUTHID CURRENT_USER }
Where procedure or function declaration :=
procedure declaration | function declaration
Where procedure declaration :=
PROCEDURE proc name[ argument list ] [restriction pragma];
Where function_declaration :=
FUNCTION func_name [ argument_list ]

RETURN rettype [ restriction pragma ];
                
Where argument_list :=
( argument declaration [, ...] )
Where argument declaration :=
argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]
Where restriction pragma : =
PRAGMA RESTRICT_REFERENCES(name, restrictions)
Where restrictions :=
restriction [, ... ]

Parameters

Parameter Description
package_name package_name is an identifier assigned to the package and each package must have a name unique within the schema.
AUTHID DEFINER If you omit the AUTHID clause or specify AUTHID DEFINER: The privileges of the package owner are used to determine access privileges to database objects and the search paths of the package owner are used to resolve the reference of the unqualified database object.
AUTHID CURRENT_USER If you specify AUTHID CURRENT_USER: the privileges of the current user that executes a program in the package are used to determine access privileges to database objects and search paths of the current user that executes a program in the package are used to resolve the reference of the unqualified database object.
declaration declaration is an identifier of a public variable. A public variable can be accessed from outside of the package by using the package_name.variable syntax. There can be zero, one, or more public variables. Public variable definitions must come before procedure or function declarations. declaration can be any of the following types:
  • Variable declaration
  • Record declaration
  • Collection declaration
  • REF CURSOR and cursor variable declaration
  • TYPE definitions for records, collections, and REF CURSORs
  • Exception
  • Object variable declaration
argname The name of an argument. The argument is referenced by this name within the function or procedure body.
IN | IN OUT | OUT The argument mode.
  • IN declares the argument for input only. This is the default mode.
  • IN OUT allows the argument to receive a value or return a value.
  • OUT specifies the argument is for output only.
argtype

The data types of an argument. An argument type may be a base 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. A length cannot be specified for any base type, for example, specify VARCHAR2, not VARCHAR2(10).

The type of a column is referenced by writing tablename.columnname%TYPE. Using tablename. columnname%TYPE can sometimes help make a procedure independent from changes to the definition of a table.

DEFAULT value The DEFAULT clause supplies a default value for an input argument if one is not supplied in the invocation. DEFAULT cannot be specified for arguments with modes IN OUT or OUT.
name name is the name of the function or procedure.
restriction The following keywords are accepted for compatibility and ignored:
  • RNDS
  • RNPS
  • TRUST
  • WNDS
  • WNPS

Package body syntax

Package implementation details reside in the package body. The package body may contain objects that are not visible to the package user. PolarDB-O supports the following syntax for the package body:
CREATE [ OR REPLACE ] PACKAGE BODY package_name
  { IS | AS }
  [ private_declaration; ] ...
  [ procedure_or_function_definition; ] ...
  [ package_initializer ]
END [ 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 :=
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 package_name is the name of the package for which this is the package body. There must be an existing package specification with this name.
private_declaration private_declaration is an 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. private_declaration can be any of the following types:
  • 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 being created.
declaration A variable, type, or REF CURSOR declaration.
statement An SPL program statement. Note that a DECLARE - BEGIN - END block is considered an SPL statement unto itself. Thus, the function body may contain nested blocks.
exception An exception condition name, such as NO_DATA_FOUND and OHERS.
func_name The name of the function being created.
rettype The return data type, which may be any of the types listed for argtype. As for argtype, a length cannot be specified for rettype.
DETERMINISTIC Includes DETERMINISTIC to specify that the function will always return the same result when given the same argument values. A DETERMINISTIC function cannot modify the database.
Note The DETERMINISTIC keyword is equivalent to the PostgreSQL IMMUTABLE option.
declaration A variable, type, or REF CURSOR declaration.
argname The name of a formal argument. The argument is referenced by this name within the procedure body.
IN | IN OUT | OUT The argument mode.
  • IN declares the argument for input only. This is the default mode.
  • IN OUT allows the argument to receive a value or return a value.
  • OUT specifies the argument is for output only.
argtype

The data types of an argument. An argument type may be a base 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. A length cannot be specified for any base type, for example, specify VARCHAR2, not VARCHAR2(10).

The type of a column is referenced by writing tablename.columnname%TYPE. Using tablename.columnname%TYPE can sometimes help make a procedure independent from changes to the definition of a table.

DEFAULT value The DEFAULT clause supplies a default value for an input argument if one is not supplied in the procedure call. DEFAULT cannot be specified for arguments with modes IN OUT or OUT.
Note The following options are not compatible with Oracle databases. They are extensions to Oracle package syntax provided by PolarDB-O only.
STRICT The STRICT keyword specifies that the function will not be executed if called with a NULL argument. Instead the function will return NULL.
LEAKPROOF The LEAKPROOF keyword specifies that the function will not reveal any information about arguments, other than through a return value.
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. Default value: 0.0025.
result_rows result_rows is the estimated number of rows that the query planner expect the function to return. Default value: 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.
package_initializer The statements in the package_initializer are executed once per user's session when the package is first referenced.
Note The STRICT, LEAKPROOF, COST, ROWS and SET keywords provide extended functionality for POLAR DB compatible with Oracle and are not supported by Oracle.