Oracle PL/SQL by Example, Fifth Edition (2015)

Chapter 12. Advanced Cursors


In this chapter, you will learn about

Image Parameterized Cursors

Image Complex Nested Cursors

Image FOR UPDATE and WHERE CURRENT Cursors


In Chapter 11, you mastered the basic concepts of cursors. In this chapter, you will learn how to dynamically alter the WHERE clause of a cursor by passing parameters when you call the cursor. In Chapter 21, you will take cursors to another level—that is, in the context of a package, you will learn to implement cursor variables.

Lab 12.1: Parameterized Cursors


After this lab, you will be able to

Image Use Parameters in a Cursor


Cursors with Parameters

A cursor can be declared with parameters. This approach enables a cursor to generate a specific result set that is simultaneously narrow and reusable. A cursor of all the data from the ZIPCODE table may be very useful, for instance, but it would be even more useful for certain data processing if it held information for only one state. At this point, you know how to create such a cursor. But wouldn’t it be more useful if you could create a cursor that could accept a parameter of a state and then run through only the city and ZIP code for that state?

For Example

CURSOR c_zip (p_state IN zipcode.state%TYPE) IS
  SELECT zip, city, state
    FROM zipcode
   WHERE state = p_state;

The main points to keep in mind for parameters in cursors are as follows:

Image Cursor parameters make the cursor more reusable.

Image Cursor parameters can be assigned default values.

Image The scope of the cursor parameters is local to the cursor.

Image The mode of the parameters can only be IN.

When a cursor has been declared as taking a parameter, it must be called with a value for that parameter. The c_zip cursor that was just declared is called as follows:

OPEN c_zip (parameter_value)

The same cursor could be opened with a cursor FOR loop as follows:

FOR r_zip IN c_zip('NY')
LOOP ...

The cursor from the previous example is expanded into a parameterized cursor in the next example. This example includes a DBMS_OUTPUT line that displays the ZIP code, city, and state. This is identical to the process we used earlier in cursor FOR loops, except that now when the cursor is opened, a parameter is passed.

For Example  ch12_1.sql

DECLARE
  CURSOR c_zip (p_state IN zipcode.state%TYPE) IS
    SELECT zip, city, state
      FROM zipcode
     WHERE state = p_state
