12. Advanced 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
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:
Cursor parameters make the cursor more reusable.
Cursor parameters can be assigned default values.
The scope of the cursor parameters is local to the cursor.
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.
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
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
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 in Chapter 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 the section_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 the c_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.
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.