The notion of packaging the code components of a database operation addresses has several advantagrs:

Modularisation

Blocks of interdependent code are grouped into logical modules, as done in other programming languages.

In programming, it is well recognised that grouping code in various ways, in namespaces, units or classes, for example, is a good thing. This is not possible with standard stored procedures and functions in the database. Although they can be grouped in different script files, two problems remain:

1. The grouping is not represented in the database metadata.

2. Scripted routines all participate in a flat namespace and are callable by everyone (we are not referring to security permissions here).

Easier tracking of dependencies

Packages make it easy to track dependencies between a collection of related routines, as well as between this collection and other routines, both packaged and unpackaged.

Whenever a packaged routine determines that it uses a certain database object, a dependency on that object is registered in Firebird’s system tables. Thereafter, to drop, or maybe alter that object, you first need to remove what depends on it. Since the dependency on other objects only exists for the package body, and not the package body, this package body can easily be removed, even if some other object depends on this package. When the body is dropped, the header remains, allowing you to recreate its body once the changes related to the removed object are done.

Simplify permission management

As Firebird runs routines with the caller privileges, it is necessary also to grant resource usage to each routine when these resources would not be directly accessible to the caller. Usage of each routine needs to be granted to users and/or roles.

Packaged routines do not have individual privileges. The privileges apply to the package as a whole. Privileges granted to packages are valid for all package body routines, including private ones, but are stored for the package header. An EXECUTE privilege on a package granted to a user (or other object), grants that user the privilege to execute all routines defined in the package header.

For example

  |GRANT SELECT ON TABLE secret TO PACKAGE pk_secret;
  |GRANT EXECUTE ON PACKAGE pk_secret TO ROLE role_secret;

Private scopes

Stored procedures and functions can be privates; that is, make them available only for internal usage within the defining package.

All programming languages have the notion of routine scope, which is not possible without some form of grouping. Firebird packages also work like Delphi units in this regard. If a routine is not declared in the package header (interface) and is implemented in the body (implementation), it becomes a private routine. A private routine can only be called from inside its package.

For information on creating packages, see CREATE PACKAGE, CREATE PACKAGE BODY

For information on modifying existing package header or bodies, see ALTER PACKAGE, CREATE OR ALTER PACKAGE, RECREATE PACKAGE, RECREATE PACKAGE BODY

For information on deleting a package, see DROP PACKAGE, DROP PACKAGE BODY