BEGIN
  FOR r_zip IN c_zip('NJ')
  LOOP...
    DBMS_OUTPUT.PUT_LINE(r_zip.city||
       ' '||r_zip.zip');
  END LOOP;
END;

To complete the block, the cursor declaration is surrounded by DECLARE and BEGIN. The cursor is opened by passing the parameter “NJ,” and then, for each iteration of the cursor loop, the ZIP code and the city are displayed by using the built-in package DBMS_OUTPUT.

Lab 12.2: Complex Nested Cursors


After this lab, you will be able to

Image Use Complex Nested Cursors


Nesting cursors allows for looping through data at various stages. For example, one cursor might loop through ZIP codes. When it hits one ZIP code, a second cursor might be nested that loops through students who live in that ZIP code. Working through a specific example will help explain this approach in more detail.

The following PL/SQL code is complex. It involves all of the topics covered so far in this chapter. There is a nested cursor with three levels, meaning a grandparent cursor, a parent cursor, and a child cursor.

For Example  ch12_2.sql

SET SERVEROUTPUT ON
  1  DECLARE
  2    CURSOR c_student IS
  3      SELECT first_name, last_name, student_id
  4        FROM student
  5       WHERE last_name LIKE 'J%';
  6    CURSOR c_course
  7     (i_student_id IN student.student_id%TYPE)
  8    IS
  9      SELECT c.description, s.section_id sec_id
 10        FROM course c, section s, enrollment e
 11       WHERE e.student_id = i_student_id
 12         AND c.course_no = s.course_no
 13         AND s.section_id = e.section_id;
 14    CURSOR c_grade(i_section_id IN section.section_id%TYPE,
 15                   i_student_id IN student.student_id%TYPE)
 16       IS
 17       SELECT gt.description grd_desc,
 18          TO_CHAR
 19              (AVG(g.numeric_grade), '999.99') num_grd
 20         FROM enrollment e,
 21              grade g, grade_type gt
 22        WHERE e.section_id = i_section_id
 23          AND e.student_id = g.student_id
 24          AND e.student_id = i_student_id
 25          AND e.section_id = g.section_id
 26          AND g.grade_type_code = gt.grade_type_code
 27        GROUP BY gt.description ;
 28  BEGIN
 29    FOR r_student IN c_student
 30    LOOP
 31     DBMS_OUTPUT.PUT_LINE(CHR(10));
 32     DBMS_OUTPUT.PUT_LINE(r_student.first_name||
 33        '  '||r_student.last_name);
 34     FOR r_course IN c_course(r_student.student_id)
 35     LOOP
 36       DBMS_OUTPUT.PUT_LINE ('Grades for course :'||
 37          r_course.description);
 38       FOR r_grade IN c_grade(r_course.sec_id,
 39                         r_student.student_id)
 40       LOOP
 41         DBMS_OUTPUT.PUT_LINE(r_grade.num_grd||
 42           '  '||r_grade.grd_desc);
 43       END LOOP;
 44      END LOOP;
 45     END LOOP;
 46  END;

The grandparent cursor, c_student, is declared in lines 2–5. It takes no parameters and is a collection of students with a last name beginning with “J”. The parent cursor is declared in lines 6–13. This cursor, c_course, takes the parameter of the student_ID to generate a list of courses taken by that student. The child cursor, c_grade, is declared in lines 14–27. It takes two parameters, the section_id and the student_id. In this way it can generate an average of the different grade types for that student for that course.

The grandparent cursor loop begins on line 29, and only the student name is displayed with DBMS_OUTPUT. The parent cursor loop begins on line 35. It takes the parameter of the student_id from the grandparent cursor. Only the description of the course is displayed. The child cursor loop begins on line 40. It takes the parameter of the section_id from the parent cursor and the student_id from the grandparent cursor. The grades are then displayed. The grandparent cursor loop ends on line 45, the parent cursor on line 44, and, finally, the child cursor on line 43.

The output of this script is a student name, followed by the courses that student is taking and the average grade he or she has earned for each grade type.

Lab 12.3: FOR UPDATE and WHERE CURRENT Cursors


After this lab, you will be able to

Image Use a FOR UPDATE Cursor

Image Use WHERE CURRENT in a Cursor


FOR UPDATE Cursor

The cursor FOR UPDATE clause is used with a cursor only when you want to update tables in the database. Generally, when you execute a SELECT statement, you are not locking any rows. The purpose of using the FOR UPDATE clause is to lock the rows of the tables that you want to update, so that another user cannot perform an update until you complete your update and release the lock. The next COMMIT or ROLLBACK statement releases the lock.

The FOR UPDATE clause will change the manner in which the cursor operates in only a few respects. When you open a cursor, all rows that meet the restriction criteria are identified as part of the active set. Using the FOR UPDATE clause will lock these rows that have been identified in the active set. If the FOR UPDATE clause is used, then rows may not be fetched from the cursor until a COMMIT has been issued. It is important for you to consider where to place the COMMIT statement. Be careful to consider the issues discussed in relation to transaction management inChapter 3.

The syntax is simply to add FOR UPDATE to the end of the cursor definition. If multiple items are being selected but you want to lock only one of them, then end the cursor definition with the following syntax:

FOR UPDATE OF <item_name>

For Example  ch12_3.sql

DECLARE
  CURSOR c_course IS
    SELECT course_no, cost
      FROM course FOR UPDATE;
BEGIN
  FOR r_course IN c_course
  LOOP
    IF r_course.cost < 2500
    THEN
      UPDATE course
         SET cost = r_course.cost + 10
       WHERE course_no = r_course.course_no;
    END IF;
  END LOOP;
END;

This example shows how to update the cost of all courses with a cost of less than 2500. It will increment each of these costs by 10.

Several issues must be taken into account with FOR UPDATE cursors in terms of where to place a COMMIT statement. The following example demonstrates one way of handling this issue.

For Example  ch12_4.sql

DECLARE
  CURSOR c_grade(
    i_student_id IN enrollment.student_id%TYPE,
    i_section_id IN enrollment.section_id%TYPE)
  IS
    SELECT final_grade
      FROM enrollment
     WHERE student_id = i_student_id
       AND section_id = i_section_id
     FOR UPDATE;
  CURSOR c_enrollment IS
    SELECT e.student_id, e.section_id
      FROM enrollment e, section s
     WHERE s.course_no = 135
       AND e.section_id = s.section_id;
BEGIN
  FOR r_enroll IN c_enrollment
  LOOP
    FOR r_grade IN c_grade(r_enroll.student_id,
                           r_enroll.section_id)
    LOOP
      UPDATE enrollment
         SET final_grade  = 90
       WHERE student_id = r_enroll.student_id
         AND section_id = r_enroll.section_id;
    END LOOP;
  END LOOP;
END;

Placing a COMMIT statement after each update can be costly. If there are a lot of updates and the COMMIT comes after the block loop, however, there is a risk of a rollback segment not being large enough. Normally, the COMMIT statement would go after the loop, except when the transaction count is high; in such a situation, you might want to code something that does a COMMIT for each 10,000 records. If this script were part of a large procedure, you might want to put a SAVEPOINT after the loop. Then, if you need to roll back this update at a later point, it would be an easy task.

If this example were run, the final_grade for all students enrolled in course 135 would be updated to 90. There are two cursors here. One cursor captures the students who are enrolled in course 135 and places them into the active set. The other cursor takes the student_id and thesection_id from this active set, selects the corresponding final_grade from the enrollment table, and locks the entire enrollment table. The enrollment cursor loop is begun first, and it passes the student_id and the section_id as IN parameters to the second cursor loop of thec_grade cursor, which performs the update. A COMMIT statement should be added immediately after the update to ensure that each update is committed to the database.

FOR UPDATE OF in a Cursor

FOR UPDATE OF can be used when creating a cursor for an update operation that is based on multiple tables. FOR UPDATE OF locks the rows of a table that both contain one of the specified columns and are members of the active set. In other words, it is the means of specifying which table you want to lock. If the FOR UPDATE OF clause is used, then rows may not be fetched from the cursor until a COMMIT has been issued.

For Example  ch12_5.sql

DECLARE
  CURSOR c_stud_zip IS
    SELECT s.student_id, z.city
      FROM student s, zipcode z
     WHERE z.city = 'Brooklyn'
       AND s.zip = z.zip
     FOR UPDATE OF phone;
BEGIN
  FOR r_stud_zip IN c_stud_zip
  LOOP
    UPDATE student
       SET phone = '718'||SUBSTR(phone,4)
     WHERE student_id = r_stud_zip.student_id;
  END LOOP;
END;

This example updates the phone numbers of students living in Brooklyn by changing the area code to 718. The cursor declaration locks the phone column of the student table. This lock is never released, however, because there is no COMMIT or ROLLBACK statement.

WHERE CURRENT OF in a Cursor

Use WHERE CURRENT OF when you want to update the most recently fetched row. This clause can only be used with a FOR UPDATE OF cursor. The advantage of the WHERE CURRENT OF clause is that it enables you to eliminate the WHERE clause in the UPDATE statement.

For Example  ch12_6.sql

DECLARE
  CURSOR c_stud_zip IS
    SELECT s.student_id, z.city
      FROM student s, zipcode z
     WHERE z.city = 'Brooklyn'
       AND s.zip = z.zip
     FOR UPDATE OF phone;
BEGIN
  FOR r_stud_zip IN c_stud_zip
  LOOP
    DBMS_OUTPUT.PUT_LINE(r_stud_zip.student_id);
    UPDATE student
       SET phone = '718'||SUBSTR(phone,4)
     WHERE CURRENT OF c_stud_zip;
  END LOOP;
END;

These last two examples perform the same update. The WHERE CURRENT OF clause allows you to eliminate a match in the UPDATE statement, because the update is being performed for the current record of the cursor only.


Did You Know?

The FOR UPDATE and WHERE CURRENT OF syntax can be used with cursors that are performing a delete as well as an update.


Summary

The chapter explored various advanced topics involving cursors. First, you learned how to pass parameters to cursors to restrict the result set of a cursor. Then, you learned how to nest cursors. Finally, you saw the syntax for creating cursors that make database updates.


By the Way

The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.