 |
|
PL/SQL Packages
Oracle Tips by
Burleson
|
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.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
