A package is a database object that groups together logically related procedures/functions, and other constructs such as variables, constants, cursors, PL/SQL types and exceptions.
One may well ask: when it’s possible to write standalone procedures/functions and define related variables within them, why have packages at all?
This article highlights six key reasons that make PL/SQL packages one of the most useful elements of PL/SQL programming. In a PL/SQL development environment, you simply cannot do without them.
Organized code management
Packages act like containers for related subprograms, types and items. Say you have several subprograms for inventory management (add_item, modify_item and so on) in your application – all of it can be bundled into a single inventory_mgmt package. This is of great help in application development as it makes code easy to locate, understand and reuse.
Easy (top-down) application design
A crucial feature of a package is the separation between its public interface (specification or specs), and its implementation (body). You can code and compile a package specification without its body. All you need for dependent programs to work is the interface information in the package specs. The package as well as the stored subprograms that reference it, will compile successfully. This means that you can develop the application in a planned, modular fashion and need not face bottlenecks due to incomplete implementation details. This is of special significance in top-down application development.
Easy implementation changes
Changes to a subprogram can create havoc with other programs that reference it. Not so with packages. If only code in the package body is changed, no change is needed in the dependent objects. Not even a recompile is required.
Security and maintainability through private code
With packages, you can specify which subprograms, types and items are public (visible and accessible outside the package) or private (hidden and inaccessible outside the package). For example, if a package contains four subprograms, three might be public and one private. The ability to have private portions in a package ensures that subprograms and other constructs that need not, or SHOULD not, be accessible publicly – remain hidden. This protects the integrity of the package, and also simplifies maintenance – any change to a private element impacts only this package and nothing else.
Session-wide persistence of public variables
Packaged public variables and cursors persist for the duration of a session. They let you maintain data across transactions without storing it in the database. All subprograms that run in the environment share public package variables and cursors, and will read/edit the same values. This unique feature of PL/SQL packages can be used by applications for storing values with session-wide relevance, such as trace/debug options. (Caution: Judicious use is recommended for editing public variable values, as it can make the application messy and error-prone.)
Better performance
When you invoke a packaged subprogram for the first time, the entire package is loaded into memory. Later calls to subprograms in the same package need no disk I/O. This translates to better performance.
PL/SQL Package Benefits: Summary
This article lists key benefits provided by PL/SQL packages for PL/SQL subprograms over standalone procedures and functions. These are:
- Organized code management
- Easy (top-down) application design
- Easy changes to implementation
- Security and maintainability
- Session-wide persistence of variables
- Better berformance
