forall的sql%bulk_rowcount属性

在forall操作中，Sql%rowcount表示commit之前被影响的记录总行数，而sql%bulk_rowcount(i)表示每一次的操作所影响的行数，该值是存在一个集合里，forall中的第n条dml语句处理的行数存储在该集合的第n个元素中

SQL> select * from test;

 

        ID NAME                                                             SALARY

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

         1 aaa                                                                2000

         1 bbb                                                                2000

         1 bbb                                                                2000

         1 bbb                                                                2000

         1 ccc                                                                2000

         1 ccc                                                                2000

         1 ddd                                                                2000

         1 eee                                                                2000

 

8 rows selected.

 

SQL> declare

  2     type type_name is table of test.name%type ;

  3     tab_name type_name :=type_name('aaa','bbb','ccc','ddd','eee');

  4  begin

  5     forall name_idx in

  6            tab_name.first .. tab_name.last

  7         update test set salary=salary+500

  8                    where name = tab_name(name_idx);

  9     dbms_output.put_line(sql%rowcount||'行记录被更新！');

 10     for i in 1 .. tab_name.count loop

 11     dbms_output.put_line('名称为'||tab_name(i)||'的记录共有'||sql%bulk_rowcount(i)||'被更新！');

 12     end loop;

 13  end;

 14  /

8行记录被更新！

名称为aaa的记录共有1被更新！

名称为bbb的记录共有3被更新！

名称为ccc的记录共有2被更新！

名称为ddd的记录共有1被更新！

名称为eee的记录共有1被更新！

 

PL/SQL procedure successfully completed.

 

SQL> select * from test;

 

        ID NAME                                                             SALARY

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

         1 aaa                                                                2500

         1 bbb                                                                2500

         1 bbb                                                                2500

         1 bbb                                                                2500

         1 ccc                                                                2500

         1 ccc                                                                2500

         1 ddd                                                                2500

         1 eee                                                                2500

 

8 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-710905/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-710905/