#

| Chapter 6 PL/SQL |

## 6.3 Packages

Now that we've looked at PL/SQL's structure and its most common programming constructs, we're ready to group these elements into tidy little structures called packages. A package is a container (hence the name) for other PL/SQL elements, such as variables and constants, procedures and functions, and datatype definitions.

A package has two parts: a specification and a body. The specification is a sort of table of contents that lists the items in the package. The body contains the implementations for each item. For example, the specification tells us "This package contains a procedure named `foo', which has the following parameters." The body of the package contains the actual implementation of foo.

Packages are the most powerful and useful PL/SQL constructs because they help us build standard code libraries with well-defined application programming interfaces (APIs). In a web environment, for example, you can create standard libraries to handle security, page formatting, or list of values (LOV) generation. Each time you build a new application, you can just plunk in calls to these standard libraries, rather than reinventing them for each new system. Packages are also excellent for building abstract data types (ADTs), a fancy terminology for structures like stacks, lists, and queues.

Prebuilt packages with clear APIs encourage software reuse, the Holy Grail of software engineering. In this final section, we'll learn how to use packages effectively. We'll start by looking at the structure of the specification and the body, and then move on to how to use a package within other programs. After that, we'll look at how to hide the implementation details of a package to create a "black box." Finally, we'll look at package persistence.

### 6.3.1 The Package Specification

The specification defines the package's API, which governs every aspect of how the package is used. The specification lists the headers of the procedures and functions in the API, as well as any variables, types, cursors, or constants necessary to interface with the package. These last items are global variables, accessible both from inside and outside the package. The headers and declarations in the specification are called public elements because they are the interface between the package and the outside world.

You might see the specification as an afterthought, if not a downright nuisance, that stands in the way of your real work. Nothing could be further from the truth. The success or failure of a package almost always depends on a clean, crisp interface that is simple to understand and use.

### 6.3.2 The Package Body

The package body contains the actual code for the modules in the specification. It must include a complete implementation of each of these modules. Additionally, it can also include procedures and functions not listed in the specification, as well as declarations for variables, types, cursors, and constants. These elements, invisible to the outside world, are private because they can be referenced only from within the body itself.

### 6.3.3 Example

Designing a good package takes practice. A package should be rich enough to support a wide variety of complex activities, yet simple enough to grasp quickly. As with any other art, the best place to learn package design is from the classics, so in this section we'll create a stack package based on an index-by table. Here's the specification:

>     /* Formatted by PL/Formatter v.1.1.13 */
>     CREATE OR REPLACE PACKAGE stack
>     IS

>        /*------------------------------------------------------
>        || Global type declaration
>        */------------------------------------------------------
>        TYPE stack_array IS TABLE OF VARCHAR2(2000)
>           INDEX BY BINARY_INTEGER;

>        /*-------------------------------------------------------
>        || Global API declaration
>        */-------------------------------------------------------
>        -- Initialize the stack
>        PROCEDURE init;

>        -- Push an item onto the stack
>        PROCEDURE push (item IN VARCHAR2);

>        -- Return the first element on the stack
>        FUNCTION pop
>           RETURN VARCHAR2;

>        -- Return a boolean if the stack is empty
>        FUNCTION is_empty
>           RETURN BOOLEAN;

>        -- Copy the stack into an array
>        PROCEDURE copy_to_array (
>           s OUT stack_array,
>           num_elements OUT NUMBER
>           );

>     END stack;

Our next step is to actually implement each of the procedures and functions listed in the specification. Here's the body of the stack package:

>     /* Formatted by PL/Formatter v.1.1.13 */
>     CREATE OR REPLACE PACKAGE BODY stack
>     IS

>        /*------------------------------------------------------
>        || Local declarations
>        */------------------------------------------------------
>        -- Declare stack data structure
>        local_stack stack_array;
>        -- Declare index to top of stack
>        top NUMBER DEFAULT 0;

>        /*-------------------------------------------------------
>        || Implementation
>        */-------------------------------------------------------

>        /*
>        || Initialize the stack
>        */
>        PROCEDURE init
>        IS
>        BEGIN
>           top := 0;
>        END init;

>        /*
>        || Push an item onto the stack.
>        || Since PL/SQL arrays are unconstrained, we never have
>        || to worry about pushing too many elements!
>        */
>        PROCEDURE push (item IN VARCHAR2)
>        IS
>        BEGIN
>           top := top + 1;
>           local_stack  (top) := item;
>        END push;

>        /*
>        || Return a boolean if the stack is empty
>        */
>        FUNCTION is_empty
>           RETURN BOOLEAN
>        IS
>        BEGIN
>           IF top = 0
>           THEN
>              RETURN TRUE;
>           ELSE
>              RETURN FALSE;
>           END IF;
>        END is_empty;

