mysql bulk collect_fetch bulk collect into 简单学习

/*

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

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

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

*/

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

declareinumber(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_typeistable 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 whererownum<= 1000 ;--for update;

i number(8) := 0;

j number(8) := 0;

begin

open cur_t_user;

loop

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

fetchcur_t_userbulk collectintov_id,v_username,v_passwordlimit256;

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

for j in1..v_id.countloop-- 遍历集合

-- 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 oft_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 inv_user.first..v_user.lastloop --遍历集合

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 oft_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 的使用率。

参考地址如下：

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

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

----批量提交

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的一些用法，我们必须要注意的一些东西，以下是参考文章：

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

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