Using WHERE CURRENT OF Statement in Oracle

This Oracle tutorial explains how to use the Oracle/PLSQL WHERE CURRENT OF declaration with syntax and examples.

Description

If you sketch on updating or deleting information that have been referenced by using a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

Syntax

The syntax for the WHERE CURRENT OF assertion 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 announcement permits you to replace or delete the report that was remaining fetched through the cursor.

Example

Updating using the WHERE CURRENT OF Statement

Here is an instance the place we are updating data the usage of the WHERE CURRENT OF Statement:

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;

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;

   end if;

   CLOSE c1;

RETURN cnumber;

END;

Deleting using the WHERE CURRENT OF Statement

Here is an example the place we are deleting records using the WHERE CURRENT OF Statement:

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;

BEGIN

   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;

   else
      DELETE FROM courses_tbl
        WHERE CURRENT OF c1;

      COMMIT;

   end if;

   close c1;

RETURN cnumber;

END;