WHERE CURRENT OF STATEMENT

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. Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

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:

WHERE [CURRENT OF cursor_name | search_condition]

The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.

EXAMPLE

UPDATING USING THE WHERE CURRENT OF STATEMENT.

DECLARE
CURSOR C1 IS SELECT empno,ename, sal
from emp
where comm IS NULL
FOR UPDATE OF comm;

var_comm NUMBER (10,2);
BEGIN
FOR rec in C1 LOOP 
  IF rec.sal < 500 THEN
      var_comm :=rec.sal*0.25;
    ELSIF rec.sal < 1500 THEN
      var_comm :=rec.sal*0.25;
    ELSIF rec.sal < 3000 THEN
      var_comm :=rec.sal*0.15;
    ELSE
   var_comm :=  rec.sal*0.12;
  END IF;
  
 UPDATE emp 
 SET comm =var_comm
 WHERE CURRENT OF C1;
 END LOOP;
 END;


SYNTAX

The syntax for the WHERE CURRENT OF statement in Oracle/PLSQL is either:

UPDATE table_name
  SET set_clause
  WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name
WHERE CURRENT OF cursor_name;


NOTE

The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

DELETING USING THE WHERE CURRENT OF STATEMENT

--DROP TABLE EMP;
--CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;

DECLARE
   v_empno   emp.empno%TYPE;
   v_ename   emp.ename%TYPE;
   v_sal     emp.sal%TYPE;

   CURSOR c1
   IS
          SELECT empno, ename, sal
            FROM emp
           WHERE comm IS NULL
          FOR UPDATE OF comm;

BEGIN
  OPEN C1;

   LOOP
      FETCH C1  INTO v_empno, v_ename, v_sal;

      EXIT WHEN c1%NOTFOUND;

      DELETE FROM emp
      WHERE CURRENT OF C1;

      --UPDATE emp
      --SET comm = 9999
         END LOOP;
    COMMIT;
   CLOSE C1;
END;