Oracle/PLSQL WHERE CURRENT OF Statement

「爱情、让人受尽委屈。」 2021-12-16 12:29 127阅读 0赞

**Oracle/PLSQL: WHERE CURRENT OF Statement**

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

译：如果你想删除或者更新被 [**Select For Update**][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;**

**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;**

**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;**

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

转载于:https://www.cnblogs.com/skiwdhwhssh/p/10342264.html

[Select For Update]: http://www.techonthenet.com/oracle/cursors/for_update.php
[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485