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.
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.
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.
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 .
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.
The general syntax for the two parts of a package follows:
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.
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:
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.
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.
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;
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.
The specification for the pets_inc package establishes the five public elements:
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;
The package body for pets_inc contains elements shown in the following table:
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.
There are several interesting facts to point out about the previous two package components:
Now that you've had an introduction to the various parts of the package, let's take a closer look at each package component.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.