A package provides an extra layer of code and structure over that of an individual module. Many of the concepts needed to understand a package's structure will be familiar to you. In fact, a package is very similar in structure to a PL/SQL module that has local modules defined within it.
Whereas a module has a header and a body, a package has a specification and a body. Just as the module's header explains to a developer how to call that module, the package specification describes the different elements of the package that can be called. Beyond that, however, there are key differences between the constructs in the module and in the package.
A module's specification and body are connected by the IS keyword; both are required and one cannot be written without the other. The specification determines how you call the module. The body, after the IS keyword, contains the code that is executed when the function is used. These two components of a module are coded together and are completely inseparable.
A package also has a specification and a body, but the package's two parts are structured differently, and have a different significance, from those for a single module. With a package, the specification and body are completely distinct objects. You can write and compile the specification independently of the body. When you create and replace stored packages in the database, you perform this action separately for each of the specification and body.
This separation of specification and body allows you to employ top-down design techniques in a powerful way. Don't worry about the details of how a procedure or function is going to do its job. Just concentrate on the different modules you need and how they should be connected together.
16.2.1 The Specification
The package specification contains the definition or specification of all elements in the package that may be referenced outside of the package. These are called the public elements of the package (see the section entitled Section 16.2.4, "Public and Private Package Elements"" for more information). Figure 16.1 shows an example of a package specification containing two module definitions.
Figure 16.1: The specification of sp_timer package
Like the module, the package specification contains all the code that is needed for a developer to understand how to call the objects in the package. A developer should never have to examine the code behind the specification (which is the body) in order to understand how to use and benefit from the package.
16.2.2 The Body
The body of the package contains all the code behind the package specification: the implementation of the modules, cursors, and other objects. Figure 16.2 illustrates the body required to implement the specification of the sp_timer package shown in Figure 16.1.
Figure 16.2: The body of sp_timer package
The body may also contain elements that do not appear in the specification. These are called the private elements of the package. A private element cannot be referenced outside of the package, since it does not appear in the specification.
The body of the package resembles a standalone module's declaration section. It contains both declarations of variables and the definitions of all package modules. The package body may also contain an execution section, which is called the initialization section because it is only run once, to initialize the package.
16.2.3 Package Syntax
The general syntax for the two parts of a package follows:
The package specification:
PACKAGE package_name IS [ declarations of variables and types ] [ specifications of cursors ] [ specifications of modules ] END [ package_name ];
You can declare variables and include specifications of both cursors and modules (and only the specifications). You must have at least one declaration or specification statement in the package specification.
Notice that the package specification has its own BEGIN-END block syntax. This enables its independent existence and compilation from the package body.
The package body:
PACKAGE BODY package_name IS [ declarations of variables and types ] [ specification and SELECT statement of cursors ] [ specification and body of modules ] [ BEGIN executable statements ] [ EXCEPTION exception handlers ] END [ package_name ];
In the body you can declare other variables, but you do not repeat the declarations in the specification. The body contains the full implementation of cursors and modules. In the case of a cursor, the package body contains both specification and SQL statement for the cursor. In the case of a module the package body contains both the specification and body of the module.
The BEGIN keyword indicates the presence of an execution or initialization section for the package. This section can also optionally include an exception section.
As with a procedure or function, you can add the name of the package, as a label, after the END keyword in both the specification and package.
16.2.4 Public and Private Package Elements
A central concept of packages is the privacy level of its elements. One of the most valuable aspects of a package is its ability to actually enforce information hiding. With a package you can not only modularize your secrets behind a procedural interface, you can keep these parts of your application completely private.
An element of a package, whether it is a variable or a module, can either be private or public, as defined below:
Public
Defined in the specification. A public element can be referenced from other programs and PL/SQL blocks.
Private
Defined only in the body of the package, but does not appear in the specification. A private element cannot be referenced outside of the package. Any other element of the package may, however, reference and use a private element. Private elements in a package must be defined before they can be referenced by other elements of the package. If, in other words, a public procedure calls a private function, that function must be defined above the public procedure in the package body. You can, alternatively, use a forward declaration if you wish to keep your private programs at the bottom of the package body (see Chapter 15, Procedures and Functions).
If you find that a formerly private object such as a module or cursor should instead be made public, simply add that object to the package specification and recompile. It will then be visible outside of the package.
The distinct difference between public and private elements gives PL/SQL developers unprecedented control over their data structures and programs. Figure 16.3 shows a Booch diagram[1] for the package that displays private and public package elements.
[1] This diagram is named after Grady Booch, who pioneered many of the ideas of the package, particularly in the context of object-oriented design.
Figure 16.3: Booch diagram showing public and private package elements
The diagram offers a clear representation of the public/private character of PL/SQL packages. The large rectangle establishes the graphical boundary of the package. A program external to the package may only call a package element if a part of the element extends past the boundary of the package. Thus, all public elements defined in the specification straddle the package boundary. Part of the element is inside the boundary, and part lies outside, accessible to other programs.
All the objects (data and modules) that are completely surrounded by the boundary of the package are private objects. These are defined only in the package body and do not appear in the specification. Because they are wholly contained, no external program can reference those elements.
Because the border of the public elements exists both outside and inside the package boundary, all elements in the package (private and public) can use those elements.
The next section offers a quick tour of a simple package which illustrates these concepts.
16.2.5 How to Reference Package Elements
A package owns its objects, just as a table owns its columns. You use the same dot notation to provide a fully qualified specification for a package's object as you would for a table's column.
The following package specification declares a constant, an exception, a cursor, and several modules:
PACKAGE pets_inc IS max_pets_in_facility CONSTANT INTEGER := 120; pet_is_sick EXCEPTION; CURSOR pet_cur RETURN pet%ROWTYPE; FUNCTION next_pet_shots (pet_id_in IN NUMBER) RETURN DATE; PROCEDURE set_schedule (pet_id_in IN NUMBER); END pets_inc;
To reference any of these objects, I preface the object name with the package name, as follows:
BEGIN IF pets_inc.max_pets_in_facility > 100 THEN ... END IF; EXCEPTION WHEN pets_inc.pet_is_sick THEN ... END; OPEN pets_inc.pet_cur; :pet_master.next_appointment := pets_inc.next_pet_shots (:pet_master.pet_id);
If you do not preface the call to next_pet_shots with the package name, pets_inc, PL/SQL is not able to resolve the reference and the compile fails.
So, the rule for referencing package elements is simple and clear: To reference a stored package element, use dot notation. The one exception is that inside a package, you do not need to qualify references to other elements of that package. PL/SQL will automatically resolve your reference within the scope of the package.
Suppose, for example, that the set_schedule procedure of pets_inc references the max_pets_in_facility constant. Such a reference would be unqualified as shown here:
PROCEDURE set_schedule (pet_id_in IN NUMBER) IS ... BEGIN ... IF total_pets < max_pets_in_facility THEN ... END IF; END;
Of course, if you want to reference the element of a second package inside the current package, you will need to include the name of that package.
16.2.6 Quick Tour of a Package
The pet maintenance package defined below is used by veterinarians to keep track of their patients and to determine when a pet needs another visit. Its specification identifies the public elements of the package. The body implements those elements and also creates two private elements.
16.2.6.1 The pets_inc package specification
The specification for the pets_inc package establishes the five public elements:
Name
Type
Description
petid_type
Subtype definition
Creates a programmer-defined subtype for the pet table primary key.
petid_nu
Variable declaration
Represents the primary key in pet table.
pet_cur
Cursor specification
Retrieves information about specified pet.
next_pet_shots
Function specification
Returns the date for next shots.
set_schedule
Procedure specification
Sets the schedule for the specified pet.
Here is the pets_inc package specification:
PACKAGE pets_inc IS SUBTYPE petid_type IS pet.pet_id%TYPE; petid_nu petid_type; CURSOR pet_cur (pet_name_in IN VARCHAR2) RETURN pet%ROWTYPE; FUNCTION next_pet_shots (pet_id_in IN petid_type) RETURN DATE; PROCEDURE set_schedule (pet_id_in IN petid_type) END pets_inc;
The header for the package specification simply states PACKAGE. You do not explicitly indicate that it is the specification, as in PACKAGE SPECIFICATION. Instead, when you create the body of a package, you indicate explicitly in the first line of the definition that you are defining the body of the pets_inc package.
Since all of these elements are in the package, I can reference them in other programs, such as the following procedure:
PROCEDURE show_next_visit (pet_in IN VARCHAR2) IS next_visit DATE; /* Declare record to receive row fetched from package cursor. */ pet_rec pets_inc.pet_cur%ROWTYPE; BEGIN /* Open the package-based cursor. */ OPEN pets_inc.pet_cur (pet_in); /* Fetch from cursor into local record. */ FETCH pets_inc.pet_cur INTO pet_rec; IF pets_inc.pet_cur%FOUND THEN /* Call packaged function to get next visit date. */ next_visit := pets_inc.next_pet_shots (pet_rec.pet_id); /* Display the information. */ DBMS_OUTPUT.PUT_LINE ('Schedule next visit for ' || pet_in || ' on ' || TO_CHAR (next_visit)); END IF; CLOSE pets_inc.pet_cur; END;
16.2.6.2 The pets_inc package body
The package body for pets_inc contains elements shown in the following table:
Name
Type
Description
max_date
Constant declaration
Private variable. Maximum date llowed.
pet_cur
Cursor declaration
The cursor specification and SQL statement that retrieves information about specified pet.
pet_status
Function definition
Private module. The specification and body for the function that returns the status of teh pet.
next_pet_shots
Function definition
The specification and body for the function that returns date for next shots.
set_schedule
Procedure definition
The specification and body for the procedure that sets the schedule for the specified pet.
Here is the pets_inc package body:
PACKAGE BODY pets_inc IS max_date CONSTANT DATE := SYSDATE + 10; CURSOR pet_cur (pet_name_in IN VARCHAR2) RETURN pet%ROWTYPE IS SELECT * FROM pet; FUNCTION pet_status (pet_id_in IN petid_type) RETURN VARCHAR2 IS BEGIN ... code behind the module ... END; FUNCTION next_pet_shots (pet_id_in IN petid_type) RETURN DATE IS BEGIN ... the code behind the module ... END; PROCEDURE set_schedule (pet_id_in IN petid_type) IS BEGIN ... the code behind the module ... END; END pets_inc;
The body for the pet maintenance package contains the SELECT statement for the pet_cur cursor as well as the code required to implement all the modules.
This package body contains two private elements: max_date and pet_status. The max_date constant is used inside the package modules to validate dates that are manipulated in the package. The pet_status function is used by other modules to retrieve the status of the pet.
Because these elements are private, they can only be referenced by other elements of the package.
16.2.6.3 Observations about pets_inc
There are several interesting facts to point out about the previous two package components:
The package specification does not contain any executable statements or exception handlers. A specification only specifies, or declares, those objects in the package that are public -- that is, visible outside of the package and callable by other programs.
The declaration of the petid_nu variable and the petid_type subtype are not repeated inside the body. The declaration in the specification is enough for the whole package. Any module in the body can reference variables declared in the specification.
Both the body and the specification of pets_inc are actually extended declaration sections. The package body can, however, also contain execution and exception sections. These two parts of the package body make up the initialization section of the package, which is explored later in this chapter.
Now that you've had an introduction to the various parts of the package, let's take a closer look at each package component.
16.1 The Benefits of Packages
16.3 The Package Specification
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
