 |
|
PL/SQL Procedure Body
Oracle Tips by
Burleson
|
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
