批量绑定:从oracle9i开始,oracle提供批量绑定关键词forall和bulk collect通过批量绑定技术,极大地加快了数据的处理速度。在不使用批量绑定的时候,为了将嵌套表中的数据插入数据库表中,需要循环执行insert语句,而使用forall关键词可以让所有插入内容集中在一次insert中执行,从而加快了执行速度。Bulk collect子句用于取得批量数据,该子句只能用于select语句、fetch语句和DML返回子句中;而forall语句只适用于执行批量的DML操作。 declare type id_table_type is table of number(6) index by binary_integer; type name_table_type is table of varchar2(10) index by binary_integer; id_table id_table_type; name_table name_table_type; start_time number(10); end_time number(10); begin for i in 1..100000 loop id_table(i) := i; name_table(i) := 'name'||i; end loop; delete from demo; start_time := dbms_utility.get_time; for i in 1..100000 loop insert into demo values(id_table(i),name_table(i)); end loop; end_time := dbms_utility.get_time; dbms_output.put_line('花费时间:'||to_char((end_time-start_time)/100));
delete from demo; start_time := dbms_utility.get_time; forall i in 1..100000 insert into demo values(id_table(i),name_table(i)); end_time := dbms_utility.get_time; dbms_output.put_line('花费时间:'||to_char((end_time-start_time)/100)); end;
declare
type id_table_type is table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..100000 loop
id_table(i) := i;
name_table(i) := 'name'||i;
end loop;
delete from demo;
start_time := dbms_utility.get_time;
for i in 1..100000 loop
insert into demo values(id_table(i),name_table(i));
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('花费时间:'||to_char((end_time-start_time)/100));
delete from demo;
start_time := dbms_utility.get_time;
forall i in 1..100000
insert into demo values(id_table(i),name_table(i));
end_time := dbms_utility.get_time;
dbms_output.put_line('花费时间:'||to_char((end_time-start_time)/100));
end;