Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE
The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.
The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted.We must declare the cursor with the FOR UPDATE clause to use this feature.
When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.
Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.
The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows:
The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.
DECLARECURSOR c_1 IS SELECT empno, ename, salary
FROM emp
WHERE comm IS NULL
FOR UPDATE OF comm;
var_comm NUMBER(10,2);
BEGIN
FOR r_1 IN c_1 LOOP
IF r_1.salary < 5000 THEN
var_comm := r_1.salary * 0.25;
ELSIF r_1.salary < 10000 THEN
var_comm := r_1.salary * 0.20;
ELSIF r_1.salary < 30000 THEN
var_comm := r_1.salary * 0.15;
ELSE
var_comm := r_1.salary * 0.12;
END IF;
UPDATE emp
SET comm = var_comm
WHERE CURRENT OF c_1;
END LOOP;
END;
/
The FOR UPDATE clause in the SELECT statement can only be specified in the top level; subqueries cannot have this clause.
