這篇Oracle教學解釋了如何使用Oracle / PLSQL WHERE CURRENT OF語句的語法和示例。
如果想要更新或刪除SELECT FOR UPDATE語句引用的記錄，則可以使用WHERE CURRENT OF語句。
語法
Oracle / PLSQL中WHERE CURRENT OF語句的語法是：
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
或者 -
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
注意：
WHERE CURRENT OF語句允許您更新或刪除上次由游標獲取的記錄。
示例
使用WHERE CURRENT OF語句更新，以下是使用WHERE CURRENT OF語句更新記錄的示例：
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
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;
使用WHERE CURRENT OF語句刪除
以下是使用WHERE CURRENT OF語句刪除記錄的示例：
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
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;