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;