Previous | Table of Contents | Next |
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 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:
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 .
| |
---|---|
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 |