bulk collect 的使用

通常在SQL语句中给PL/SQL变量赋值叫做绑定（Binding）,一次绑定一个完整的集合称为批量绑定（Bulk Binding）。

批量绑定（Bulk binds）可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.

批量绑定（Bulk binds）包括：
(i) Input collections, use the FORALL statement，一般用来改善DML（INSERT、UPDATE和DELETE) 操作的性能
(ii) Output collections, use BULK COLLECT clause，一般用来提高查询（SELECT）的性能

FORALL的语法如下：

FORALL index IN lower_bound..upper_bound sql_statement;

具体的例子

[@more@]

declare
maxrows number default 1000;
row_id_table dbms_sql.Urowid_Table;
currcount_table dbms_sql.number_Table;
cursor cur_t2 is
select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
T2.id2, T2.curr_count, T1.rowid row_id
from T1, T2
where T1.id2=T2.id2
order by T1.rowid;
v_counter number;
begin
v_counter := 0;
open cur_t2;
LOOP
EXIT WHEN cur_t2%NOTFOUND;
FETCH cur_t2 bulk collect into row_id_table,currcount_table limit maxrows;
forall i in 1 .. row_id_table.count
update T1 set curr_count=currcount_table(i)
where rowid= row_id_table(i);
commit;
end loop;
end;
/

例子2

create or replace procedure bulk_1 is
t1 number;
t2 number;
type t_agent_id_b is table of agent_id_b%rowtype;
l_data t_agent_id_b;
cursor cur_bulk is
select agent_id from agent_id_b;
begin
SELECT DBMS_UTILITY.get_time into t1 from dual;

open cur_bulk;
loop
fetch cur_bulk bulk collect
into l_data limit 1000;
forall i in 1 .. l_data.count
insert into agent_id_b_bak values l_data (i);
exit when cur_bulk%notfound;
end loop;
commit;
close cur_bulk;
SELECT DBMS_UTILITY.get_time into t2 from dual;
dbms_output.put_line('time is ' || to_char(t2 - t1));
end bulk_1;

http://www.psoug.org/reference/array_processing.html

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

转载于:http://blog.itpub.net/66233/viewspace-1010439/