6.11.1 Releasing Locks with COMMIT

As soon as a cursor with a FOR UPDATE clause is OPENed, all rows identified in the result set of the cursor are locked and remain locked until your session issues either a COMMIT statement to save any changes or a ROLLBACK statement to cancel those changes. When either of these actions occurs, the locks on the rows are released. As a result, you cannot execute another FETCH against a FOR UPDATE cursor after you COMMIT or ROLLBACK. You will have lost your position in the cursor.

Consider the following program, which assigns winterization chores:[1]

[1] Caveat: I don't want to set false expectations with anyone, especially my wife. The code in this block is purely an example. In reality, I set the max_procrastination_allowed to five years and let my house decay until I can afford to pay someone to do something, or my wife does it, or she gives me an ultimatum. Now you know why I decided to write a book...

DECLARE
   /* All the jobs in the Fall to prepare for the Winter */
   CURSOR fall_jobs_cur
   IS
      SELECT task, expected_hours, tools_required, do_it_yourself_flag
        FROM winterize
       WHERE year = TO_CHAR (SYSDATE, 'YYYY')
         AND completed_flag = 'NOTYET'
       FOR UPDATE OF task;
BEGIN
   /* For each job fetched by the cursor... */
   FOR job_rec IN fall_jobs_cur
   LOOP
      IF job_rec.do_it_yourself_flag = 'YOUCANDOIT'
      THEN
         /*
         || I have found my next job. Assign it to myself (like someone
         || is going to do it!) and then commit the changes.
         */
         UPDATE winterize SET responsible = 'STEVEN'
          WHERE task = job_rec.task
            AND year = TO_CHAR (SYSDATE, 'YYYY');
         COMMIT;
      END IF;
   END LOOP;
END;

Suppose this loop finds its first YOUCANDOIT job. It then commits an assignment of a job to STEVEN. When it tries to FETCH the next record, the program raises the following exception:

ORA-01002: fetch out of sequence

If you ever need to execute a COMMIT or ROLLBACK as you FETCH records from a SELECT FOR UPDATE cursor, you should include code (such as a loop EXIT or other conditional logic) to halt any further fetches from the cursor.

6.11.2 The WHERE CURRENT OF Clause

PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor in order to allow you to easily make changes to the most recently fetched row of data.

The general format for the WHERE CURRENT OF clause is as follows:

UPDATE table_name
   SET set_clause
 WHERE CURRENT OF cursor_name;

DELETE
  FROM table_name
 WHERE CURRENT OF cursor_name;

Notice that the WHERE CURRENT OF clause references the cursor and not the record into which the next fetched row is deposited.

The most important advantage to using WHERE CURRENT OF where you need to change the row fetched last is that you do not have to code in two (or more) places the criteria used to uniquely identify a row in a table. Without WHERE CURRENT OF, you would need to repeat the WHERE clause of your cursor in the WHERE clause of the associated UPDATEs and DELETEs. As a result, if the table structure changes in a way that affects the construction of the primary key, you have to make sure that each SQL statement is upgraded to support this change. If you use WHERE CURRENT OF, on the other hand, you only have to modify the WHERE clause of the SELECT statement.

This might seem like a relatively minor issue, but it is one of many areas in your code where you can leverage subtle features in PL/SQL to minimize code redundancies. Utilization of WHERE CURRENT OF, %TYPE, and %ROWTYPE declaration attributes, cursor FOR loops, local modularization, and other PL/SQL language constructs can have a big impact on reducing the pain you may experience when you maintain your Oracle-based applications.

Let's see how this clause would improve the previous example. In the jobs cursor FOR loop above, I want to UPDATE the record that was currently FETCHed by the cursor. I do this in the UPDATE statement by repeating the same WHERE used in the cursor because (task, year) makes up the primary key of this table:

WHERE task = job_rec.task
  AND year = TO_CHAR (SYSDATE, 'YYYY');

This is a less than ideal situation, as explained above: I have coded the same logic in two places, and this code must be kept synchronized. It would be so much more convenient and natural to be able to code the equivalent of the following statements:

or:

A perfect fit for WHERE CURRENT OF! The next version of my winterization program below uses this clause. I have also switched to a simple loop from FOR loop because I want to exit conditionally from the loop:

DECLARE
   CURSOR fall_jobs_cur IS SELECT ... same as before ... ;
   job_rec fall_jobs_cur%ROWTYPE;
BEGIN
   OPEN fall_jobs_cur;
   LOOP
      FETCH fall_jobs_cur INTO job_rec;

      IF fall_jobs_cur%NOTFOUND
      THEN
         EXIT;

      ELSIF job_rec.do_it_yourself_flag = 'YOUCANDOIT'
      THEN
         UPDATE winterize SET responsible = 'STEVEN'
          WHERE CURRENT OF fall_jobs_cur;
         COMMIT;
         EXIT;
      END IF;
   END LOOP;
   CLOSE fall_jobs_cur;
END;