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