批量SQL之FORALL 语句

批量SQL之FORALL 语句

转自：http://blog.csdn.net/leshami/article/details/7536926

对PL/SQL而言，任何的PL/SQL块或者子程序都是PL/SQL引擎来处理，而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL引擎来处
理，SQL引擎处理完毕后向PL/SQL引擎返回数据。
Pl/SQL引擎与SQL引擎之间的通信则称之为上下文切换。过多的上下文切换将带来过量的性能负载。
因此为减少性能负载的FORALL与BULK COLLECT的子句应运而生。
即仅仅使用一次切换多次执行来降低上下文切换次数。本文主要描述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                                                                            -->引用特定集合元素的值
     
      SQL_STATEMENT部分：SQL_STATEMENT部分必须是一个或者多个集合的静态或者动态的DML(insert,update,delete)语句。
      SAVE EXCEPTIONS部分：对于SQL_STATEMENT部分导致的异常使用SAVE EXCEPTIONS来保证异常存在时语句仍然能够继续执行。

二、使用 FORALL 代替 FOR 循环提高性能

-->下面的示例使用了FOR循环与FORALL循环操作进行对比，使用FORALL完成同样的功能，性能明显提高   

CREATE TABLE t  
(  
    col_num    NUMBER  
  ,col_var    VARCHAR2( 10 )  
);  
 
DECLARE  
    TYPE col_num_type IS TABLE OF NUMBER                      -->声明了两个联合数组   
                                                    INDEX BY PLS_INTEGER;  
 
    TYPE col_var_type IS TABLE OF VARCHAR2( 10 )  
                                                    INDEX BY PLS_INTEGER;  
 
    col_num_tab      col_num_type;  
    col_var_tab      col_var_type;  
    v_start_time    INTEGER;  
    v_end_time        INTEGER;  
BEGIN  
    FOR i IN 1 .. 5000                                      -->使用FOR循环向数组填充元素   
    LOOP  
          col_num_tab( i ) := i;  
          col_var_tab( i ) := 'var_' || i;  
    END LOOP;  
 
    v_start_time := DBMS_UTILITY.get_time;  -->获得FOR循环向表t插入数据前的初始时间   
 
    FOR i IN 1 .. 5000                                    -->使用FOR循环向表t插入数据   
    LOOP  
          INSERT INTO t  
          VALUES ( col_num_tab( i ), col_var_tab( i ) );  
    END LOOP;  
 
    v_end_time  := DBMS_UTILITY.get_time;  
    DBMS_OUTPUT.put_line( 'Duration of the FOR LOOP: ' || ( v_end_time - v_start_time ) );  
   
   
    v_start_time := DBMS_UTILITY.get_time;  
   
    FORALL i IN 1 .. 5000                  -->使用FORALL循环向表t插入数据   
          INSERT INTO t  
          VALUES ( col_num_tab( i ), col_var_tab( i ) );  
 
    v_end_time  := DBMS_UTILITY.get_time;  
    DBMS_OUTPUT.put_line( 'Duration of the FORALL STATEMENT: ' || ( v_end_time - v_start_time ) );  
    COMMIT;  
END;  
 
Duration of the FOR LOOP: 68                    -->此处的计时单位为百分之一秒，即0.68s，下同   
Duration of the FORALL STATEMENT: 18  
 
PL/SQL procedure successfully completed.  



三、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)。
存放在SQL%BULK_EXCEPTIONS中的值总是与最近一次FORALL语句执行的结果相关，异常的个数存放在SQL%BULK_EXCEPTIONS的COUNT属性中，SQL%BULK_EXCEPTIONS有效的下标索引范围在1到SQL%BULK_EXCEPTIONS.COUNT之间。


1、SQL%BULK_EXCEPTIONS的用法  

CREATE TABLE tb_emp AS                          -->创建表tb_emp   
    SELECT empno, ename, hiredate  
    FROM    emp  
    WHERE  1 = 2;  
 
ALTER TABLE tb_emp MODIFY(empno NOT NULL);    -->为表添加约束   
 
DECLARE  
    TYPE col_num_type IS TABLE OF NUMBER                      -->一共定义了3个联合数组类型   
                                                    INDEX BY PLS_INTEGER;  
 
    TYPE col_var_type IS TABLE OF VARCHAR2( 100 )  
                                                    INDEX BY PLS_INTEGER;  
 
    TYPE col_date_type IS TABLE OF DATE  
                                                      INDEX BY PLS_INTEGER;  
 
    empno_tab          col_num_type;  
    ename_tab          col_var_type;