批量SQL(原创)

批量sql概述

对 PL/SQL而言，任何的PL/SQL块或者子程序都是PL/SQL引擎来处理，而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL 引擎来处理，SQL引擎处理完毕后向PL/SQL引擎返回数据。Pl/SQL与SQL引擎之间的通信则称之为上下文切换。过多的上下文切换将带来过量的性 能负载。因此为减少性能的FORALL与BULK COLLECT的子句应运而生，即仅仅使用一次切换多次执行来降低上下文切换次数。

注意：并不是所有的SQL，FORALL语句都会一次性将SQL转交到SQL引擎来处理。具体可参见下面的例子2，例子3

FORALL

FORALL语法描述
FORALL loop_counter IN bounds_clause            -->注意FORALL块内不需要使用loop, end loop
SQL_STATEMENT [SAVE EXCEPTIONS];
bounds_clause的形式
lower_limit .. upper_limit                                     -->指明循环计数器的上限和下限，与for循环类似
INDICES OF collection_name BETWEEN lower_limit .. upper_limit  -->引用特定集合元素的下标(该集合可能为稀疏，即当集合(嵌套表或联合数组)中的元素被删除之后，对稀疏集合实现迭代。)
VALUES OF colletion_name                             --> VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。VALUES OF选项使用时有一些限制

如果VALUES OF子句中所使用的集合是联合数组，则必须使用PLS_INTEGER和BINARY_INTEGER进行索引；
VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER；
当VALUES OF 子句所引用的集合为空，则FORALL语句会导致异常；
SQL_STATEMENT部分：SQL_STATEMENT部分必须是一个或者多个集合的静态或者动态的DML(insert,update,delete)语句。
SAVE EXCEPTIONS部分：对 于任意的SQL语句执行失败，将导致整个语句或整个事务会滚。而使用SAVE EXCEPTIONS可以使得在对应的SQL语句异常的情形下，FORALL仍然可以继续执行。如果没有SAVE EXCEPTIONS时，当异常发生，FORALL语句就会停止执行。SAVE EXCEPTIONS使得FORALL子句中的DML下产生的所有异常都记录在SQL%BULK_EXCEPTIONS的游标属性中。 SQL%BULK_EXCEPTIONS属性是个记录集合，其中的每条记录由两个字段组成，ERROR_INDEX和ERROR_CODE。 ERROR_INDEX字段会存储发生异常的FORALL语句的迭代编号，而ERROR_CODE则存储对应异常的ORACLE错误代码。类似于这样： (2,01400)，(6,1476)和(10,12899)。存放在%BULK_EXCEPTIONS中的值总是与最近一次FORALL语句执行的结果 相关，异常的个数存放在%BULK_EXCEPTIONS.COUNT属性中，%BULK_EXCEPTIONS有效的下标索引范围在1 到%BULK_EXCEPTIONS.COUNT之间。

%BULK_ROWCOUNT 也是专门为FORALL设计的，用于保存第i个元素第i次insert或update或delete所影响到的行数。如果第i次操作没有行被影响， 则%BULK_ROWCOUNT返回为零值。FORALL语句和%BULK_ROWCOUNT属性使用同样的下标索引。如果FORALL使用下标索引的范 围在5到8的话，那么 
%BULK_ROWCOUNT的也是5到8。需要注意的是一般情况下，对于insert .. values而言，所影响的行数为1，即%BULK_ROWCOUNT的值为1。而对于insert .. select方式而言，%BULK_ROWCOUNT的值就有可能大于1。update与delete语句存在0，1，以及大于1的情形。 

FORALL综合示例

示例1，循环效率和indices of

SQL> CREATE TABLE t  ( 
   col_num   NUMBER 
  ,col_var   VARCHAR2( 10 )  ); 

SQL>  declare
  2     type col_num_type is table of number index by pls_integer;       -->声明了两个联合数组
  3     type col_var_type is table of varchar2(10) index by pls_integer;
  4     col_num_tab col_num_type;
  5     col_var_tab col_var_type;
  6     v_start_time integer;
  7     v_end_time   integer;
  8     v_total      integer;
  9  begin
 10    for i in 1..5000 loop      -->使用FOR循环向数组填充元素 
 11      col_num_tab(i) := i;
 12      col_var_tab(i) := 'var_' || i ;
 13    end loop;
 14    v_start_time := dbms_utility.get_time;     -->获得FOR循环向表t插入数据前的初始时间
 15    for i in 1..5000 loop     -->使用FOR循环向表t插入数据
 16      insert into t
 17      values(col_num_tab(i),col_var_tab(i));
 18    end loop;
 19    v_end_time := dbms_utility.get_time; rollback;    -->获得FOR循环向表t插入数据前的结束时间

 20    dbms_output.put_line('Duration of the for loop: ' || (v_end_time - v_start_time));
 21    v_start_time := dbms_utility.get_time;     -->获得FOR循环向表t插入数据前的初始时间

 22    forall i in 1..5000       -->使用FORALL循环向表t插入数据
 23      insert into t
 24      values(col_num_tab(i),col_var_tab(i));
 25    v_end_time :=dbms_utility.get_time; rollback;    -->获得FORALL循环向表t插入数据前的结束时间

