Home » SQL & PL/SQL » SQL & PL/SQL » where current of
where current of [message #184973] Sat, 29 July 2006 05:18 Go to next message
Messages: 4
Registered: July 2006
Location: Tamil Nadu
Junior Member
what is the use of where current of cursor when we can directly use a where clause to locate a particular row for updation or deletion?
Re: where current of [message #185002 is a reply to message #184973] Sat, 29 July 2006 16:20 Go to previous messageGo to next message
Messages: 21131
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use of the WHERE CURRENT OF clause is Oracle feature which enables a developer to update or delete records which were referenced by SELECT FOR UPDATE cursor. Documentation says

Refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error.

If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.

It is, of course, possible to reference records using an ordinary WHERE clause, but it is more convenient to use WHERE CURRENT OF cluase in situation where records is uniquely identified by more than one column. For example, it is easier and more understandable written

WHERE CURRENT OF cursor_name;

instead of

WHERE t.column1 = cur_r.column1
AND t.column2 = cur_r.column2
AND t.column3 = cur_r.column3;
Re: where current of [message #185203 is a reply to message #185002] Mon, 31 July 2006 08:42 Go to previous messageGo to next message
Messages: 4759
Registered: February 2005
Location: East Coast USA
Senior Member
Also, wouldn't it be one less hit on the database using WHERE CURRENT OF? I have to admit I don't recall ever checking to look under the hood to see what really happens.
Re: where current of [message #185205 is a reply to message #185203] Mon, 31 July 2006 08:54 Go to previous message
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
As I recall, when using the where current of, Oracle already has the rowid of the row currently being processed, in memory. Using a where clause to find a rowid, when we already have the info available, requires a whole lot of extra, unnecessary work.

Previous Topic: EXCEL UPLOAD
Next Topic: How to insert only the time value in the date column
Goto Forum:

Current Time: Fri Jul 28 12:27:01 CDT 2017

Total time taken to generate the page: 0.16970 seconds