Explain the usage of WHERE CURRENT OF clause in cursors

to avoid dead lock situation we will use this.

WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor.

"WHERE CURRENT OF" has to be used in concurrence with "FOR UPDATE" in the cursor select stmt.

"WHERE CURRENT OF" used in delete or update stmts means, delete/update the current record specified by the cursor.

By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement.

Look at the following pl/sql code:

DECLARE
CURSOR wip_cur IS
SELECT  acct_no,
enter_date
FROM wip
WHERE enter_date < SYSDATE - 7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
INSERT INTO acct_log
(acct_no,
order_date)
VALUES (wip_rec.acct_no,
wip_rec.enter_date);

DELETE FROM wip WHERE CURRENT OF wip_cur;
END LOOP;
END;