Oracle/PLSQL: WHERE CURRENT OF Statement

 

Oracle/PLSQL: WHERE CURRENT OF Statement

---

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.

译：如果你想删除或者更新被 Select For Update 引用的记录，你可以使用 Where Current Of 语句。

The syntax for the Where Current Of statement is either:

UPDATE table_name
    SET set_clause
    WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

 

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

译： Where Current Of 语句允许你更新或者是删除最后由 cursor 取的记录。

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the Where Current Of Statement:

译：下面一个使用 Where Current Of 更新记录的例子：

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number, instructor
        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:

译：下面一个使用 Where Current Of 删除记录的例子：

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number, instructor
        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;