利用游标更新数据

转载http://blog.sina.com.cn/s/blog_55844be4010008pb.html

基本用法：
 cursor c1 is

     select *

       from tablename

 

   

   where name is null

        for update [of column]

 ……

 for r1 in c1 loop

     update tablename

        set column = ……   --已限制了条件了，更新只是当前记录集  

      where current of c1;
end loop;

where current of 子句只能在定义游标的时候使用了for update语句才可以使用。  
上面的语句中r1 是c1中的一行数据，更新的时候应该用current of c1，因为c1只是一个隐式游标，它本身不是通过for update定义的，只是代表了循环中的当前记录。这样，你的update语句或者delete语句的作用范围就只在你循环的当前行的范围中了。  
   
要注意的是，用for update定义的游标会让数据库对涉及的行加锁，别的会话如果要访问该游标中的行便会进入等待状态。你也可以明确指明要加锁的列，用for update of "列名"  就可以了。 

如果select for update选中的行已经被别的会话加锁的话,会话就需要等待解锁，如果别的会话一直不解锁，那么你的select就会一直等待下去，如果你不想等,只需在for update后面加上nowait就可以解决这个问题了,这样你的选择会立即返回。

针对where current of的使用和我们日常的更新方法，进行了一个简单的测试。
编写了两个简单的过程，比较使用where current of和不使用时，性能的差异。
SQL> create table t
  2  as
  3  select * from all_objects;
表已创建。

SQL> create table t1
  2  as
  3  select * from t;
表已创建。

SQL> select count(*) from t;
  COUNT(*)
----------
     37003

SQL> exec runstats.rs_start;
PL/SQL 过程已成功完成。

SQL> declare
  2    cursor sal_cursor is
  3      select object_name
  4        from t
  5         for update nowait;
  6  begin
  7    for i in sal_cursor loop
  8      update t
  9         set data_object_id = data_object_id  + 1
 10       where current of sal_cursor;
 11    end loop;
 12  end;
 13  /
PL/SQL 过程已成功完成。

SQL> exec runstats.rs_middle;
PL/SQL 过程已成功完成。

SQL> declare
  2    cursor sal_cursor is
  3      select object_name
  4        from t1
  5         for update nowait;
  6  begin
  7    for i in sal_cursor loop
  8      update t1
  9         set data_object_id = data_object_id  + 1
 10       where object_name = i.object_name;
 11    end loop;
 12    commit;
 13  end;
 14  /
PL/SQL 过程已成功完成。

SQL> exec runstats.rs_stop(1000000);
Run1 ran in 3767 hsecs
Run2 ran in 126597 hsecs
run 1 ran in 2.98% of the time
Name                                  Run1        Run2        Diff
STAT..no work - consistent rea      37,268   9,212,891   9,175,623
STAT..table scan blocks gotten      37,247   9,212,999   9,175,752
STAT..buffer is not pinned cou      37,282   9,213,043   9,175,761
STAT..consistent gets               37,311   9,400,510   9,363,199
STAT..session logical reads        119,901   9,521,124   9,401,223
STAT..redo size                 15,563,548  25,830,156  10,266,608
LATCH.cache buffers chains         502,564  19,504,830  19,002,266
STAT..table scan rows gotten        73,998########################
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
782,133  20,678,374  19,896,241      3.78%
PL/SQL 过程已成功完成。

对t1表建立索引的差异：
SQL> analyze index idx_object_name compute statistics;
索引已分析

SQL> exec runstats.rs_stop(100000);
Run1 ran in 2989 hsecs
Run2 ran in 5250 hsecs
run 1 ran in 56.93% of the time
Name                                  Run1        Run2        Diff
STAT..session logical reads        112,542     231,954     119,412
STAT..session logical reads        112,542     232,502     119,960
STAT..session logical reads        112,542     233,080     120,538
STAT..session pga memory                 0     131,072     131,072
STAT..session pga memory                 0     131,072     131,072
LATCH.cache buffers chains         488,072     828,849     340,777
LATCH.cache buffers chains         488,072     832,711     344,639
LATCH.cache buffers chains         488,072     835,333     347,261
STAT..redo size                 15,561,972  25,909,144  10,347,172
STAT..redo size                 15,561,972  25,970,072  10,408,100
STAT..redo size                 15,561,972  26,030,812  10,468,840
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
2,293,947   3,511,140   1,217,193     65.33%
PL/SQL 过程已成功完成。