fetch bulk collect into 简单学习

/*

 fetch bulk collect into 的使用格式是：fetch some_cursor bulk collect into col1, col2 limit xxx。

 col1、col2 是声明的集合类型变量，xxx 为每次取数据块的大小（记录数），

 相当于缓冲区的大小，可以不指定 limit xxx 大小

*/

-- 1.给一张表加数据，以便测试用

declare i number(8) := 0;

begin

 for i in 0..100000 loop

   insert into t_user(id,username,password) values(seq_t_user.nextVal,'xiweiyuan'||i,'xiweiyuan'||i);

 end loop;  

end;

/  

-- 或者这样--

begin

 for i in 1..200000 loop

insert into t_user(id,username,password) select seq_t_user.nextVal,'hanchao','xiweiyuan' from dual;

 end loop;

 commit;

end;

/

--2.使用fetch bulk collect into 获取游标的数据

declare

  -- 声明需要集合类型及变量，参照字段的type来声明类型。

typeid_type istable of t_user.id%type;

v_idid_type;

  type username_type is table of t_user.username%type;

  v_username username_type;

  type password_type is table of t_user.password%type;

  v_password password_type;

  cursor cur_t_user is select id,username,password from t_user where rownum <= 1000 ;--for update;

  i number(8) := 0;

  j number(8) := 0;

begin

 open cur_t_user;

 loop

   i := i + 1; -- 记录外层循环多少次

fetch cur_t_user bulk collect into v_id,v_username,v_password limit 256;

   --dbms_output.put_line('fetch OK → 缓冲区大小:' || v_id.count || ',外层循环次数 :' || i);

for j in 1..v_id.count loop -- 遍历集合

    -- dbms_output.put_line('fetch OK → 缓冲区大小:' || v_id.count || ',内层循环次数 :' || j);

    insert into t_user_bak(id,u,p) values(v_id(j),v_username(j),v_password(j));

      --null;

   end loop;

  exit when cur_t_user%notfound; --exit 不能紧跟着fetch后面了，不然最后不够256部分会被漏掉

 end loop;

 close cur_t_user;

end;

/  

--3.使用%rowtype的例子

declare

   -- 声明需要集合类型及变量，参照字段的type来声明类型。

   type user_type is table of t_user%rowtype;

   v_user user_type;

   n number(5) := 0;

   cursor cur_t_user is --用rownum来限制取出的记录的条数

          select * from t_user where rownum <= 100;

begin

 open cur_t_user;

 loop

   n := n + 1;

   fetch cur_t_user bulk collect into v_user limit 10;

          if v_user.count = 0 then

            exit;

          end if;

   dbms_output.put_line('fetch OK → 缓冲区大小:' || v_user.count || ',begin :' || v_user.first || ',end :' || v_user.last || ',,循环的次数：' || n);

   for i in v_user.first..v_user.last loop --遍历集合

            dbms_output.put_line('nei  缓冲区大小:' || v_user.count || ',begin :' || v_user.first || ',end :' || v_user.last || ',,循环的次数：' || n);

           insert into t_user_bak(id,u,p) values(v_user(i).id,v_user(i).username,v_user(i).password);

   end loop;

   exit when cur_t_user%notfound;

 end loop;

 close cur_t_user;

end;

/                    

--4.使用%rowtype的例子,不用last

declare

   -- 声明需要集合类型及变量，参照字段的type来声明类型。

   type user_type is table of t_user%rowtype;

   v_user user_type;

   n number(5) := 0;

   i number(5) := 0;

   cursor cur_t_user is --用rownum来限制取出的记录的条数

          select * from t_user where rownum <= 100000;

begin

 open cur_t_user;

 loop

   n := n + 1;

   fetch cur_t_user bulk collect into v_user limit 256;

  -- dbms_output.put_line('fetch OK → 缓冲区大小:' || v_user.count || ',begin :' || v_user.first || ',end :' || v_user.last || ',,外层循环的次数：' || n);

  for i in 1..v_user.count loop --遍历集合

           -- dbms_output.put_line('nei  缓冲区大小:' || v_user.count || ',begin :' || v_user.first || ',end :' || v_user.last || ',,外层循环的次数：' || n  || ',内层循环次数：' || i);

insert into t_user_bak(id,u,p) values(v_user(i).id,v_user(i).username,v_user(i).password);

   end loop;

   exit when cur_t_user%notfound;

 end loop;

 close cur_t_user;

