Where Current Of

توضیحات

اگر بخواهیم رکوردی که با دستور select for update ، lock شده است را update یا delete کنیم باید از دستور where current of استفاده کنیم.

دستور

دستور 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:

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;


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;