批量 SQL 之 FORALL 語句

    對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: 18PL/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)。存放在%BULK_EXCEPTIONS中的值總是與最近一次FORALL語句執行的結果相關，異常的個數存放在%BULK_EXCEPTIONS的COUNT屬性中，%BULK_EXCEPTIONS有效下標索引範圍在1到%BULK_EXCEPTIONS.COUNT之間。

1、%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;   hiredate_tab   col_date_type;   v_counter      PLS_INTEGER := 0;   v_total        INTEGER := 0;   errors         EXCEPTION;                      -->聲明異常   PRAGMA EXCEPTION_INIT( errors, -24381 );BEGIN   FOR rec IN ( SELECT empno, ename, hiredate FROM emp )   -->使用for迴圈將資料填充到聯合數組   LOOP      v_counter   := v_counter + 1;      empno_tab( v_counter ) := rec.empno;      ename_tab( v_counter ) := rec.ename;      hiredate_tab( v_counter ) := rec.hiredate;   END LOOP;   empno_tab( 2 ) := NULL;                                -->對部分資料進行處理以產生異常   ename_tab( 5 ) := RPAD( ename_tab( 5 ), 15, '*' );   empno_tab( 10 ) := NULL;   FORALL i IN 1 .. empno_tab.COUNT                      -->使用forall將聯合數組中的資料插入到表tb_emp   SAVE EXCEPTIONS      INSERT INTO tb_emp      VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );   COMMIT;   SELECT COUNT( * ) INTO v_total FROM tb_emp;   DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' );EXCEPTION   WHEN errors THEN      DBMS_OUTPUT.put_line( 'There are ' || SQL%bulk_exceptions.COUNT || ' exceptions' );      FOR i IN 1 .. SQL%bulk_exceptions.COUNT            -->SQL%bulk_exceptions.COUNT記錄異常個數來控制迭代      LOOP         DBMS_OUTPUT.          put_line(                       'Record '                    || SQL%bulk_exceptions( i ).error_index                    || ' caused error '                    || i                    || ': '                    || SQL%bulk_exceptions( i ).error_code                    || ' '                    || SQLERRM( -SQL%bulk_exceptions( i ).error_code ) );   -->使用SQLERRM根據錯誤號碼拋出具體的錯誤資訊      END LOOP;END;There are 3 exceptionsRecord 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 ()PL/SQL procedure successfully completed.2、%BULK_ROWCOUNT%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的情形。DECLARE   TYPE dept_tab_type IS TABLE OF NUMBER;   dept_tab   dept_tab_type := dept_tab_type( 10, 20, 50 );    -->聲明及初始化巢狀表格BEGIN   FORALL i IN dept_tab.FIRST .. dept_tab.LAST                 -->使用FORALL更新      UPDATE emp      SET    sal          = sal * 1.10      WHERE  deptno = dept_tab( i );   -- COMMIT;   FOR i IN 1 .. dept_tab.COUNT                               -->迴圈輸出每次執行SQL語句影響的行數   LOOP      DBMS_OUTPUT.put_line( 'Dept no ' || dept_tab( i ) || ' has ' || SQL%bulk_rowcount (i) || ' rows been updated' );   END LOOP;   -- Did the 3rd UPDATE statement affect any rows?   IF SQL%bulk_rowcount (3) = 0 THEN      DBMS_OUTPUT.put_line( 'The deptno 50 has not child record' );   END IF;END;Dept no 10 has 3 rows been updatedDept no 20 has 5 rows been updatedDept no 50 has 0 rows been updatedThe deptno 50 has not child recordPL/SQL procedure successfully completed.

四、INDICES OF 選項
    INDICES OF 選項用於處理稀疏集合類型。即當集合(巢狀表格或聯合數組)中的元素被刪除之後，對稀疏集合實現迭代。

-->下面的指令碼同前面的樣本基本相似，所不同的是使用了delete方式刪除其中的部分記錄，導致集合變得稀疏。-->其次在forall子句處使用indices OF方式來控制迴圈。TRUNCATE TABLE tb_emp;DECLARE   TYPE col_num_type IS TABLE OF NUMBER                           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;   hiredate_tab   col_date_type;   v_counter      PLS_INTEGER := 0;   v_total        INTEGER := 0;BEGIN   FOR rec IN ( SELECT empno, ename, hiredate FROM emp )   LOOP      v_counter   := v_counter + 1;      empno_tab( v_counter ) := rec.empno;      ename_tab( v_counter ) := rec.ename;      hiredate_tab( v_counter ) := rec.hiredate;   END LOOP;   empno_tab.delete( 2 );       -->此處刪除了數組中的第二個元素，導致數組變為稀疏型   ename_tab.delete( 2 );   hiredate_tab.delete( 2 );   FORALL i IN indices OF empno_tab   -->此處使用了indices OF empno_tab，則所有未被delete的元素都將進入迴圈      INSERT INTO tb_emp      VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );   COMMIT;   SELECT COUNT( * ) INTO v_total FROM tb_emp;   DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' );END;13 rows were inserted to tb_empPL/SQL procedure successfully completed.