end;

/                    

--5.使用%rowtype的例子,例4加forall 此种方法与上面几种方法貌似就不是一个数量级的，很快

declare

   -- 声明需要集合类型及变量，参照字段的type来声明类型。

   type user_type is table of t_user%rowtype;

   v_user user_type;

   n number(5) := 0;

   i number(5) := 0;

   cursor cur_t_user is --用rownum来限制取出的记录的条数

          select * from t_user where rownum <= 1000;

begin

 open cur_t_user;

 loop

   n := n + 1;

   fetch cur_t_user bulk collect into v_user limit 256;

   dbms_output.put_line('fetch OK → 缓冲区大小:' || v_user.count || ',begin :' || v_user.first || ',end :' || v_user.last || ',,外层循环的次数：' || n);

forall i in 1..v_user.count --loop --遍历集合

           insert into t_user_bak(id,u,p) values(v_user(i).id,v_user(i).username,v_user(i).password);

   --end loop;

   exit when cur_t_user%notfound;

 end loop;

 close cur_t_user;

end;

/

你可以根据你的实际来调整 limit 参数的大小，来达到你最优的性能。limit 参数会影响到 pga 的使用率。

参考地址如下：

http://www.cnblogs.com/haibin168/archive/2011/02/27/1966072.html

http://blog.163.com/zangyunling@126/blog/static/164624505201051141848630/

有问题再讨论吧。晚安。亲

=====================================================================================

----批量提交

declare

  -- 声明需要集合类型及变量，参照字段的type来声明类型。

  type user_type is table of t_user%rowtype;

  v_user user_type;

  n number(5) := 0;

  i number(5) := 0;

y number(10) := 0;

  cursor cur_t_user is --用rownum来限制取出的记录的条数

         select * from t_user where rownum <= 300002;

begin

open cur_t_user;

loop

  n := n + 1;

  fetch cur_t_user bulk collect into v_user limit 256;

 -- dbms_output.put_line('fetch OK → 缓冲区大小:' || v_user.count || ',begin :' || v_user.first || ',end :' || v_user.last || ',,外层循环的次数：' || n);

  for i in 1..v_user.count loop --遍历集合

y := y + 1;

          insert into t_user_bak(id,u,p) values(v_user(i).id,v_user(i).username,v_user(i).password);

         -- 批量提交数据

          if mod(y,5000) = 0 then

            dbms_output.put_line('commit...');

            commit;

          end if;  

  end loop;

  commit;

  exit when cur_t_user%notfound;

end loop;

dbms_output.put_line(y);

close cur_t_user;

end;

/

--发现批量提交在速度方面没有明显提高啊！

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

declare

  -- 声明需要集合类型及变量，参照字段的type来声明类型。

  type user_type is table of t_user%rowtype;

  v_user user_type;

  n number(5) := 0;

  i number(5) := 0;

  cursor cur_t_user is --用rownum来限制取出的记录的条数

         select * from t_user where rownum <= 300002;

begin

open cur_t_user;

loop

  n := n + 1;

  fetch cur_t_user bulk collect into v_user limit 256;

 -- dbms_output.put_line('fetch OK → 缓冲区大小:' || v_user.count || ',begin :' || v_user.first || ',end :' || v_user.last || ',,外层循环的次数：' || n);

  forall i in 1..v_user.count --loop --遍历集合

          insert into t_user_bak(id,u,p) values(v_user(i).id,v_user(i).username,v_user(i).password);

  --end loop;

commit;

  exit when cur_t_user%notfound;

end loop;

close cur_t_user;

end;

/

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

批量插入时，这样的效率貌似也挺快，但是数据良超大时，需要注意了！

begin

insert into t_user_bak(id,u,p) select id,username,password from t_user;

 commit;

end;

/  

================================================================================

关于Forall的一些用法，我们必须要注意的一些东西，以下是参考文章：

1. http://ears.iteye.com/blog/1485116 → 小样

2. http://blog.csdn.net/indexman/article/details/9262915 → 小样

3. http://allthingsoracle.com/bulk-processing-in-oracle-part-2/ → 出错了你可以看看！

4. http://blog.csdn.net/cnham/article/details/6109195 → 小样

5. http://blog.chinaunix.net/uid-298599-id-2443034.html → 原理。相当于3的翻译吧。

累死了。不搞了。准备下班去。

================================================================================