WHERE CURRENT OF-优快云博客

本文介绍如何使用WhereCurrentOf语句在PL/SQL中更新或删除由游标获取的记录。通过两个示例函数展示更新和删除操作的具体实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

UPDATE table_name
    SET set_clause
    WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;

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

下面一个使用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;