5.12. PACKAGE BODY

5.12.1. CREATE PACKAGE BODY

Creates a package body

Available inDSQL

Syntax 

   |CREATE PACKAGE BODY name
   |AS
   |BEGIN
   |  [ <package_item> ... ]
   |  [ <package_body_item> ... ]
   |END
   | 
   |<package_item> ::=
   |  !! See CREATE PACKAGE syntax !!
   | 
   |<package_body_item> ::=
   |  <function_impl> |
   |  <procedure_impl>
   | 
   |<function_impl> ::=
   |  FUNCTION funcname [ ( [ <in_params> ] ) ]
   |  RETURNS <domain_or_non_array_type> [COLLATE collation]
   |  [DETERMINISTIC]
   |  <module-body>
   | 
   |<procedure_impl> ::=
   |  PROCEDURE procname [ ( [ <in_params> ] ) ]
   |  [RETURNS (<out_params>)]
   |  <module-body>
   | 
   |<module-body> ::=
   |  !! See Syntax of Module Body !!
   | 
   |<in_params> ::=
   |  !! See CREATE PACKAGE syntax !!
   |  !! See also Rules below !!
   | 
   |<out_params> ::=
   |  !! See CREATE PACKAGE syntax !!
   | 
   |<domain_or_non_array_type> ::=
   |  !! See Scalar Data Types Syntax !!

Table 5.32. CREATE PACKAGE BODY Statement Parameters
ParameterDescription

package_name

Package name. The maximum length is 63 characters. The package name must be unique among all package names.

function_impl

Function implementation. Essentially a CREATE FUNCTION statement without CREATE.

procedure_impl

Procedure implementation Essentially a CREATE PROCEDURE statement without CREATE.

func_name

Function name. The maximum length is 63 characters. The function name must be unique within the package.

collation

Collation

proc_name

Procedure name. The maximum length is 63 characters. The function name must be unique within the package.

The CREATE PACKAGE BODY statement creates a new package body. The package body can only be created after the package header has been created. If there is no package header with name package_name, an error is raised.

All procedures and functions declared in the package header must be implemented in the package body. Additional procedures and functions may be defined and implemented in the package body only. Procedure and functions defined in the package body, but not defined in the package header, are not visible outside the package body.

The names of procedures and functions defined in the package body must be unique among the names of procedures and functions defined in the package header and implemented in the package body.

ⓘ
Package procedure and function names may shadow global routines

If a package header or package body declares a procedure or function with the same name as a stored procedure or function in the global namespace, it is not possible to call that global procedure or function from the package body. In this case, the procedure or function of the package will always be called.

For this reason, it is recommended that the names of stored procedures and functions in packages do not overlap with names of stored procedures and functions in the global namespace.

Rules

  • In the package body, all procedures and functions must be implemented with the same signature as declared in the header and at the beginning of the package body

  • The default values for procedure or function parameters cannot be overridden (as specified in the package header or in <package_item>). This means default values can only be defined in <package_body_item> for procedures or functions that have not been defined in the package header or earlier in the package body.

ⓘ
Note

UDF declarations (DECLARE EXTERNAL FUNCTION) are not supported for packages. Use UDR instead.

5.12.1.1. Who Can Create a Package Body

The CREATE PACKAGE BODY statement can be executed by:

  • Administrators

  • The owner of the package

  • Users with the ALTER ANY PACKAGE privilege

5.12.1.2. Examples of CREATE PACKAGE BODY

Creating the package body 

   |CREATE PACKAGE BODY APP_VAR
   |AS
   |BEGIN
   |  -- Returns the start date of the period
   |  FUNCTION GET_DATEBEGIN() RETURNS DATE DETERMINISTIC
   |  AS
   |  BEGIN
   |    RETURN RDB$GET_CONTEXT('USER_SESSION', 'DATEBEGIN');
   |  END
   |  -- Returns the end date of the period
   |  FUNCTION GET_DATEEND() RETURNS DATE DETERMINISTIC
   |  AS
   |  BEGIN
   |    RETURN RDB$GET_CONTEXT('USER_SESSION', 'DATEEND');
   |  END
   |  -- Sets the date range of the working period
   |  PROCEDURE SET_DATERANGE(ADATEBEGIN DATE, ADATEEND DATE)
   |  AS
   |  BEGIN
   |    RDB$SET_CONTEXT('USER_SESSION', 'DATEBEGIN', ADATEBEGIN);
   |    RDB$SET_CONTEXT('USER_SESSION', 'DATEEND', ADATEEND);
   |  END
   |END

See alsoSection 5.12.2, “DROP PACKAGE BODY”, Section 5.12.3, “RECREATE PACKAGE BODY”, CREATE PACKAGE

5.12.2. DROP PACKAGE BODY

Drops a package body

Available inDSQL

Syntax 

  |DROP PACKAGE package_name

Table 5.33. DROP PACKAGE BODY Statement Parameters
ParameterDescription

package_name

Package name

The DROP PACKAGE BODY statement deletes the package body.

5.12.2.1. Who Can Drop a Package Body

The DROP PACKAGE BODY statement can be executed by:

  • Administrators

  • The owner of the package

  • Users with the ALTER ANY PACKAGE privilege

5.12.2.2. Examples of DROP PACKAGE BODY

Dropping the package body 

  |DROP PACKAGE BODY APP_VAR;

See alsoSection 5.12.1, “CREATE PACKAGE BODY”, Section 5.12.3, “RECREATE PACKAGE BODY”, DROP PACKAGE

5.12.3. RECREATE PACKAGE BODY

Drops a package body if it exists, and creates a package body

Available inDSQL

Syntax 

  |RECREATE PACKAGE BODY name
  |AS
  |BEGIN
  |  [ <package_item> ... ]
  |  [ <package_body_item> ... ]
  |END
  | 
  |!! See syntax of CREATE PACKAGE BODY for further rules !!

The RECREATE PACKAGE BODY statement creates a new or recreates an existing package body. If a package body with the same name already exists, the statement will try to drop it and then create a new package body. After recreating the package body, privileges of the package and its routines are preserved.

See Section 5.12.1, “CREATE PACKAGE BODY” for more details.

5.12.3.1. Examples of RECREATE PACKAGE BODY

Recreating the package body 

   |RECREATE PACKAGE BODY APP_VAR
   |AS
   |BEGIN
   |  -- Returns the start date of the period
   |  FUNCTION GET_DATEBEGIN() RETURNS DATE DETERMINISTIC
   |  AS
   |  BEGIN
   |    RETURN RDB$GET_CONTEXT('USER_SESSION', 'DATEBEGIN');
   |  END
   |  -- Returns the end date of the period
   |  FUNCTION GET_DATEEND() RETURNS DATE DETERMINISTIC
   |  AS
   |  BEGIN
   |    RETURN RDB$GET_CONTEXT('USER_SESSION', 'DATEEND');
   |  END
   |  -- Sets the date range of the working period
   |  PROCEDURE SET_DATERANGE(ADATEBEGIN DATE, ADATEEND DATE)
   |  AS
   |  BEGIN
   |    RDB$SET_CONTEXT('USER_SESSION', 'DATEBEGIN', ADATEBEGIN);
   |    RDB$SET_CONTEXT('USER_SESSION', 'DATEEND', ADATEEND);
   |  END
   |END

See alsoSection 5.12.1, “CREATE PACKAGE BODY”, Section 5.12.2, “DROP PACKAGE BODY”, Section 5.12.3, “RECREATE PACKAGE BODY”, ALTER PACKAGE