Why do we need to use 'WHERE CURRENT OF' clause

panda_dilip

 Profile Answers by panda_dilip 

• May 4th, 2007

 

This is generally used where we use cursor in a PL/SQL block.

shamkohli

 Profile Answers by shamkohli Questions by shamkohli

• Jun 17th, 2008

 

While using cusor in PL/SQL, when we update a record, we can write
WHERE CURRENT OF     along with cursor_name.
This is done in place of the where condition while updating. If we give WHERE condition, the whole table have to be searched for the condition to be fulfilled for the updation to be done. But if we write 'WHERE CURRENT OF' the updation is done in the current record of the table without scanning the whole table.

saginandkishore

 Profile Answers by saginandkishore Questions by saginandkishore

• Jun 24th, 2008

 

Where current of is used for updating the data referenced by cursor which was created as "SELECT FOR UPDATE".

DECLARE
CURSROR MY_CURR IS
       SELECT COL1, COL2, COL3,...,COLN
       FROM TABLE_NAME
       WHERE COND1 AND COND2
       FOR UPDATE OF COLX;
......
BEGIN
....
UPDATE TABLE_NAME SET COLX = <NEW_VALUE> USING CURRENT OF MY_CURR;
.....
END;