What is where current of clause in cursor? why to use it?
This clause use for update & delete most resent row with out help of ROWID and before update lock the particular row in the base table.FOR UPDATE clause is mandatory for this clause.
Take an example, Cursor is
Select * from EMP;
and in cursor we write
Update Emp set sal = sal + 100;
that means the fetching loops will execute as many times as rows returned by the cursor and the update will also be done for the same number of times.
Now suppose, If we put a where clause in the update statement, then the result would also not be predictable.
In the third case, we use the WHERE CURRENT OF clause in the where clause, this point out to the current row returned by the cursor. In that case, Oracle differentiate all the rows on the basis of RowID. But what happens if someone changes one row in the table.....that means the rowid would be changed. So we have to manually lock all the rows of that table by writing the FOR UPDATE/DELETE clause.
So In case of WHERE CURRENT OF clause, we should use the FOR UPDATE/DELETE clause also. This is known as the Pessimistic/Manual Locking and this the major difference between the Implicit Cursors and Explicit Cursors.
This clause use for update & delete most resent row with out help of ROWID and before update lock the particular row in the base table.FOR UPDATE clause is mandatory for this clause.
Take an example, Cursor is
Select * from EMP;
and in cursor we write
Update Emp set sal = sal + 100;
that means the fetching loops will execute as many times as rows returned by the cursor and the update will also be done for the same number of times.
Now suppose, If we put a where clause in the update statement, then the result would also not be predictable.
In the third case, we use the WHERE CURRENT OF clause in the where clause, this point out to the current row returned by the cursor. In that case, Oracle differentiate all the rows on the basis of RowID. But what happens if someone changes one row in the table.....that means the rowid would be changed. So we have to manually lock all the rows of that table by writing the FOR UPDATE/DELETE clause.
So In case of WHERE CURRENT OF clause, we should use the FOR UPDATE/DELETE clause also. This is known as the Pessimistic/Manual Locking and this the major difference between the Implicit Cursors and Explicit Cursors.
