15.1.1 Use Parameters in a Cursor
15.1.2 Use a FOR UPDATE CursorThe cursor FOR UPDATE clause is only used with a cursor 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 perform 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. Be careful to consider issues covered in the transaction management topic in Chapter 4. The syntax is simply to add FOR UPDATE to the end of the cursor definition. If there are multiple items being selected, but you only want to lock one of them, then end the cursor definition with the following syntax: FOR UPDATE OF <item_name> FOR EXAMPLE -- ch15_2a.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 under $2500. It will increment them by 10.
FOR EXAMPLE -- ch15_3a.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;
FOR UPDATE OF can be used when creating a cursor for update that is based on multiple tables. FOR UPDATE OF locks the rows of a stable 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 -- ch15_4a.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;
15.1.3 Use the WHERE CURRENT OF ClauseUse WHERE CURRENT OF when you want to update the most recently fetched row. WHERE CURRENT OF 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 -- ch15_5a.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;
|