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

---

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.