PL/SQL：有关FORALL的用法与FOR，插入记录对比

今天看了一个2010年发的一个贴子。记录一下动态使用FORALL语句与FOR的性能对比，测试环境为ORACLE 10G

1.建立二个表

create table a_tab(ver number,id number)

A_tab表中写了20000条记录；

create table b_tab(ver number,id number)

B_tab表中无记录;

2.给a_tab表增加数据;

declare
  l_add integer:=0;
begin
  for i in 1..20000 loop
   l_add:=l_add+1;
   execute immediate 'insert into a_tab values(:a,:b)' using i,l_add;
  end loop;
  commit;
end;
/

PL/SQL procedure successfully completed
Executed in 2.172 seconds

SQL> select count(*) from a_tab;
  COUNT(*)
----------
     20000
Executed in 0.031 seconds
下面这段代码为不知道表名插入数据，使用FORALL语句实现

 declare
    v_sql varchar2(4000);
    v_tablename varchar2(100):='b_tab';
 begin
    v_sql:='
           declare
             type r_outtab is record (ver NUMBER,id NUMBER);
             type t_outtab is table of r_outtab index by binary_integer;
             v_outtab t_outtab;
             v_query varchar2(30000);
           begin
           v_query :=''select ver,id from a_tab '' ;
           execute immediate v_query  bulk collect into  v_outtab;
           forall i in v_outtab.first .. v_outtab.last
             insert into  '||v_tablename||'  values v_outtab(i) ;
           end;';
    dbms_output.put_line(v_sql);
    execute immediate v_sql;
    commit;
 end;
/

PL/SQL procedure successfully completed 
Executed in 0.203 seconds

PL/SQL procedure successfully completed                  
Executed in 0.125 seconds

以上是二次插入时间

SQL> select count(*) from b_tab; 
  COUNT(*)
----------
     20000
Executed in 0.015 seconds
下面这段代码为FOR语句实现

declare
   type r_outtab is record(
   ver NUMBER,
   id  NUMBER);
   type t_outtab is table of r_outtab index by binary_integer;
   v_outtab    t_outtab;  
   v_tablename varchar2(100);
   v_query     varchar2(30000);
begin
   v_tablename := 'b_tab';
   v_query     := 'select ver,id from a_tab ';
   execute immediate v_query bulk collect into v_outtab;
   for i in v_outtab.first .. v_outtab.last loop
     execute immediate 'insert into ' || v_tablename || ' values(:a, :b)' using v_outtab(i).ver, v_outtab(i).id;
   end loop; 
end;
/
	PL/SQL procedure successfully completed
	Executed in 10.906 seconds
	PL/SQL procedure successfully completed
	Executed in 27.063 seconds

以上二次插入数据 
SQL> select count(*) from b_tab;
  COUNT(*)
----------
     60000
Executed in 0.078 seconds

通过二次对比，使用FORALL的动态SQL比FOR的插入要快
但是FORALL不支持下面语句请大家注意，使用按上面的例子使用

declare
     type r_outtab is record (ver NUMBER,id NUMBER);
     type t_outtab is table of r_outtab index by binary_integer;
     v_outtab t_outtab;
     v_query varchar2(30000);
     v_tablename varchar2(100):='a_tab';
begin
     v_query :='select ver,id from b_tab ' ;
     execute immediate v_query  bulk collect into  v_outtab;
     forall i in v_outtab.first .. v_outtab.last
       execute immediate 'insert into '||v_tablename||' values (:a,:b)' using v_outtab(i).ver,v_outtab(i).id ;
end;
 
ORA-06550: line 13, column 82:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 13, column 11:
PL/SQL: Statement ignored
ORA-06550: line 13, column 11:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL