Previous Table of Contents Next


The Procedure Body

The SQL and PL/SQL statements that follow the BEGIN statement and precede the EXCEPTION and/or END statements is the body of the procedure. The highlighted portion of Listing 4.19 is the procedure’s body.

Listing 4.19 The body of a procedure.

PROCEDURE Calculate_GPA (iStudentSSN IN    number)

IS

  CURSOR StudentClasses_cur
  IS
  SELECT SC.course_number, C.course_hours
  FROM   SCHEDULED_CLASSES SC,
         CLASSES           C
  WHERE  ssn = iStudentSSN
  AND    credit_flag = 'Y'
  AND    audit_flag  = 'N';

  iTotalCredits    integer := 0;
  iTotalHours      integer := 0;

  FUNCTION Get_Course_Credits (iCourseID IN     integer)
  RETURN integer

  IS

     iCreditsForClass integer := 0;

  BEGIN
    SELECT decode (course_grade, 'A', 4,
                                 'B', 3,
                                 'C', 2,
                                 'D', 1, 0)
    INTO   iCreditsForClass
    FROM   SCHEDULED_CLASSES
    WHERE  course_number = iCourseID
    AND    ssn           = iStudentSSN;

    RETURN iCreditsForClass;
  END Get_Course_Credits;

BEGIN
  FOR StudentClasses_rec IN StudentClasses_cur LOOP
    iCourse     := StudentClasses_rec.course_number;
    iTotalHours := StudentClasses_rec.course_hours;

    iTotalCredits :=   iTotalCredits
                  + Get_Course_Credits (iCourse);
    END LOOP;

    UPDATE STUDENTS
    SET    overall_gpa = (iTotalCredits / iTotalHours)
    WHERE  ssn = iStudentSSN;

EXCEPTION
  WHEN ZERO_DIVIDE THEN
      System.Log_Error (vObjectName =>'Calculate_GPA',
                        vErrorText  => SQLERRM,
                        vParameters => to_char (iCourseID) ||
                                       '^' ||
                                       to_char (iStudentSSN));
END Calculate_GPA;

Exception Handlers

Exception handlers are defined within the procedure to handle error conditions that could reasonably be expected to occur while the procedure is executing. In Listing 4.20, the developer feels that the ZERO_DIVIDE exception (one of the standard PL/SQL exceptions) could reasonably be expected to occur while calculating the student’s new GPA.

Listing 4.20 The exception handler of a procedure.

PROCEDURE Calculate_GPA (iStudentSSN IN    number)

IS

  CURSOR StudentClasses_cur
  IS
  SELECT SC.course_number, C.course_hours
  FROM   SCHEDULED_CLASSES SC,
         CLASSES           C
  WHERE  ssn = iStudentSSN
  AND    credit_flag = 'Y'
  AND    audit_flag  = 'N';

  iTotalCredits    integer := 0;
  iTotalHours      integer := 0;

  FUNCTION Get_Course_Credits (iCourseID IN     integer)
  RETURN integer

  IS

    iCreditsForClass integer := 0;

  BEGIN
    SELECT decode (course_grade, 'A', 4,
                                 'B', 3,
                                 'C', 2,
                                 'D', 1, 0)
    INTO   iCreditsForClass
    FROM   SCHEDULED_CLASSES
    WHERE  course_number = iCourseID
    AND    ssn           = iStudentSSN;
    RETURN iCreditsForClass;
  END Get_Course_Credits;

BEGIN
  FOR StudentClasses_rec IN StudentClasses_cur LOOP
    iCourse     := StudentClasses_rec.course_number;
    iTotalHours := StudentClasses_rec.course_hours;

    iTotalCredits :=   iTotalCredits
                  + Get_Course_Credits (iCourse);
  END LOOP;

  UPDATE STUDENTS
  SET    overall_gpa = (iTotalCredits / iTotalHours)
  WHERE  ssn = iStudentSSN;

EXCEPTION
  WHEN ZERO_DIVIDE THEN
      System.Log_Error (vObjectName =>'Calculate_GPA',
                        vErrorText  => SQLERRM,
                        vParameters => to_char (iCourseID) ||
                                       '^' ||
                                       to_char (iStudentSSN));
END Calculate_GPA;

Exceptions occur for one of three reasons:

  Oracle detects an unexpected error while the object is executing.
  An exception is explicitly raised using the RAISE statement.
  An exception is raised using the Raise_Application_Error() procedure.

Unexpected Errors

If an error occurs during the execution of an object, Oracle raises an exception and generates the most appropriate error text.

If the error message corresponds to one of the predefined exceptions that PL/SQL uses, the error can be handled using an exception handler for the predefined exception. Table 4.2 lists these predefined exceptions and their associated Oracle error message numbers.

Table 4.2 Predefined exceptions in PL/SQL.

Exception Associated Oracle Error
CURSOR_ALREADY_OPEN ORA-06511
DUP_VAL_ON_INDEX ORA-00001
INVALID_CURSOR ORA-01001
INVALID_NUMBER ORA-01722
LOGIN_DENIED ORA-01017
NO_DATA_FOUND ORA-01403
NOT_LOGGED_ON ORA-01012
PROGRAM_ERROR ORA-06501
STORAGE_ERROR ORA-06500
TIMEOUT_ON_RESOURCE ORA-00051
TOO_MANY_ROWS ORA-01422
TRANSACTION_BACKED_OUT ORA-00061
VALUE_ERROR ORA-06502
ZERO_DIVIDE ORA-01476

You can redeclare these predefined exceptions and create custom handlers for your new exceptions, but Oracle will not recognize your new exception when it attempts to raise one of the predefined exceptions. Your best bet is to leave the predefined exceptions alone and create your own user-defined exceptions.


Previous Table of Contents Next