Using a SELECT FOR UPDATE Cursor

Once you've defined a SELECT FOR UPDATE cursor, you use the WHERE CURRENT OF clause to process the rows returned by it. You can use this clause in an UPDATE or DELETE statement. It has the following syntax:

WHERE CURRENT of cursor_name;

where cursor_name is the name of the cursor defined with a FOR UPDATE clause.

The following is a complete example of using SELECT FOR UPDATE cursors. I use the sec_hrc_tab table to demonstrate this. First, this table is populated using an INSERT statement as follows:

BEGIN
     INSERT INTO sec_hrc_tab
         SELECT * FROM hrc_tab;
     COMMIT;
END;
/

The output can be verified as follows:

SQL> select * from sec_hrc_tab;

  HRC_CODE HRC_DESCR
----- -------------
           1 CEO/COO
           2 VP
           3 Director
           4 Manager
           5 Analyst

Then I define a SELECT FOR UPDATE cursor and use the WHERE CURRENT OF clause to update the rows retrieved by this cursor in a particular fashion. Here's the program for this:

DECLARE
       CURSOR csr_1 IS
             SELECT * FROM sec_hrc_tab FOR UPDATE OF hrc_descr;
             v_hrc_descr VARCHAR2(20);
BEGIN
     FOR idx IN csr_1 LOOP
             v_hrc_descr := UPPER(idx.hrc_descr);
             UPDATE sec_hrc_tab
              SET         hrc_descr = v_hrc_descr
              WHERE CURRENT OF csr_1;
     END LOOP;
     COMMIT;
END;
/

This program updates the hrc_descr column of each row retrieved by csr_1 with its value converted to uppercase. The output can be verified as follows:

 SQL> select * from sec_hrc_tab;

   HRC_CODE HRC_DESCR
   -------- ----------
          1 CEO/COO
          2 VP
          3 DIRECTOR
          4 MANAGER
          5 ANALYST

The mechanism of SELECT FOR UPDATE cursors works as follows:

1. The SELECT FOR UPDATE cursor puts a lock on the rows retrieved by the cursor. If it's unable to obtain a lock because some other session has placed a lock on the specific rows, it waits until it can get a lock. A COMMIT or ROLLBACK in the corresponding session frees the locks held by other sessions.

2. For each row identified by the cursor, the cursor updates the specified column of that row. That is, it keeps track of the current row and updates it, and then fetches the subsequent row and updates it. It does this without scanning the same table again. This is unlike an ordinary UPDATE or DELETE statement inside the loop, where the cursor scans the updated table again to determine the current row to be modified.

Although you could achieve the same function by using a simple UPDATE statement, this example is meant to illustrate the use of SELECT FOR UPDATE cursors.

To use WHERE CURRENT OF, you have to declare the cursor using FOR UPDATE. The reverse is not true. That is, you can use a SELECT FOR UPDATE cursor to modify the rows without using the WHERE CURRENT OF clause. Then, you have to update or delete the cursor rows using the primary key.

A SELECT FOR UPDATE cursor offers two important advantages: Namely, it locks the rows after opening the cursor and the resultset rows are identified for update, and it eliminates a second fetch of the rows for doing the update and preserves the current row by the WHERE CURRENT OF clause.

You have to do a COMMIT outside of the cursor loop when you use WHERE CURRENT OF in processing the rows of a SELECT FOR UPDATE cursor. This is because a COMMIT releases the lock on the rows that the SELECT FOR UPDATE has put a lock on, and this causes a subsequent fetch to fail.