توضیحات
اگر بخواهیم رکوردی که با دستور select for update ، lock شده است را update یا delete کنیم باید از دستور where current of استفاده کنیم.
دستور
دستور where current of را اینگونه میتوان استفاده کرد:
1 2 3 4 5 6 | UPDATE table_name SET set_clause WHERE CURRENT OF cursor_name;DELETE FROM table_nameWHERE CURRENT OF cursor_name; |
مثال
مثالهایی از استفاده where current of:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 ) RETURN numberIS 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 numberIS 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; |
