WHERE CURRENT OF & FOR UPDATE

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;