065-068

Previous

Table of Contents

Next

Packages

PL/SQL allows developers to group stored procedures and functions into packages . Ideally, packages are groups of related procedures and functions, each handling a small part of a larger task. Organizing code into packages is quite useful when trying to modularize a system design.

A package specification (or spec) specifies the interface for the procedures and functions that will be contained in the package body. It goes almost without saying that the interfaces for procedures and functions defined in the package spec must be mirrored in the package body.

In addition to grouping related procedures and functions into a cohesive unit, packages also enable developers to define variables and data types that are global to all the procedures and functions contained inside a package (a good example of this is the Build_SUID_Matrix package contained in Appendix D, which defines a global cursor that is used by multiple procedures within the package body). These variables can then be used and reused to store data for reference between modules, allow all the modules to work with the same data, keep track of errors, and perform a multitude of other functions.

A typical package spec might look like the one shown in Listing 2.20.

Listing 2.20 A sample package spec.

 CREATE OR REPLACE PACKAGE Update_Student AS   -- Holds the name of the current object inside the package   vCurrentContext            varchar2 (61);   YES               CONSTANT char(1) := 'Y';   NO                CONSTANT char (1) := 'N';   PROCEDURE Calculate_Semester_GPA (SSN IN     number);   FUNCTION Get_Student_GPA (SSN IN    number) RETURN number; END Update_Student; 

The variable vCurrentContext is global within the package and can be referenced by both the procedure and function listed (as well as any other procedures or functions that exist in the package body).

Procedures and functions declared in the package spec are public and can be seen by other procedures and functions. However, the package body can also contain procedures and functions that are not declared as part of the package spec; these procedures and functions are private and can only be referenced by procedures and functions within the package body.

Like stored procedures and functions that stand alone, objects inside packages are executed with the privileges of the user who created the package.

Calling a packaged procedure or function is quite simple, as shown in the following code sample:

Procedure:

 Update_Student.Calculate_Semester_GPA (ssn => nSSN); 

Function:

 nGPA := Update_Student.Get_Student_GPA (ssn => nSSN); 

The only difference between calling a standalone stored procedure and calling a stored procedure inside a package is that the procedure inside the package must be prefaced with the name of the package.

Packages are covered more thoroughly in Chapter 4.

A PL/SQL Wish List

PL/SQL is not a perfect programming language (someone from Oracle told me in 1992 that it wasn t really a programming language at all). If you have any experience with other programming languages, you ll find that some really useful things are missing from PL/SQL. While these aren t serious deficiencies, their incorporation would make PL/SQL a bit less tedious to use at times.

The continue Statement

The continue statement in C is a marvelous tool, allowing you to immediately skip to the next iteration of a loop without executing any steps that occur inside the loop after the continue statement. Unfortunately, PL/SQL has only a GOTO statement to allow you to accomplish this simple task. The GOTO statement is illustrated by this example:

 LOOP    <<START_OF_LOOP>>   ...PL/SQL statements..    GOTO <<START_OF_LOOP>> END LOOP; 

You can also mirror this functionality by putting the contents of your loop inside a stored procedure and using the RETURN statement to exit out of the stored procedure if you determine that your data can t or shouldn t be processed any further. This example calls a procedure that holds the internal logic of the loop.

 LOOP    MyLoopContents (parameter1 => value); END LOOP; 

Listing 2.21 illustrates a loop that simulates the functionality of a continue statement by using a stored procedure.

Listing 2.21 Using a stored procedure to simulate a C continue statement.

 PROCEDURE MyLoopContents (parameter1    <datatype>) IS    <variable declarations> BEGIN     ...PL/SQL statements...    IF some_condition THEN        RETURN;    END IF; END; 

Previous

Table of Contents

Next