The WHERE CURRENT OF Clause

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

To update columns in the most recently fetched row, specify:

UPDATE table_name SET set_clause WHERE CURRENT OF cursor_name;

To delete the row from the database for the most recently fetched record, specify:

DELETE FROM table_name WHERE CURRENT OF cursor_name;

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

The most important advantage to using WHERE CURRENT OF to change the last row fetched 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 changed in a way that affected the construction of the primary key, you would have to update each SQL statement to support this change. If you use WHERE CURRENT OF, on the other hand, you modify only 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 significantly reduce the pain of maintaining your Oracle-based applications.

Let's see how this clause would improve the example in the previous section. In the jobs cursor FOR loop, 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:

· "Delete the row I just fetched."

· "Update these columns in that row I just fetched."

A perfect fit for WHERE CURRENT OF! The next version of my winterization program uses this clause. I have also switched from a FOR loop to a simple 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;