Oracle/PLSQL: WHERE CURRENT OF Statement

Oracle/PLSQL: WHERE CURRENT OF Statement

--------------------------------------------------------------------------------

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

譯：如果你想删除或者更新被Select For Update引用的記錄，你可以使用Where Current Of語句。

The syntax for the Where Current Of statement is either:

UPDATE table_name

    SET set_clause

    WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name

WHERE CURRENT OF cursor_name;

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

譯：Where Current Of語句允許你更新或者是删除最後由cursor取的記錄。

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating 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

     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删除記錄的例子：

     DELETE FROM courses_tbl

文章出處：http://www.diybl.com/course/7_databases/oracle/2007114/84247.html