WHERE CURRENT OF

来源：互联网发布：python signal 编辑：程序博客网时间：2024/05/16 06:09

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

UPDATE table_name    SET set_clause    WHERE CURRENT OF cursor_name;ORDELETE FROM table_nameWHERE CURRENT OF cursor_name;

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

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

CREATE OR REPLACE Function FindCourse   ( name_in IN varchar2 )   RETURN numberIS    cnumber number;    CURSOR c1    IS       SELECT course_number, instructor        from courses_tbl        where course_name = name_in        FOR UPDATE of instructor;BEGINopen 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 numberIS    cnumber number;    CURSOR c1    IS       SELECT course_number, instructor        from courses_tbl        where course_name = name_in        FOR UPDATE of instructor;BEGINopen 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;