WHERE CURRENT OF Statement

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

Description

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

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.

Example

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating 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

      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 where 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;