WHERE CURRENT OF

最新推荐文章于 2023-04-09 20:49:05 发布

转载最新推荐文章于 2023-04-09 20:49:05 发布 · 1w 阅读

文章标签：

#function #table #c #delete

本文介绍如何使用WhereCurrentOf语句来更新或删除由游标最近获取的记录。通过具体的函数示例展示了更新及删除操作的具体实现方式。

如果你想删除或者更新被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;