 |
|
PL/SQL Packages
Oracle Tips by
Burleson
|
A package is a group of procedures, functions, and
variables, often grouped together because they accomplish related
tasks. In PL/SQL, packages consist of two parts: the package
specification, or package spec, and the package body.
The Package Spec
The package spec can be clearly illustrated by using a form-based
analogy. When a form displays on your terminal, certain buttons are
displayed as well. The code behind each button is a call to a
procedure or function. Figure 6.1 illustrates this model within the
context of a package.
Figure 6.1 The package
model.
Simply put, a package spec defines how other objects within an
Oracle database interact with the package. The package spec contains
the following types of definitions:
- Global variables, constants, user-defined
datatypes, and user-defined exceptions
- Procedure declarations (interface only)
- Function declarations (interface only)
Every construct and object defined within a package spec is public
and can be referenced by any block of PL/SQL code. Objects within a
package body that are not defined within the package spec are private
objects and can be referenced only by objects within the package.
Global Variables
The package spec can contain definitions for variables, constants,
datatypes, and user-defined exceptions that can be referenced by the
package spec and body, as well as by outside objects. Listing 6.1
illustrates the definition of these constructs.
Listing 6.1 The definition of global constructs in a package
spec.
PACKAGE System_Errors
IS
TYPE MessageParts_type IS TABLE OF varchar2 (20)
INDEX BY binary_integer;
vLastError varchar2 (100);
THIS_PACKAGE CONSTANT varchar2 (13) := 'System_Errors';
xUNHANDLED_ERROR EXCEPTION;
FUNCTION Build_Message (vObjectName IN varchar2,
iErrorCode IN integer,
vErrorString IN varchar2);
PROCEDURE Log_Error (vObjectName IN varchar2,
vErrorString IN varchar2,
vErrorData IN varchar2,
iErrorCode IN integer);
END System_Errors;
Each of the highlighted constructs can be referenced by all the
procedures and functions contained within the package body. Objects
outside the package can also reference the constructs by prefacing the
name of the construct with the name of the package, as shown in
Listing 6.2.
Listing 6.2 Referencing an object within a package.
PROCEDURE Local_Error_Log (vObjectName IN varchar2,
iErrorCode IN integer,
vErrorString IN varchar2)
IS
vMessageParts System_Errors.MessageParts_type;
BEGIN
System_Errors.Build_Message (vObjectName => vObjectName,
iErrorCode => iErrorCode,
vErrorString => vErrorString);
END Local_Error_Log;
Each construct is specific to the user who references it. For
example, user A initializes vLastError to ‘Student does not
exist’ and user B initializes vLastError to ‘Attempted
division by zero’. Neither user is overwriting the value of the
other user’s variable.
The Bodyless
Package
Large systems often have a number of definitions that need to be
standardized throughout the system. You can define a package spec that
contains these definitions and reference the package spec from any
object. Although packages are usually defined using both a spec and a
body, Oracle doesn’t require every package spec to have an associated
package body. Consider this package spec:
PACKAGE Globals
IS
FIELD_DELIMITER CONSTANT varchar2 (1) := chr (29);
ROW_DELIMITER CONSTANT varchar2 (1) := chr (30);
MAX_LENGTH CONSTANT integer := 255;
TYPE Student_rec_TYPE
IS
RECORD (first_name varchar2 (20),
last_name varchar2 (20),
middle_initial varchar2 (1),
ssn varchar2 (9));
TYPE SubStrings_tab_TYPE IS TABLE OF varchar2 (20)
INDEX BY binary_integer;
END Globals;
Any stored PL/SQL object can reference any of the
constants or datatypes defined in the Globals package spec.
Since the package doesn’t contain any actual procedures or functions,
there’s no need to define a package body that corresponds to the spec.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
