WHERE CURRENT OF
果你想删除或者更新被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;
posted on 2014-05-24 14:59 Sunny_NUAA 阅读(672) 评论(0) 编辑 收藏 举报