26    dbms_output.put_line('Duration of the forall statement: ' || (v_end_time - v_start_time));
 27    col_num_tab.delete(2);col_num_tab.delete(4);col_num_tab.delete(6);      -->此处删除了数组中的第二个元素，导致数组变为稀疏型
 28    col_var_tab.delete(2);col_var_tab.delete(4);col_var_tab.delete(6);
 29    forall i in indices of col_num_tab    -->此处使用了indices OF empno_tab，则所有未被delete的元素都将进入循环
 30       insert into t2
 31       values(col_num_tab(i),col_var_tab(i));
 32    select count(*) into v_total from t2;
 33    dbms_output.put_line('The amount rows of t2: ' || v_total);
 34*   end;
 35  /
Duration of the for loop: 105   -->此处的计时单位为百分之一秒，即1.05s，下同 
Duration of the forall statement: 2
The amount rows of t2: 4997     -->此处少了3条记录。

PL/SQL procedure successfully completed.

示例2，save exception和values of

SQL> create table tb_emp_ins_log(empno number,ename varchar2(50),hiredate date); 
-->创建表tb_emp_ins_log用于记录错误记录 

Table created.

SQL> CREATE TABLE tb_emp AS              -->创建表tb_emp 
   SELECT empno, ename, hiredate 
   FROM   emp 
   WHERE  1 = 2; 

SQL> ALTER TABLE tb_emp MODIFY(empno NOT NULL);   -->为表添加约束 
SQL> declare
  2     type col_num_type is table of number index by pls_integer;    -->一共定义了4个联合数组类型 
  3     type col_var_type is table of varchar2(100) index by pls_integer;
  4     type col_date_type is table of date index by pls_integer;
  5     type ins_log_type is table of pls_integer index by pls_integer;   -->用于填充异常记录的元素值 
  6     empno_tab col_num_type;
  7     ename_tab col_var_type;
  8     hiredate_tab col_date_type;
  9     ins_log ins_log_type;
 10     v_counter pls_integer := 0;
 11     v_total integer := 0;
 12     errors exception;       -->异常声明 
 13     pragma exception_init( errors, -24381);
 14  begin
 15    for rec in (select empno,ename,hiredate from emp) loop    -->使用for循环将数据填充到联合数组
 16      v_counter := v_counter + 1 ;
 17      empno_tab(v_counter) := rec.empno;
 18      ename_tab(v_counter) := rec.ename;
 19      hiredate_tab(v_counter) := rec.hiredate;
 20    end loop;
 21   
 22    empno_tab(2) := null;      -->对部分数据进行处理以产生异常
 23    ename_tab(5) := rpad(ename_tab(5),15,'*');
 24    empno_tab(10) := null;
 25   
 26    forall i in 1..empno_tab.count    -->使用forall将联合数组中的数据插入到表tb_emp 
 27    save exceptions
 28      insert into tb_emp
 29      values(empno_tab(i),ename_tab(i),hiredate_tab(i));
 30    commit;

 32    exception
 33      when errors then
 34        dbms_output.put_line('There are ' || SQL%bulk_exceptions.count || ' exceptions');
 35        for i in 1..SQL%bulk_exceptions.count loop  -->SQL%bulk_exceptions.COUNT记录异常个数来控制迭代 
 36          ins_log(i) := SQL%bulk_exceptions(i).error_index;        -->异常记录的索引值将填充ins_log_type联合数组，此处的结果是ins_log(1)=2,  ins_log(2)=5,  ins_log(3)=10
 38          dbms_output.put_line(
 39                                'Record '
 40                                || SQL%bulk_exceptions(i).error_index
 41                                || ' caused error '
 42                                || i
 43                                || '; '
 44                                || SQL%bulk_exceptions(i).error_code
 45                                || ' '
 46                                || SQLERRM( -SQL%bulk_exceptions(i).error_code));   -->使用SQLERRM根据错误号抛出具体的错误信息
 47        end loop;
 49     for i in empno_tab.first .. empno_tab.last loop
 50        dbms_output.put_line(SQL%bulk_rowcount(i) || ' rows are inserted into tb_emp!');
 51     end loop;
 52     select count(*) into v_total from tb_emp;
 53     dbms_output.put_line(v_total || ' rows are inserted into tb_emp!');

 55     forall i in values of ins_log    -->使用VALUES OF子句为ins_log_type联合数组中的元素值 
 56        insert into tb_emp_ins_log
 57        values(empno_tab(i),ename_tab(i),hiredate_tab(i));  -->异常记录的索引值将填充ins_log_type联合数组 
 58     commit;
 60  end;
 61  /
