10.4 Package Specification

Programming has always involved the "packaging" of related programs. This includes compiling a group of programs into a single object library and configuring the code of a single subsystem under one source code directory. Configuration management tools allow us to package our software under logical and functional subject areas so developers can easily locate programs for check-out and check-in purposes.

For some environments, the highest abstraction of a unit of software is a procedure. The management of the software repository then becomes the management of many individual programs. For other languages, procedures that collectively satisfy a major functional requirement compile into one object module, managed as one software unit. This is the case with C programming where multiple C functions compile as one C object file. Java packages contain Java classes and the compiler relies on a match between directory and package name .

A programmer who needs to use a procedure must first identify the module and then identify the specific function, procedure, or method needed. The C language uses a header file, a separate file from the C algorithmic code, that defines the interface ”this is the first step in learning how to use another programmer's C code.

Complexity of a programming language and the programming environment hinders development. IDEs are very successful with helping developers quickly locate reusable programs that are part of the development environment. Many of these IDE tools allow you to add your newly developed code to the library repository ”this enables a developer to search for a particular interface and easily locate the API of another developer. Oracle's JDeveloper and Procedure Builder are IDE tools that provide this type of rapid development environment support.

Without the use of an IDE, the features of a language can lead to small amounts of code reuse. One of the most powerful features of PL/SQL is the simplicity of the package specification. The PL/SQL language requires that the interface to a program collection compile into a single program unit. This program unit, the package specification, is not complex. It simply defines the API. The package body contains the details of the logic.

You can incorporate PL/SQL package specification in the design process. High-level design includes defining high-level interfaces and documenting what an implementation will do. The package specification identifies interfaces and includes and uses code and comments to define the interface. A set of package specifications can represent the complete top-level design of a system. A package body with pseudocode and comments can represent the detailed design.

In theory, should you see the code to a package specification on a programmer's desk, you should not know whether they are at the tail end of the design phase or the beginning of the code phase. The package specification overlaps the design and coding phase. The package specification defines a set of interfaces and operations performed on a set of objects, such as tables in a schema. It is also a compiled object in the database, subject to syntax rules and database privileges.

The package specification is a single ASCII file that compiles as a single program unit. The package body is also a single ASCII file. The body compiles only after a successful compilation of the specification. You can put the specification in the same file as the body.

10.4.1 Syntax and Style

The basic package specification syntax is:

 CREATE PACKAGE package_name IS  Type definitions for records, index-by tables,   varrays, nested tables   Constants   Exceptions   Global variable declarations  PROCEDURE procedure_name_1 (parameters & types);     PROCEDURE procedure_name_2 (parameter & types);     FUNCTION function_name_1 (parameters & types) RETURN type; END package_name; 

There is no order to the procedures and functions in the specification. Put them in a logical order that suits the developer.

The package body will include the PL/SQL code for each of the subprograms in the specification. Each subprogram in the specification must have an accompanying subprogram body.

The package specification can declare data types such as a record type, data declarations such as a record, and exceptions. All data objects declared in the package specification are global. Therefore, only declare what needs to be global.

The PROCEDURE statement in the package body, including the subprogram name, parameter names , parameter modes, and parameter types, must match the PROCEDURE statement in the specification. The same holds true for the FUNCTION subprogram.

The package body template for the preceding specification is:

 CREATE PACKAGE BODY package_name IS     PROCEDURE procedure_name_1 (parameters & types)     IS  local variables  BEGIN  body of code  END procedure_name_1;     PROCEDURE procedure_name_2 (parameter & types)     IS  local variables  BEGIN         body_of_code     END procedure_name_2;     FUNCTION function_name_1 (parameters & types) RETURN type     IS  local variables  BEGIN  body of code  RETURN statement;     END function_name_1; END package_name; 

A package specification may declare exceptions. Let's modify PACKAGE_NAME so it declares an exception. The exception name is INVALID_OPERATION. The package code is the same, but with an exception declaration. Exceptions are sometimes declared all in the beginning of the spec, sometimes all at the bottom. Most important, comments should indicate which subprograms potentially raise which exceptions.

 CREATE PACKAGE package_name IS     invalid_operation EXCEPTION;     PROCEDURE procedure_name_1 (parameters & types);     PROCEDURE procedure_name_2 (parameter & types);     FUNCTION function_name_1 (parameters & types) RETURN type; END package_name; 

The user of PACKAGE_NAME has a question: "What procedures raise this exception?" Assuming the answer is PROCEDURE_NAME_1, the application code, should it choose to handle a possible exception, looks like this:

 BEGIN  other code, etc  package_name.procedure_name_1(  parameters  );  other code, etc  EXCEPTION     WHEN package_name.invalid_operation THEN  do something  ; END; 

A package specification can also declare type definitions such as records. If a procedure is to return a student name and status, the option exists to declare a record type in the specification, as follows :

 CREATE PACKAGE package_name IS     TYPE student_rec_type IS RECORD (         student_name students.student_name%TYPE,         status students.status%TYPE);     PROCEDURE get_student         (stud_id  IN students.student_id%TYPE          stud_rec OUT student_rec_type); END package_name; 

