WHERE CURRENT OF & FOR UPDATE in Oracle

 

WHERE CURRENT OF & FOR UPDATE

 

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

 

UPDATE table_name

  SET set_clause

  WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name

WHERE CURRENT OF cursor_name;

 

CREATE OR REPLACE Function FindCourse

   ( name_in IN varchar2 )

   RETURN number

IS

   cnumber number;

   CURSOR c1

   IS

     SELECT course_number

     FROM courses_tbl

     WHERE course_name = name_in

     FOR UPDATE of instructor;  -- instructor – column name in the table

/* Once we open a cursor having a FOR UPDATE clause, all the rows returned by the SELECT statement are locked for our changes until a commit or a rollback is placed to release the lock. */

BEGIN

   OPEN c1;

   FETCH c1 INTO cnumber;

   if c1%notfound then

      cnumber := 9999;

   else

      UPDATE courses_tbl

        SET instructor = 'SMITH'

        WHERE CURRENT OF c1;

      COMMIT;   -- Lock gets released

   end if;

   CLOSE c1;

RETURN cnumber;

END;

 

Once we open a cursor having a FOR UPDATE clause, all the rows returned by the SELECT statement are locked for our changes until a commit or a rollback is placed to release the lock.

When we associate a SELECT statement with more than one table joined together to a cursor with a FOR UPDATE clause, we end up locking all the tables in the FROM clause of the SELECT statement.

DECLARE

  CURSOR cur

  IS

    SELECT       *      FROM

      employees e,

      departments d

    WHERE

      e.department_id=d.department_id FOR UPDATE;  -- It’ll lock both the tables

 

BEGIN

  OPEN cur;

END;

/

 

However,

DECLARE

  CURSOR cur

  IS

    SELECT

      *

    FROM

      employees e,

      departments d

    WHERE

      e.department_id=d.department_id FOR UPDATE OF e.employee_id;

 -- It’ll lock employees table ONLY.

BEGIN

  OPEN cur;

END;

/

 

The WHERE CURRENT OF clause internally operates on the ROWID pseudo column of the rows returned by the cursor.

Thus we cannot use WHERE CURRENT OF clause on the cursor associated SELECT statement having more than one table joined.

The block will fail with an ORA-01410: invalid ROWID error as there is no way to specify the rowid as there are two tables.

DECLARE

  l_n_sal employees.salary%type;

  CURSOR cur

  IS

    SELECT

      e.salary

    FROM

      employees e,

      departments d

    WHERE

      e.department_id    =d.department_id

    AND d.department_name='IT'   FOR UPDATE OF e.employee_id;

BEGIN

  OPEN cur;

  LOOP

    FETCH cur INTO l_n_sal;

    UPDATE

      employees

    SET

      salary=l_n_sal*1.10

    WHERE CURRENT OF cur;

    EXIT

  WHEN cur%notfound;

  END LOOP;

  Commit;

END;

/

Error report –

 

ORA-01410: invalid ROWID

 

This scenario can be manhandled by fetching the rowid of the intended table in the cursor associated SELECT statement and using it in the WHERE clause of the DELETE or the UPDATE statement instead of the WHERE CURRENT OF clause

DECLARE

  l_n_sal employees.salary%type;

  l_r_rowid rowid;

  CURSOR cur

  IS

    SELECT

      e.rowid,e.salary

    FROM

      employees e,

      departments d

    WHERE

      e.department_id    =d.department_id

    AND d.department_name='IT'   FOR UPDATE OF  e.employee_id;

BEGIN

  OPEN cur;

  LOOP

    FETCH

      cur

    INTO

      l_r_rowid,

      l_n_sal;

    UPDATE

      employees

    SET

      salary=l_n_sal*1.10

    WHERE

      rowid=l_r_rowid;

    EXIT

  WHEN cur%notfound;

  END LOOP;

END;

 

Mutating table

 

Mutating table trigger errors with the compound trigger

 

A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). 

 

CREATE OR REPLACE TRIGGER equitable_salary_trg 

   AFTER INSERT OR UPDATE 

   ON employees 

   FOR EACH ROW 

DECLARE 

   l_max_allowed   employees.salary%TYPE; 

BEGIN 

   SELECT MIN (salary) * 25 

     INTO l_max_allowed 

     FROM employees; 

   IF l_max_allowed < :NEW.salary 

   THEN 

      UPDATE employees 

         SET salary = l_max_allowed 

       WHERE employee_id = :NEW.employee_id; 

   END IF; 

END equitable_salary_trg;

 

BEGIN

   UPDATE employees

      SET salary = 100000

    WHERE last_name = 'King';

END;

and I see this error:

ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it

ORA-06512: at "EQUITABLE_SALARY_TRG", line 4

 

The solution:

 

1.     Define an AFTER STATEMENT trigger that goes through the to-do list, and executes the desired logic for each row.

2.     The Compound DML Trigger

 

CREATE OR REPLACE TRIGGER equitable_salary_trg    

FOR UPDATE OR INSERT ON employees    

COMPOUND TRIGGER     

   TYPE id_salary_rt IS RECORD (    

      employee_id   employees.employee_id%TYPE    

    , salary        employees.salary%TYPE    

   );    

   TYPE row_level_info_t IS TABLE OF id_salary_rt  INDEX BY PLS_INTEGER;    

   g_row_level_info   row_level_info_t;    

   AFTER EACH ROW IS    

   BEGIN  

      g_row_level_info (g_row_level_info.COUNT + 1).employee_id :=    

           :NEW.employee_id;    

      g_row_level_info (g_row_level_info.COUNT).salary := :NEW.salary;

   END AFTER EACH ROW;    

   AFTER STATEMENT IS    

      l_max_allowed   employees.salary%TYPE;    

   BEGIN      

      SELECT MIN (salary) * 25    

        INTO l_max_allowed    

        FROM employees;     

      FOR indx IN 1 .. g_row_level_info.COUNT    

      LOOP                                      

         IF l_max_allowed < g_row_level_info (indx).salary    

         THEN    

            UPDATE employees    

               SET salary = l_max_allowed    

             WHERE employee_id = g_row_level_info (indx).employee_id;    

         END IF;    

      END LOOP;    

   END AFTER STATEMENT;    

END equitable_salary_trg; 

 

In this trigger:

• First, declare an array of Employee record that includes employee id and salary.
• Second, collect affected rows into the array in the row-level trigger.
• Third, update each affected row in the statement-level trigger.