五、VALUES OF 選項
    VALUES OF選項可以指定FORALL語句中迴圈計數器的值來自於指定集合中元素的值。
    VALUES OF選項使用時有一些限制
          如果VALUES OF子句中所使用的集合是聯合數組，則必須使用PLS_INTEGER和BINARY_INTEGER進行索引
          VALUES OF 子句中所使用的元素必須是PLS_INTEGER或BINARY_INTEGER
          當VALUES OF 子句所引用的集合為空白，則FORALL語句會導致異常               

TRUNCATE TABLE tb_emp;CREATE TABLE tb_emp_ins_log AS                    -->建立一張與tb_emp結構類似的表tb_emp_ins_log   SELECT *   FROM   tb_emp   WHERE  1 = 0;ALTER TABLE tb_emp_ins_log MODIFY(ename VARCHAR2(50));   -->修改列ename的長度DECLARE   TYPE col_num_type IS TABLE OF tb_emp.empno%TYPE                           INDEX BY PLS_INTEGER;   TYPE col_var_type IS TABLE OF VARCHAR2( 100 )                           INDEX BY PLS_INTEGER;   TYPE col_date_type IS TABLE OF tb_emp.hiredate%TYPE                            INDEX BY PLS_INTEGER;   TYPE ins_log_type IS TABLE OF PLS_INTEGER         -->此處較之前的樣本多聲明了一個聯合數組                           INDEX BY PLS_INTEGER;     -->用於填充異常記錄的元素值   empno_tab      col_num_type;   ename_tab      col_var_type;   hiredate_tab   col_date_type;   ins_log_tab    ins_log_type;   v_counter      PLS_INTEGER := 0;   v_total        INTEGER := 0;   errors         EXCEPTION;   PRAGMA EXCEPTION_INIT( errors, -24381 );BEGIN   FOR rec IN ( SELECT empno, ename, hiredate FROM emp )   LOOP      v_counter   := v_counter + 1;      empno_tab( v_counter ) := rec.empno;      ename_tab( v_counter ) := rec.ename;      hiredate_tab( v_counter ) := rec.hiredate;   END LOOP;   ename_tab( 2 ) := RPAD( ename_tab( 2 ), 15, '*' );    -->使記錄2與記錄5的ename列長度變長而產生異常   ename_tab( 5 ) := RPAD( ename_tab( 5 ), 15, '*' );   empno_tab( 6 ) := NULL;                          -->使第6條記錄的empno為NULL值，由於表tb_emp的empno不允許為NULL而產生異常   FORALL i IN 1 .. empno_tab.COUNT   SAVE EXCEPTIONS      INSERT INTO tb_emp      VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );   COMMIT;EXCEPTION   WHEN errors THEN      FOR i IN 1 .. SQL%bulk_exceptions.COUNT      LOOP         ins_log_tab( i ) := SQL%bulk_exceptions( i ).error_index;   -->異常記錄的索引值將填充ins_log_type聯合數組      END LOOP;                                    -->此處的結果是ins_log_tab(1)=2,  ins_log_tab(2)=5,  ins_log_tab(2)=6      FORALL i IN VALUES OF ins_log_tab   -->使用VALUES OF子句為ins_log_type聯合數組中的元素值         INSERT INTO tb_emp_ins_log         VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );  -->因此values中的i分別為2和5      COMMIT;END;PL/SQL procedure successfully completed.-->異常的記錄被插入到表tb_emp_ins_logselect * from tb_emp_ins_log;     EMPNO ENAME                                              HIREDATE---------- -------------------------------------------------- ---------      7369 Henry**********                                    17-DEC-80      7566 JONES**********                                    02-APR-81           MARTIN                                             28-SEP-81

六、INDICES OF 與 VALUES OF 的綜合運用

