Current OF

WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENCT OF clause in an UPDATE or DELETE statement states that most recent row fetched from the table should be update or deleted. We must declare the cursor with the CURRENCT OF clause to use this feature.

Oracle provide the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of transactions.

The syntax of using the WHERE CURRENT OF clause is UPDATE OR DELETE statement.

Syntax:-

WHERE[CURRENT OF cursor_name | search_condition]

Example:-

DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL
FOR UPDATE OF COMMISSION_PCT;
BEGIN
FOR I IN C1
LOOP
UPDATE EMPLOYEES
SET COMMISSION_PCT= 0.19
WHERE CURRENT OF C1;
END LOOP;
END;

Advertisements