Oracle使用游标更新数据 Oracle游标之select for update和where current of 语句

た入场券 2022-04-02 07:44 387阅读 0赞

# Oracle使用游标更新数据 #

2016年11月20日 13:15:49 [hzwy23][] 阅读数：5313

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

> 友情推广  
> ![在这里插入图片描述][2018110522483248.png]

\#\#\#使用游标修改数据

\#\#\#\#定义一个游标，游标名称为 mycursor

\#\#\#\#\#更新scott用户中emp表中empno为7369的销售额

-- Created on 2015/11/30 by ZHANW 
    declare 
      he emp%rowtype;
      cursor mycursor(pid integer) is select * from emp where empno = pid for update;
    begin
      open mycursor(7369);
      while(true) loop
         fetch mycursor into he; 
         exit when mycursor%notfound;
         update emp set sal = 1111 where current of mycursor;
      end loop;
    end;

-- Created on 2015/11/30 by ZHANW 
    declare 
      he emp%rowtype;
      cursor mycursor(pid integer) is select * from emp where empno = pid for update;
    begin
      open mycursor(7369);
      while(true) loop
         fetch mycursor into he; 
         exit when mycursor%notfound;
         delete from emp where current of mycursor;
      end loop;
    end;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12

\#\#\#注意：

> delete语句一定要写在exit后面，不然可能会报错。

\#\#\#\#优化：

> 在定义游标时，可以在for update 后面添加 of 字段或者nowait。

[https://blog.csdn.net/hzwy23/article/details/53240333][https_blog.csdn.net_hzwy23_article_details_53240333]

# Oracle游标之select for update和where current of 语句 #

2013年06月27日 10:57:09 [luckystar2008][] 阅读数：3150

转载[http://hanjiangduqiao.blog.163.com/blog/static/613105442011431111153601][http_hanjiangduqiao.blog.163.com_blog_static_613105442011431111153601]

使用select for update 语句可以使用行锁锁定你要更改的记录．当遇到下一个commit和rollback语句时会被释放．

The **Select For Update** statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

语法如下：The syntax for the **Select For Update** is:

CURSOR cursor\_name  
IS  
   select\_statement  
   FOR UPDATE \[of column\_list\] \[NOWAIT\];

当你要使用游标进行更新和删除操作时，则需要使用where current of 语句，这个是标示出当前游标的位置．

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.

语法如下：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;
> 
>   
> 在这时的例子中我采用上面所建的test表来试验．  
>   
> **２.1 更新．**  
>   
> SQL> DECLARE  
>   2  CURSOR  test\_cur IS SELECT  \* FROM test  
>   3  FOR UPDATE OF sal;  
>   4  BEGIN  
>   5  FOR test\_rec IN  test\_cur LOOP  
>   6  UPDATE test  
>   7  SET sal = test\_rec.sal +1  
>   8  WHERE CURRENT OF test\_cur;  
>   9  END LOOP;  
>  10  COMMIT;  
>  11  END;  
>  12  /  
>   
> PL/SQL 过程已成功完成。  
>   
> **２.2 删除．**  
>   
> SQL> DECLARE  
>   2  CURSOR test\_cur IS select \* from test for update;  
>   3  BEGIN  
>   4  FOR test\_rec IN test\_cur LOOP  
>   5  DELETE FROM test WHERE CURRENT OF test\_cur;  
>   6  END LOOP;  
>   7  END;  
>   8  /  
>   
> PL/SQL 过程已成功完成。  
>   
> 文中的游标只是简单的使用，在记录到pl/sql详细编程的时候会再讲到时会结合oracle的执行计划并一起讨论它们的执行效率．  
>   
> 注：文中的英文注解部分出自：http://www.techonthenet.com/oracle/cursors/current\_of.php

[https://blog.csdn.net/qincidong/article/details/9185693][https_blog.csdn.net_qincidong_article_details_9185693]

[hzwy23]: https://me.csdn.net/hzwy23
[2018110522483248.png]: /images/20220402/e96af9690f224f5aa5e517886ab9f2d3.png
[https_blog.csdn.net_hzwy23_article_details_53240333]: https://blog.csdn.net/hzwy23/article/details/53240333
[luckystar2008]: https://me.csdn.net/qincidong
[http_hanjiangduqiao.blog.163.com_blog_static_613105442011431111153601]: http://hanjiangduqiao.blog.163.com/blog/static/613105442011431111153601/
[https_blog.csdn.net_qincidong_article_details_9185693]: https://blog.csdn.net/qincidong/article/details/9185693