The 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.

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>   Example:

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.

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