Previous | Table of Contents | Next |
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;
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;
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.
The primary purpose of a package is to group related procedures and functions into a single object. With this in mind, let s build a package based on a common need for every system ”error handling and message generation.
Our package needs to provide other stored PL/SQL objects with a way to generate an error message that contains excerpts from the data being processed . While we could certainly format the error messages individually as problems arise, it would be easier on developers if common code handled this process.
Error messages that are generated will be stored in the SYSTEM_ERRORS table, as follows :
error_number NOT NULL number error_time NOT NULL date error_text varchar2 (200) displayed char (1)
Our package must satisfy the following conditions:
Based on these conditions, it seems reasonable that we ll want to store the error messages for each procedure and function in an ERROR_MESSAGES table that looks something like this:
module_name NOT NULL varchar2 (30) error_number NOT NULL number error_part NOT NULL number error_text NOT NULL varchar2 (30)
This table makes it possible to store a single error message in several different pieces. Doing so will allow the code to simply pull the pieces of the message from the table and stick a piece of data between each portion of the message.
Severity information for messages will be stored in the ERROR_SEVERITIES table, as follows:
module_name NOT NULL varchar2 (30) error_number NOT NULL number severity_level NOT NULL number
At this point, we can be certain that we ll need at least one procedure to be called to build a message. Let s call this procedure Build_Error() .
Previous | Table of Contents | Next |