 |
|
Exception Handling
Oracle Tips by
Burleson
|
It’s very common for developers to flag error conditions and handle
the condition through the use of IF-THEN logic. Listing
10.5 illustrates the use of this approach.
Listing 10.5 Using IF-THEN logic to flag
errors.
DECLARE
bAidAmountOk boolean;
vLastName varchar2 (20);
vFirstName varchar2 (20);
nGPA number (3,2);
nSemesterGPA number (3,2);
vSSN varchar2 (9);
nFinanceNum number (5);
nTotalAid number (7,2);
CURSOR Students_cur
IS
SELECT ssn, first_name, last_name, financing_num,
overall_gpa
FROM STUDENTS
WHERE overall_gpa < 2.5;
BEGIN
FOR Students_rec IN Students_cur LOOP
bAidAmountOk := FALSE;
vFirstName := Students_rec.first_name;
vLastName := Students_rec.last_name;
nGPA := Students_rec.overall_gpa;
nFinanceNum := Students_rec.financing_num;
vSSN := Students_rec.ssn;
SELECT total_aid
INTO nTotalAid
FROM STUDENT_FINANCIAL_AID
WHERE financing_num = nFinanceNum;
IF (nTotalAid < $1000) THEN
bAidAmountOk := TRUE;
END IF;
IF NOT bAidAmountOk THEN
nSemesterGPA := Get_Semester_GPA (vStudentSSN => vSSN);
IF (nSemesterGPA > 3.0) THEN
bAidAmountOk := TRUE;
END IF;
END IF;
END LOOP;
END;
This example uses the boolean variable bAidAmountOk to keep
track of a condition throughout the processing of each student record.
The highlighted statements deal directly with keeping track of this
condition throughout the loop.
This approach does have an impact on performance. Multiple
instructions are used to test for the error condition. Each
instruction requires CPU cycles to complete. A much better approach
involves the use of exceptions to avoid wasting CPU cycles, as shown
in Listing 10.6.
Listing 10.6 Using exception handlers to improve
performance.
DECLARE
vLastName varchar2 (20);
vFirstName varchar2 (20);
nGPA number (3,2);
nSemesterGPA number (3,2);
vSSN varchar2 (9);
nFinanceNum number (5);
nTotalAid number (7,2);
xAID_AMOUNT_OK EXCEPTION;
CURSOR Students_cur
IS
SELECT ssn, first_name, last_name, financing_num,
overall_gpa
FROM STUDENTS
WHERE overall_gpa < 2.5;
BEGIN
FOR Students_rec IN Students_cur LOOP
BEGIN
vFirstName := Students_rec.first_name;
vLastName := Students_rec.last_name;
nGPA := Students_rec.overall_gpa;
nFinanceNum := Students_rec.financing_num;
vSSN := Students_rec.ssn;
SELECT sum (total_aid)
INTO nTotalAid
FROM STUDENT_FINANCIAL_AID
WHERE financing_num = nFinanceNum;
IF (nTotalAid < $1000) THEN
RAISE xAID_AMOUNT_OK;
END IF;
nSemesterGPA := Get_Semester_GPA (vStudentSSN => vSSN);
IF (nSemesterGPA > 3.0) THEN
RAISE xAID_AMOUNT_OK;
END IF;
EXCEPTION
WHEN xAID_AMOUNT_OK THEN
NULL;
END;
END LOOP;
END;
In this example, the xAID_AMOUNT_OK exception is explicitly
raised inside the loop to allow execution to skip the instructions
that occur after the student’s GPA is checked. This probably results
in a gain of several CPU cycles being freed up. The highlighted
statements in the example are used for this exception handling.
PL/SQL’s exception handling is very performance efficient. When an
exception is raised, all subsequent instructions within the block are
bypassed so the exception can be handled by an exception handler. You
can utilize this built-in performance boost by thinking about the
organization of your code before you write it and planning to use
user-defined exceptions to skip code that you don’t need to execute.
Summary
Performance tuning is one of the most grueling aspects of any
Oracle developer’s job. Often, it will take hours to achieve
acceptable performance from a complex query. The tips presented in
this chapter will provide a starting point for this type of work,
which you will have to do at some point in your career.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
