一般来讲在PL/SQL开发过程中,我们可以采用bulk collect将查询一次加载到一个集合(collection)中,而不是传统的通过cursor一条条的处理。大大提高了效率减少了loop循环的开销,但是需要注意的是bulk collect的所有承载变量都应该是collection类型的。
简单的例子:
1.通过cursor的fetch into来使用bulk collect
declare
type id_value is table of alan.id%type;
my_result id_value;
cursor mycur is select id from alan where id <= 10;
begin
open mycur;
fetch mycur bulk collect into my_result;
-- dbms_output.put_line(mycur%rowcount);
close mycur;
for i in 1 .. my_result.count
loop
dbms_output.put_line(my_result(i));
end loop;
end;
/
2.加limit来限制fetch的数量,这里只从cursor里面fetch 5行数据
declare
type id_value is table of alan.id%type;
my_result id_value;
cursor mycur is select id from alan where id <= 10;
begin
open mycur;
fetch mycur bulk collect into my_result limit 5;
-- dbms_output.put_line(mycur%rowcount);
close mycur;
for i in 1 .. my_result.count
loop
dbms_output.put_line(my_result(i));
end loop;
end;
/
3.在select中使用bulk collect的用法
declare
type id_value is table of alan.id%type;
my_result id_value;
begin
select id bulk collect into my_result from alan where rownum <= 15;
for i in 1 .. my_result.count
loop
dbms_output.put_line(my_result(i));
end loop;
end;
/
简单的例子:
1.通过cursor的fetch into来使用bulk collect
declare
type id_value is table of alan.id%type;
my_result id_value;
cursor mycur is select id from alan where id <= 10;
begin
open mycur;
fetch mycur bulk collect into my_result;
-- dbms_output.put_line(mycur%rowcount);
close mycur;
for i in 1 .. my_result.count
loop
dbms_output.put_line(my_result(i));
end loop;
end;
/
2.加limit来限制fetch的数量,这里只从cursor里面fetch 5行数据
declare
type id_value is table of alan.id%type;
my_result id_value;
cursor mycur is select id from alan where id <= 10;
begin
open mycur;
fetch mycur bulk collect into my_result limit 5;
-- dbms_output.put_line(mycur%rowcount);
close mycur;
for i in 1 .. my_result.count
loop
dbms_output.put_line(my_result(i));
end loop;
end;
/
3.在select中使用bulk collect的用法
declare
type id_value is table of alan.id%type;
my_result id_value;
begin
select id bulk collect into my_result from alan where rownum <= 15;
for i in 1 .. my_result.count
loop
dbms_output.put_line(my_result(i));
end loop;
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12361284/viewspace-205087/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12361284/viewspace-205087/