The package users should drive the design. You should consider the user community when designing the specification. Overloading of procedures and functions is a frequent technique to making a package interface acceptable to a wide audience. A reasonable suggestion to the prior PACKAGE_NAME specification is to declare a function in the spec and have this return a student record type. Why? Because it can make the application using the package easier to write and easier to read. Such a modification changes the specification to the following:

 CREATE PACKAGE package_name IS     TYPE student_rec_type IS RECORD (         student_name students.student_name%TYPE,         status students.status%TYPE);     PROCEDURE get_student         (stud_id  IN students.student_id%TYPE          stud_rec OUT student_rec_type);     FUNCTION student         (stud_id  IN students.student_id%TYPE)     RETURN student_rec_type; END package_name; 

10.4.2 Developing a Specification

A Booch (Grady Booch) diagram, showing just one package in Figure 10-5, is an excellent paradigm for design. These diagrams are easy to whiteboard for technical group discussion. They provide a clear image of the software architecture. Booch diagrams specify what operations exist. This enables one to look at the system requirements and judge if the operations designed will satisfy the requirements.

Figure 10-5. Students Package.

We begin this section with a software requirement to implement procedures that perform operations on the STUDENTS table. Chapter 4 includes the DDL, entity diagram, and sample data for a student application.

We have some software to write. First, we need to implement the following:

• Add a new student.

• Return the total number of students.

We begin with a model depicting these operations. These operations belong together in a package because they each perform functions on the student's table. The package name will be STUDENTS_PKG.

The development must transition from the graphical model to an interface design. We use the package specification as a vehicle for describing this interface. The following paragraphs illustrate the development toward an interface specification.

ADD_STUDENT SUBPROGRAM

Below is a list of the columns of the STUDENTS table. Also refer to the STUDENTS table DDL in the data model demo of Chapter 4 (p. 146).

• STUDENT_ID

• STUDENT_NAME

• COLLEGE_MAJOR

• STATUS

• STATE

• LICENSE_NO

The STUDENT_ID column is the primary key and is generated with the sequence STUDENTS_PK_SEQ. This column value is determined in the body of the ADD_STUDENT procedure and will use the sequence attribute NEXTVAL during the INSERT statement.

The three columns after STUDENT_ID are mandatory column values. The last two are optional. We should make STATE and LICENSE_NO optional parameters in the procedure call. These will have a default of NULL in the procedure interface.

 PROCEDURE add_student (v_student_name   IN  students.student_name%TYPE,  v_college_major  IN  students.college_major%TYPE,  v_status         IN  students.status%TYPE,  v_state          IN  students.state%TYPE DEFAULT NULL,  v_license_no     IN  students.license_no%TYPE DEFAULT NULL); 

An application program can use this interface with the following call:

 students_pkg.add_student(name, major, status); 

The interface also permits :

 students_pkg.add_student(name,major,status,state,license_no); 

NO_OF_STUDENTS SUBPROGRAM

This should be a function. The requirement is to return an attribute of the entire student body. The following is a function with some flexibility. The user can use this function to get the number of students who have a specific major, a specific status, or a combination. The two parameters passed would be (1) the subject major description value from the MAJOR_LOOKUP table and (2) a status value of "Degree" or "Certificate."

 FUNCTION NO_OF_STUDENTS (v_major  IN major_lookup.major_desc%TYPE DEFAULT NULL,  v_status IN students.status%TYPE DEFAULT NULL) RETURN NUMBER; 

This interface permits the package user to write the following calls:

 -- get the number of students with an undeclared major. undeclared_major_count INTEGER :=    students_pkg.no_of_students(v_major=> 'Undeclared '); -- get the total number of students. student_count INTEGER := students_pkg.no_of_students(); -- get the number of degree-seeking biology students. biology_degrees INTEGER :=    students_pkg.no_of_students         (v_major => 'Biology',          v_status => 'Degree'); 

The STATUS column indicates the student's status with the school and has a CHECK constraint with valid values of "Degree" and "Certificate."

PACKAGE SPECIFICATION

The package specification now defines the interfaces. It initially appears to meet the requirements of adding a student and returning a count of students in the school. This is a starting point. We can add subprograms as needed. Additional procedures and functions can enhance the overall functionality of the package.

 CREATE OR REPLACE PACKAGE students_pkg IS   PROCEDURE add_student     (v_student_name   IN  students.student_name%TYPE,      v_college_major  IN  students.college_major%TYPE,      v_status         IN  students.status%TYPE,      v_state          IN  students.state%TYPE DEFAULT NULL,      v_license_no     IN  students.license_no%TYPE DEFAULT NULL);   FUNCTION NO_OF_STUDENTS     (v_major  IN major_lookup.major_desc%TYPE DEFAULT NULL,      v_status IN students.status%TYPE DEFAULT NULL)   RETURN NUMBER; END students_pkg;