Oracle/PLSQL: WHERE CURRENT OF Statement

 

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

译：如果你想删除或者更新被

引用的记录，你可以使用

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

删除记录的例子：

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

     DELETE FROM courses_tbl

        WHERE CURRENT OF c1;

    COMMIT;

end if;

close c1;

RETURN cnumber;

END;

 

 

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！