5.12 PACKAGE BODY

5.12.1 CREATE PACKAGE BODY

Used forCreating the 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.12.1.1 CREATE PACKAGE BODY Statement Parameters

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 appropriate error will be 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) is 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, ALTER PACKAGE BODY, Section 5.12.3, DROP PACKAGE BODY, Section 5.12.4, RECREATE PACKAGE BODY, CREATE PACKAGE

5.12.2 ALTER PACKAGE BODY

Used forAltering the package body

Available inDSQL

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 Section 5.12.1, CREATE PACKAGE BODY for more details.

5.12.2.1 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

5.12.2.2 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

See alsoSection 5.12.1, CREATE PACKAGE BODY, Section 5.12.3, DROP PACKAGE BODY, Section 5.12.4, RECREATE PACKAGE BODY, ALTER PACKAGE

5.12.3 DROP PACKAGE BODY

Used forDropping a package body

Available inDSQL

Syntax

DROP PACKAGE package_name

Table 5.12.3.1 DROP PACKAGE BODY Statement Parameters

The DROP PACKAGE BODY statement deletes the package body.

5.12.3.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.3.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.2, ALTER PACKAGE BODY, DROP PACKAGE

5.12.4 RECREATE PACKAGE BODY

Used forCreating a new or recreating an existing 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.4.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, ALTER PACKAGE BODY, Section 5.12.3, DROP PACKAGE BODY, ALTER PACKAGE