There are 3 exceptions
Record 2 caused error 1; 1400 ORA-01400: cannot insert NULL into ()
Record 5 caused error 2; 12899 ORA-12899: value too large for column  (actual: , maximum: )
Record 10 caused error 3; 1400 ORA-01400: cannot insert NULL into ()
1 rows are inserted into tb_emp!
0 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
0 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
0 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
11 rows are inserted into tb_emp!
PL/SQL procedure successfully completed.
SQL> select * from tb_emp_ins_log;    -->异常的记录被插入到表tb_emp_ins_log 
     EMPNO ENAME           HIREDATE
---------- --------------- ------------------
           ALLEN           20-FEB-81
      7654 MARTIN********* 28-SEP-81
           TURNER          08-SEP-81

FROALL语句SQL%BULK_COUNT的使用可以参见下面例子：

例子3

create table emp_temp as select * from emp;
declare
  TYPE numlist is table of NUMBER;
  depts numlist := numlist(10,20,30);
begin
  forall j in depts.first .. depts.last
    delete from emp_temp where deptno = depts(j);
  for i in depts.first .. depts.last loop
    dbms_output.put_line('Statement #' || i || ' deleted ' ||
    SQL%BULK_ROWCOUNT(i) || ' rows.');
  end loop;
  dbms_output.put_line('Total rows deleted: ' || SQL%ROWCOUNT);
END;
/
Statement #1 deleted 3 rows.
Statement #2 deleted 5 rows.
Statement #3 deleted 6 rows.
Total rows deleted: 14
PL/SQL procedure successfully completed.

BULK COLLECT

BULK COLLECT 子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。

使用LIMIT限制FETCH数据量
在 使用BULK COLLECT 子句时，对于集合类型，如嵌套表，联合数组等会自动对其进行初始化以及扩展(如下示例)。因此如果使用BULKCOLLECT子句操作集合，则无需对集合 进行初始化以及扩展。由于BULK COLLECT的批量特性，如果数据量较大，而集合在此时又自动扩展，为避免过大的数据集造成性能下降，因此使用limit子句来限制一次提取的数据量。 limit子句只允许出现在fetch操作语句的批量中。
用法：
FETCH ... BULK COLLECT INTO ... [LIMIT rows]

RETURNING子句的批量绑定
BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用。当与这几个DML语句结合时，我们需要使用RETURNING子句来实现批量绑定。

BULK COLLECT综合示例

SQL> declare
  2    cursor emp_cur is
  3       select empno,ename,hiredate from emp;
  4    type emp_rec_type is record(
  5         empno emp.empno%type,
  6         ename emp.ename%type,
  7         hiredate emp.hiredate%type);
  type nested_emp_type is table of emp_rec_type;
  emp_tab1 nested_emp_type;
  emp_tab1 nested_emp_type;
  emp_tab2 nested_emp_type;
  v_limit integer := 5;
  v_counter integer := 0;
begin
  open emp_cur;
  loop
    fetch emp_cur  -->fetch时使用了BULK COLLECT子句,并使用limit子句限制提取数据量     

   bulk collect into emp_tab1
    limit v_limit;
 19    dbms_output.put_line('The recycle time : ' || v_counter);
  exit when emp_tab1.count = 0;  -->注意此时游标退出使用了emp_tab.COUNT，而不是emp_cur%notfound 

    delete from emp where deptno = 20
    v_counter := v_counter + 1;     -->记录使用LIMIT之后fetch的次数 
  end loop;
  dbms_output.put_line('The recycle time : ' || v_counter);
 
    delete from emp where deptno = 20
    returning empno,ename,hiredate
    bulk collect into emp_tab2;tab2;     -->使用returning将前面返回的列的数据批量插入到集合变量
 27 
    end loop;i in emp_tab2.first..emp_tab2.last loop
    for i in emp_tab2.first..emp_tab2.last loop
       dbms_output.put_line('Current record : ' || emp_tab2(i).empno
                         || '     '
                         || emp_tab2(i).ename
                         || '     '
                         || emp_tab2(i).hiredate
                         || ' hase been deleted');
    end loop;
 36  end;
 37  /
The recycle time : 3
Current record : 7369     SMITH     17-DEC-80 hase been deleted
Current record : 7566     JONES     02-APR-81 hase been deleted
Current record : 7788     SCOTT     19-APR-87 hase been deleted
Current record : 7876     ADAMS     23-MAY-87 hase been deleted
Current record : 7902     FORD     03-DEC-81 hase been deleted
PL/SQL procedure successfully completed.

BULK COLLECT的限制
1、不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2、只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
3、BULK COLLECT INTO 的目标对象必须是集合类型。
4、复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5、如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO 子句中使用。
6、如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

 

参考至：《Oracle PL/SQL实例精解》Benjamin著
            http://blog.csdn.net/robinson_0612/article/details/7536926
            http://blog.csdn.net/robinson_0612/article/details/7545597
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:[e-mail]