328-330

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 < 00) 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 < 00) 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