What Is a PL/SQL
Package?
A package is a schema object that groups logically
related PL/SQL types, variables, and subprograms. Packages usually have two
parts, a specification (spec) and a body; sometimes the body is unnecessary.
The specification is the interface to the package.
Advantages of PL/SQL Packages
Packages
have a long history in software engineering, offering important features for
reliable, maintainable, reusable code, often in team development efforts for
large systems.
Modularity
Packages
let you capture logically related types, items, and subprograms in a named
PL/SQL module. Each package is easy to understand, and the interfaces between
packages are simple, clear, and well defined. This aids application
development.
Easier Presentation Design
When
planning an application, all you need initially is the interface information in
the package specs. You can code and compile a spec without its body. Then,
stored subprograms that reference the package can be compiled as well. You need
not define the package bodies fully until you are ready to complete the
application.
Information Hiding
With
packages, you can specify which types, items, and subprograms are public
(visible and accessible) or private (hidden and inaccessible). For example, if
a package contains four subprograms, three might be public and one private. The
package hides the implementation of the private subprogram so that only the
package (not your application) is disturbed
if the execution changes. This make things easier maintenance
and enhancement. Also, by hiding implementation details from users, you protect
the integrity of the package.
It declares the types, variables, constants,
exceptions, cursors, and subprograms that can be referenced from outside the
package. The body defines the queries for the cursors and the code for the
subprograms.
You can think of the spec as an interface and of
the body as a black box. You can debug, enhance, or
replace a package body without changing the package spec.
Better Performance
When
you call a packaged subprogram for the first time, the whole package is loaded
into memory. Later calls to related subprograms in the package require no disk
I/O.
Packages
stop cascading dependencies and avoid unnecessary recompiling.
For example, if you change the body of a packaged function,
Oracle does not recompile other subprograms that call the function, these
subprograms only depend on the parameters and return value that are declared in
the spec, so they are only recompiled if the spec changes.
Example A Simple Package
Specification Without a Body
CREATE OR REPLACE PACKAGE SAL_PACK
IS
PROCEDURE EMPSAL(P_ID IN NUMBER);
END SAL_PACK;
Use EXEC to call a procedure in a package
exec SAL_PACK. EMPSAL(200)