FirebirdSQL logo

CREATE PACKAGE BODY

Creates a package body

Available in

DSQL

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 1. CREATE PACKAGE BODY Statement Parameters
Parameter Description

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 implementationEssentially 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.

Note
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.

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

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

ALTER PACKAGE BODY

Alters a package body

Available in

DSQL

Syntax
ALTER PACKAGE BODY name
AS
BEGIN
  [ <package_item> ... ]
  [ <package_body_item> ... ]
END

!! See syntax of CREATE PACKAGE BODY for further rules !!

The ALTER PACKAGE BODY statement modifies the package body.It can be used to change the definition and implementation of procedures and functions of the package body.

See [fblangref50-ddl-pkg-body-create] for more details.

Who Can Alter a Package Body

The ALTER PACKAGE BODY statement can be executed by:

  • Administrators

  • The owner of the package

  • Users with the ALTER ANY PACKAGE privilege

Examples of ALTER PACKAGE BODY

Modifying the package body
ALTER 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

DROP PACKAGE BODY

Drops a package body

Available in

DSQL

Syntax
DROP PACKAGE package_name
Table 1. DROP PACKAGE BODY Statement Parameters
Parameter Description

package_name

Package name

The DROP PACKAGE BODY statement deletes the package body.

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

Examples of DROP PACKAGE BODY

Dropping the package body
DROP PACKAGE BODY APP_VAR;

RECREATE PACKAGE BODY

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

Available in

DSQL

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 [fblangref50-ddl-pkg-body-create] for more details.

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