 |
|
The Package Body
Oracle Tips by
Burleson
|
The package body includes the definition of procedures and
functions declared in the package spec and, like the package spec, can
also include variables, constants, user-defined exceptions, and
user-defined datatypes.
Unlike objects declared in a package spec, variables and other
constructs defined within a package body are private to the package
body. These constructs can only be referenced by procedures and
functions within the package body.
Figure 6.2 illustrates the relationship of objects defined within a
package body to a package spec.
Figure 6.2 Relating a package body to a package spec.
Procedures
Procedures are created inside a package without using the CREATE
PROCEDURE command. Instead, a procedure’s definition is defined as
part of the CREATE PACKAGE BODY command, as shown in Listing
6.7.
Listing 6.7 Creating a procedure inside a package body.
CREATE OR REPLACE
PACKAGE BODY GPA_Calculations
AS
FUNCTION Overall_GPA (iStudentSSN IN integer)
RETURN number
IS
iTotalCredits integer := 0;
iTotalHours integer := 0;
nGPA number := 0;
CURSOR StudentClasses_cur
IS
SELECT course_credits, course_grade
FROM SCHEDULED_CLASSES
WHERE ssn = iStudentSSN
AND audit_flag = 'N'
AND no_credit_flag = 'N';
BEGIN
FOR StudentClasses_rec IN StudentClasses_cur (iStudentSSN) LOOP
iTotalCredits := iTotalCredits
+ StudentClasses_rec.course_credits;
iTotalHours := iTotalHours
+ StudentClasses_rec.course_hours;
END LOOP;
nGPA := (iTotalCredits / iTotalHours);
RETURN nGPA;
END Overall_GPA;
PROCEDURE Calculate_GPA (iStudentSSN IN integer)
IS
CURSOR Students_cur
IS
SELECT ssn
FROM STUDENTS;
BEGIN
FOR Students_rec IN Students_cur LOOP
UPDATE STUDENTS
SET overall_gpa = Overall_GPA (iStudentSSN => iStudentSSN)
WHERE CURRENT OF Students_cur;
END LOOP;
END Calculate_GPA;
END GPA_Calculations;
Functions
Like procedures, functions are created within a package body as
part of the CREATE PACKAGE BODY command, as shown in Listing
6.8.
Listing 6.8 Creating a function inside a package body.
FUNCTION Overall_GPA (iStudentSSN IN integer)
RETURN number
IS
iTotalCredits integer := 0;
iTotalHours integer := 0;
nGPA number := 0;
CURSOR StudentClasses_cur
IS
SELECT course_credits, course_grade
FROM SCHEDULED_CLASSES
WHERE ssn = iStudentSSN
AND audit_flag = 'N'
AND no_credit_flag = 'N';
BEGIN
FOR StudentClasses_rec IN StudentClasses_cur (iStudentSSN) LOOP
iTotalCredits := iTotalCredits
+ StudentClasses_rec.course_credits;
iTotalHours := iTotalHours
+ StudentClasses_rec.course_hours;
END LOOP;
nGPA := (iTotalCredits / iTotalHours);
RETURN nGPA;
END Overall_GPA;
PROCEDURE Calculate_GPA (iStudentSSN IN integer)
IS
CURSOR Students_cur
IS
SELECT ssn
FROM STUDENTS;
BEGIN
FOR Students_rec IN Students_cur LOOP
UPDATE STUDENTS
SET overall_gpa = Overall_GPA (iStudentSSN => iStudentSSN)
WHERE CURRENT OF Students_cur;
END LOOP;
END Calculate_GPA;
END GPA_Calculations;
Initializing a Package
It’s not unusual for a package to contain one or more variables
that must be initialized when the package is first loaded into memory.
Consider the package in Listing 6.9.
Listing 6.9 Initializing packaged variables.
PACKAGE BODY System_Errors
.
.
.
BEGIN
vLastError := 'No error condition exists';
END;
END System_Errors;
The highlighted code in this example is executed the first time the
package is loaded into memory. Thus, vLastError will always
contain the string ‘No error condition exists’ when the package
is first executed by a user. The initialization code for the package
must follow the declaration of all procedures and functions within the
package.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