>        /*
>        || Return the first element on the stack.
>        || Return NULL if the stack is empty.
>        */
>        FUNCTION pop
>           RETURN VARCHAR2
>        IS
>           item_to_return VARCHAR2(2000);
>        BEGIN
>           IF is_empty
>           THEN
>              item_to_return := NULL;
>           ELSE
>              item_to_return := local_stack (top);
>              top := top - 1;
>           END IF;
>           RETURN item_to_return;
>        END pop;

>        /*
>        || Copy the stack into an array
>        */
>        PROCEDURE copy_to_array (
>           s OUT stack_array,
>           num_elements OUT NUMBER
>           )
>        IS
>        BEGIN
>           -- Set the number of elements
>           num_elements := top;
>           -- Load each element into the array
>           FOR i IN 1 .. top
>           LOOP
>              s  (i) := local_stack (i);
>           END LOOP;
>        END copy_to_array;

>     END stack;

### 6.3.4 Using a Package

After we've created the package specification and body, we can start using it in other procedures and functions. You use the following syntax to refer to a public element (variable, procedure, function, etc.) in a package:

>     package_name.public_element_name

The following sample illustrates how to use the stack package developed in the last section:

>     /* Formatted by PL/Formatter v.1.1.13 */
>     CREATE OR REPLACE PROCEDURE test_stack
>     IS
>        stack_copy stack.stack_array;
>        stack_size NUMBER;
>     BEGIN
>        --Push some test data onto the stack
>        FOR i IN 1 .. 10
>        LOOP
>           stack.push (i);
>        END LOOP;

>        -- Make a copy of the stack

>        stack.copy_to_array (stack_copy, stack_size);

>        -- Pop all elements off the stack
>        WHILE NOT stack.is_empty
>        LOOP
>           DBMS_OUTPUT.put_line (stack.pop);
>        END LOOP;

>        -- Print the copied elements
>        FOR i IN 1 .. stack_size
>        LOOP
>           DBMS_OUTPUT.put_line (stack_copy (i));
>        END LOOP;

>     END test_stack;

### 6.3.5 Information Hiding

You might have noticed in the stack example that we declared the local_stack and the top variable inside the body, rather than in the specification. This is an example of information hiding, a technique that's used to hide a package's implementation details from its users.

A package should be a black box; input comes in one side and predictable output goes out the other. The details between these steps should be invisible to everyone except the package's developer. It's amazing how often knowing how something works can get us into trouble. How many times have you written one program to take advantage of a bug in another? These shortcuts turn bugs into permanent fixtures. Information hiding eliminates the possibility of this problem by forcing everyone to use the package the way it was intended to be used.

Access to the internal workings of a package can cause problems even when the workaround seems perfectly innocent. To return to our original example, if the local_stack and top variables in the stack example were declared in the package specification, and not hidden away in the body, a developer in a rush might be tempted to write a program to bypass the stack directly, as you can see here:

>     /*
>     || Quick procedure to print stack
>     */
>     CREATE OR REPLACE PROCEDURE show_stack
>        i NUMBER DEFAULT 0;
>     IS
>     BEGIN
>        FOR i IN REVERSE 1 .. STACK.top
>        LOOP
>           DBMS_OUTPUT.put_line (STACK.local_stack (i));
>        END LOOP;
>     END;

This shortcut depends on the stack being implemented as an index-by table. If we decided to redesign the package to represent the stack in another way (for example, using an object type we can store directly in the database), it would break this program and every other program that made a similar use of the package's public data structures.

### 6.3.6 Variable Persistence Throughout a Session

Package variables, whether they are declared in the specification or the body, maintain their values throughout a session. All values are initialized when a session begins and are lost when it ends. This is called persistence because the values remain, or persist, even when the package is not being directly used. Only package variables declared as part of the specification or body persist. Variables declared inside a procedure or function do not.

For example, suppose you log into SQL*Plus and push a few elements onto the stack. After that, you execute a few SELECT statements, issue some updates, and describe a table or two. Finally, before you're ready to log out, you call the test_stack procedure. When it executes, not only does it print "1" through "10," it also prints the elements you manually added at the beginning of the session. PL/SQL remembered the previous elements, even though you've been doing other things. However, if you logged out and reconnected before running the test_stack procedure, the previous elements disappear, because variables persist only throughout a single session.

In Chapter 7, The PL/SQL Toolkit, we'll look at some strategies to mimic persistence in the web environment.

6.2 Programming Constructs |  | 6.4 PL/SQL Tools

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.