Posted on 2010-05-10 22:59
Prayer 閱讀(1503)
評論(0) 編輯 收藏 引用 所屬分類:
DB2
如果你想刪除或者更新被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;