Previous Table of Contents Next


Listing 10.4 Implementing SELECT statement functionality by using a cursor.

DECLARE
   CURSOR StudentName_cur
   IS
   SELECT last_name
   FROM   STUDENTS
   WHERE  ssn = '999999999';

BEGIN
   OPEN StudentName_cur;
   FETCH StudentName_cur INTO StudentName_rec;
   CLOSE StudentName_cur;
END;

The same SELECT statement, when implemented with a cursor, requires one less fetch than a standalone SELECT statement within the PL/SQL block. However, there are a couple of “gotchas” lurking behind the use of a cursor in this way:

  Using a cursor like this always returns only the first row of a result set. If the query could potentially return more than one row, using a cursor like this may cause you to overlook data that you need to process or an error condition that you should be handling.
  Unless you’ve used a meaningful identifier to name your cursor, you’ll find that debugging your routine is difficult because you must keep jumping to the top of your code to look at your cursor declaration again.

Probably more important than the use of cursors is the wise use of exception handling. Careful use of exceptions can reduce the amount of conditional logic (IF-THEN statements, etc.) used in your code, thus reducing the number of instructions that the CPU must process.

Exception Handling

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.


Previous Table of Contents Next