-->下面的例子來自Oraclehttp://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm-- Create empty tables to hold order detailsCREATE TABLE valid_orders(   cust_name   VARCHAR2( 32 )  ,amount      NUMBER( 10, 2 ));CREATE TABLE big_orders AS   SELECT *   FROM   valid_orders   WHERE  1 = 0;CREATE TABLE rejected_orders AS   SELECT *   FROM   valid_orders   WHERE  1 = 0;DECLARE   -- Make collections to hold a set of customer names and order amounts.   SUBTYPE cust_name IS valid_orders.cust_name%TYPE;   TYPE cust_typ IS TABLE OF cust_name;   cust_tab             cust_typ;   SUBTYPE order_amount IS valid_orders.amount%TYPE;   TYPE amount_typ IS TABLE OF NUMBER;   amount_tab           amount_typ;   -- Make other collections to point into the CUST_TAB collection.   TYPE index_pointer_t IS TABLE OF PLS_INTEGER;   big_order_tab        index_pointer_t := index_pointer_t( );   rejected_order_tab   index_pointer_t := index_pointer_t( );   PROCEDURE setup_data IS   BEGIN      -- Set up sample order data, including some invalid orders and some 'big' orders.      cust_tab    :=         cust_typ( 'Company1','Company2','Company3','Company4','Company5' );      amount_tab  :=         amount_typ( 5000.01,0,150.25,4000.00,NULL );   END;BEGIN   setup_data( );   DBMS_OUTPUT.put_line( '--- Original order data ---' );   FOR i IN 1 .. cust_tab.LAST   LOOP      DBMS_OUTPUT.put_line( 'Customer #' || i || ', ' || cust_tab( i ) || ': $' || amount_tab( i ) );   END LOOP;   -- Delete invalid orders (where amount is null or 0).   FOR i IN 1 .. cust_tab.LAST   LOOP      IF amount_tab( i ) IS NULL OR amount_tab( i ) = 0 THEN         cust_tab.delete( i );         amount_tab.delete( i );      END IF;   END LOOP;   DBMS_OUTPUT.put_line( '--- Data with invalid orders deleted ---' );   FOR i IN 1 .. cust_tab.LAST   LOOP      IF cust_tab.EXISTS( i ) THEN         DBMS_OUTPUT.put_line( 'Customer #' || i || ', ' || cust_tab( i ) || ': $' || amount_tab( i ) );      END IF;   END LOOP;   -- Because the subscripts of the collections are not consecutive, use   -- FORALL...INDICES OF to iterate through the actual subscripts,   -- rather than 1..COUNT   FORALL i IN indices OF cust_tab      INSERT INTO valid_orders( cust_name, amount )      VALUES ( cust_tab( i ), amount_tab( i ) );   -- Now process the order data differently   -- Extract 2 subsets and store each subset in a different table   setup_data( );                    -- Initialize the CUST_TAB and AMOUNT_TAB collections again.   FOR i IN cust_tab.FIRST .. cust_tab.LAST   LOOP      IF amount_tab( i ) IS NULL OR amount_tab( i ) = 0 THEN         rejected_order_tab.EXTEND;                          -- Add a new element to this collection         -- Record the subscript from the original collection         rejected_order_tab( rejected_order_tab.LAST ) := i;      END IF;      IF amount_tab( i ) > 2000 THEN         big_order_tab.EXTEND;                            -- Add a new element to this collection         -- Record the subscript from the original collection         big_order_tab( big_order_tab.LAST ) := i;      END IF;   END LOOP;   -- Now it's easy to run one DML statement on one subset of elements,   -- and another DML statement on a different subset.   FORALL i IN VALUES OF rejected_order_tab      INSERT INTO rejected_orders      VALUES ( cust_tab( i ), amount_tab( i ) );   FORALL i IN VALUES OF big_order_tab      INSERT INTO big_orders      VALUES ( cust_tab( i ), amount_tab( i ) );   COMMIT;END;--- Original order data ---Customer #1, Company1: $5000.01Customer #2, Company2: $0Customer #3, Company3: $150.25Customer #4, Company4: $4000Customer #5, Company5: $--- Data with invalid orders deleted ---Customer #1, Company1: $5000.01Customer #3, Company3: $150.25Customer #4, Company4: $4000PL/SQL procedure successfully completed.SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;Customer                         Valid order amount-------------------------------- ------------------Company1                                    5000.01Company3                                     150.25Company4                                       4000SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;Customer                         Big order amount-------------------------------- ----------------Company1                                  5000.01Company4                                     4000SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;Customer                         Rejected order amount-------------------------------- ---------------------Company2                                             0Company5--Author: Robinson Cheng--Blog : http://blog.csdn.net/robinson_0612     --上面的例子對訂單進行分類，並將其儲存到三張不同類型的表中。--1、首先定義了兩個巢狀表格cust_tab，amount_tab用於儲存未經處理資料，setup_data( )則用來初始化資料。--2、第一個for迴圈用於輸出所有的訂單，第二個for迴圈則用來將刪除amount_tab中為NULL或0值的記錄。--3、第三個for迴圈則用來輸出經過刪除之後剩餘的記錄，使用exists方法判斷。--4、使用forall子句將所有有效記錄插入到valid_orders，注意此時使用了indices of，因此此時的兩個巢狀表格已為稀疏表。--5、在這之後，使用setup_data( )重新初始化資料。--6、將無效訂單的下標記錄到rejected_order_tab巢狀表格，將amount > 2000訂單的下標記錄到big_order_tab。--7、使用VALUES OF 子句將兩個巢狀表格中對應下表的記錄插入到對應的表中。

七、更多參考

批量 SQL 之 BULK COLLECT 子句

PL/SQL 集合的初始化與賦值
PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫

啟用 AUTOTRACE 功能

函數使得索引列失效

Oracle 綁定變數窺探

Oracle 自適應共用遊標