Oracle Where Current Of語句

這篇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;