205-209

Previous

Table of Contents

Next

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.

Step-By-Step: Building A 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.

Requirements

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:

   The code that generates error messages must be able to insert specific pieces of data into the error message.

   The error messages that are generated will be stored in the SYSTEM_ERRORS table and will often be displayed to the system users. However, we also want to be able to specify whether or not the error is displayed to the user.

   Each message should have a severity level defined so that the proper type of message box can be displayed to the user.

   If the problem is an Oracle error, we want to store the data that was being processed at the time (or at least the parameter values of the procedure or function).

   Every piece of code can potentially have several error messages.

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