kishorebabu

• Sep 20th, 2005

 

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:

Delete the record I just fetched.

or:

Update these columns in that row I just fetched.

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;

Shefali

• Aug 16th, 2006

 

"WHERE CURRENT OF" clause is used for updating or deleting rows selected by the cursor with FOR UPDATE cluase.It explicitily locks the row for updating or deleting.

g_sidhu

 Profile 

• Jan 31st, 2008

 

When referencing the current row from an explicit cursor, use the WHERE CURRENT OF clause. This allows you to apply updates and deletes to the row currently being addressed, without the need to explicitly reference the ROWID. You must include the FOR UPDATE clause in the cursor query to lock the rows first.