批量 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 循环提高性能

[sql] view plaincopyprint?

1. -->下面的示例使用了FOR循环与FORALL循环操作进行对比，使用FORALL完成同样的功能，性能明显提高  
2. CREATE TABLE t  
3. (  
4.    col_num   NUMBER  
5.   ,col_var   VARCHAR2( 10 )  
6. );  
7.   
8. DECLARE  
9.    TYPE col_num_type IS TABLE OF NUMBER            -->声明了两个联合数组  
10.                            INDEX BY PLS_INTEGER;  
11.   
12.    TYPE col_var_type IS TABLE OF VARCHAR2( 10 )  
13.                            INDEX BY PLS_INTEGER;  
14.   
15.    col_num_tab    col_num_type;  
16.    col_var_tab    col_var_type;  
17.    v_start_time   INTEGER;  
18.    v_end_time     INTEGER;  
19. BEGIN  
20.    FOR i IN 1 .. 5000                    -->使用FOR循环向数组填充元素  
21.    LOOP  
22.       col_num_tab( i ) := i;  
23.       col_var_tab( i ) := 'var_' || i;  
24.    END LOOP;  
25.   
26.    v_start_time := DBMS_UTILITY.get_time;  -->获得FOR循环向表t插入数据前的初始时间  
27.   
28.    FOR i IN 1 .. 5000                   -->使用FOR循环向表t插入数据  
29.    LOOP  
30.       INSERT INTO t  
31.       VALUES ( col_num_tab( i ), col_var_tab( i ) );  
32.    END LOOP;  
33.   
34.    v_end_time  := DBMS_UTILITY.get_time;  
35.    DBMS_OUTPUT.put_line( 'Duration of the FOR LOOP: ' || ( v_end_time - v_start_time ) );  
36.    v_start_time := DBMS_UTILITY.get_time;  
37.   
38.    FORALL i IN 1 .. 5000          -->使用FORALL循环向表t插入数据  
39.       INSERT INTO t  
40.       VALUES ( col_num_tab( i ), col_var_tab( i ) );  
41.   
42.    v_end_time  := DBMS_UTILITY.get_time;  
43.    DBMS_OUTPUT.put_line( 'Duration of the FORALL STATEMENT: ' || ( v_end_time - v_start_time ) );  
44.    COMMIT;  
45. END;  
46.   
47. Duration of the FOR LOOP: 68           -->此处的计时单位为百分之一秒，即0.68s，下同  
48. Duration of the FORALL STATEMENT: 18  
49.   
50. PL/SQL procedure successfully completed.  

-->下面的示例使用了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之间。

[sql] view plaincopyprint?

1. 1、%BULK_EXCEPTIONS的用法  
2. CREATE TABLE tb_emp AS              -->创建表tb_emp  
3.    SELECT empno, ename, hiredate  
4.    FROM   emp  
5.    WHERE  1 = 2;  
6.   
7. ALTER TABLE tb_emp MODIFY(empno NOT NULL);   -->为表添加约束  
8.   
9. DECLARE  
10.    TYPE col_num_type IS TABLE OF NUMBER            -->一共定义了3个联合数组类型  
11.                            INDEX BY PLS_INTEGER;  
12.   
13.    TYPE col_var_type IS TABLE OF VARCHAR2( 100 )  
14.                            INDEX BY PLS_INTEGER;  
15.   
16.    TYPE col_date_type IS TABLE OF DATE  
17.                             INDEX BY PLS_INTEGER;  
18.   
19.    empno_tab      col_num_type;  
20.    ename_tab      col_var_type;  
21.    hiredate_tab   col_date_type;  
22.    v_counter      PLS_INTEGER := 0;  
23.    v_total        INTEGER := 0;  
24.    errors         EXCEPTION;                      -->声明异常  
25.    PRAGMA EXCEPTION_INIT( errors, -24381 );  
26. BEGIN  
27.    FOR rec IN ( SELECT empno, ename, hiredate FROM emp )   -->使用for循环将数据填充到联合数组  
28.    LOOP  
29.       v_counter   := v_counter + 1;  
30.       empno_tab( v_counter ) := rec.empno;  
31.       ename_tab( v_counter ) := rec.ename;  
32.       hiredate_tab( v_counter ) := rec.hiredate;  
33.    END LOOP;  
34.   
35.    empno_tab( 2 ) := NULL;                                -->对部分数据进行处理以产生异常  
36.    ename_tab( 5 ) := RPAD( ename_tab( 5 ), 15, '*' );  
37.    empno_tab( 10 ) := NULL;  
38.   
39.    FORALL i IN 1 .. empno_tab.COUNT                      -->使用forall将联合数组中的数据插入到表tb_emp  
40.    SAVE EXCEPTIONS  
41.       INSERT INTO tb_emp  
42.       VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );  
43.   
44.    COMMIT;  
45.   
46.    SELECT COUNT( * ) INTO v_total FROM tb_emp;  
47.   
48.    DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' );  
49. EXCEPTION  
50.    WHEN errors THEN  
51.       DBMS_OUTPUT.put_line( 'There are ' || SQL%bulk_exceptions.COUNT || ' exceptions' );  
52.   
53.       FOR i IN 1 .. SQL%bulk_exceptions.COUNT            -->SQL%bulk_exceptions.COUNT记录异常个数来控制迭代  
54.       LOOP  
55.          DBMS_OUTPUT.  
56.           put_line(  
57.                        'Record '  
58.                     || SQL%bulk_exceptions( i ).error_index  
59.                     || ' caused error '  
60.                     || i  
61.                     || ': '  
62.                     || SQL%bulk_exceptions( i ).error_code  
63.                     || ' '  
64.                     || SQLERRM( -SQL%bulk_exceptions( i ).error_code ) );   -->使用SQLERRM根据错误号抛出具体的错误信息  
65.       END LOOP;  
66. END;  
67.   
68. There are 3 exceptions  
69. Record 2 caused error 1: 1400 ORA-01400: cannot insert NULL into ()  
70. Record 5 caused error 2: 12899 ORA-12899: value too large for column  (actual: , maximum: )  
71. Record 10 caused error 3: 1400 ORA-01400: cannot insert NULL into ()  
72.   
73. PL/SQL procedure successfully completed.  
74.   
75. 2、%BULK_ROWCOUNT  
76.     %BULK_ROWCOUNT也是专门为FORALL设计的，用于保存第i个元素第i次insert或update或delete所影响到的行数。如果第i次操作没有行被影  
77. 响，则%BULK_ROWCOUNT返回为零值。FORALL语句和%BULK_ROWCOUNT属性使用同样的下标索引。如果FORALL使用下标索引的范围在5到8的话，那么  
78. %BULK_ROWCOUNT的也是5到8。需要注意的是一般情况下，对于insert .. values而言，所影响的行数为1，即%BULK_ROWCOUNT的值为1。而对于  
79. insert .. select方式而言，%BULK_ROWCOUNT的值就有可能大于1。update与delete语句存在0，1，以及大于1的情形。  
80.   
81. DECLARE  
82.    TYPE dept_tab_type IS TABLE OF NUMBER;  
83.   
84.    dept_tab   dept_tab_type := dept_tab_type( 10, 20, 50 );    -->声明及初始化嵌套表  
85. BEGIN  
86.    FORALL i IN dept_tab.FIRST .. dept_tab.LAST                 -->使用FORALL更新  
87.       UPDATE emp  
88.       SET    sal          = sal * 1.10  
89.       WHERE  deptno = dept_tab( i );  
90.   
91.    -- COMMIT;   
92.    FOR i IN 1 .. dept_tab.COUNT                               -->循环输出每次执行SQL语句影响的行数  
93.    LOOP  
94.       DBMS_OUTPUT.put_line( 'Dept no ' || dept_tab( i ) || ' has ' || SQL%bulk_rowcount (i) || ' rows been updated' );  
95.    END LOOP;  
96.   
97.    -- Did the 3rd UPDATE statement affect any rows?  
98.    IF SQL%bulk_rowcount (3) = 0 THEN  
99.       DBMS_OUTPUT.put_line( 'The deptno 50 has not child record' );  
100.    END IF;  
101. END;  
102.   
103. Dept no 10 has 3 rows been updated  
104. Dept no 20 has 5 rows been updated  
105. Dept no 50 has 0 rows been updated  
106. The deptno 50 has not child record  
107.   
108. PL/SQL procedure successfully completed.  

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 选项用于处理稀疏集合类型。即当集合(嵌套表或联合数组)中的元素被删除之后，对稀疏集合实现迭代。

[sql] view plaincopyprint?

1. -->下面的脚本同前面的示例基本相似，所不同的是使用了delete方式删除其中的部分记录，导致集合变得稀疏。  
2. -->其次在forall子句处使用indices OF方式来控制循环。         
3. TRUNCATE TABLE tb_emp;  
4.   
5. DECLARE  
6.    TYPE col_num_type IS TABLE OF NUMBER  
7.                            INDEX BY PLS_INTEGER;  
8.   
9.    TYPE col_var_type IS TABLE OF VARCHAR2( 100 )  
10.                            INDEX BY PLS_INTEGER;  
11.   
12.    TYPE col_date_type IS TABLE OF DATE  
13.                             INDEX BY PLS_INTEGER;  
14.   
15.    empno_tab      col_num_type;  
16.    ename_tab      col_var_type;  
17.    hiredate_tab   col_date_type;  
18.    v_counter      PLS_INTEGER := 0;  
19.    v_total        INTEGER := 0;  
20. BEGIN  
21.    FOR rec IN ( SELECT empno, ename, hiredate FROM emp )  
22.    LOOP  
23.       v_counter   := v_counter + 1;  
24.       empno_tab( v_counter ) := rec.empno;  
25.       ename_tab( v_counter ) := rec.ename;  
26.       hiredate_tab( v_counter ) := rec.hiredate;  
27.    END LOOP;  
28.   
29.    empno_tab.delete( 2 );       -->此处删除了数组中的第二个元素，导致数组变为稀疏型  
30.    ename_tab.delete( 2 );  
31.    hiredate_tab.delete( 2 );  
32.   
33.    FORALL i IN indices OF empno_tab   -->此处使用了indices OF empno_tab，则所有未被delete的元素都将进入循环  
34.       INSERT INTO tb_emp  
35.       VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );  
36.   
37.    COMMIT;  
38.   
39.    SELECT COUNT( * ) INTO v_total FROM tb_emp;  
40.   
41.    DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' );  
42. END;  
43.   
44. 13 rows were inserted to tb_emp  
45.   
46. PL/SQL procedure successfully completed.  

-->下面的脚本同前面的示例基本相似，所不同的是使用了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语句会导致异常               

[sql] view plaincopyprint?

1. TRUNCATE TABLE tb_emp;  
2.                   
3. CREATE TABLE tb_emp_ins_log AS                    -->创建一张与tb_emp结构类似的表tb_emp_ins_log  
4.    SELECT *  
5.    FROM   tb_emp  
6.    WHERE  1 = 0;  
7.   
8. ALTER TABLE tb_emp_ins_log MODIFY(ename VARCHAR2(50));   -->修改列ename的长度  
9.   
10. DECLARE  
11.    TYPE col_num_type IS TABLE OF tb_emp.empno%TYPE  
12.                            INDEX BY PLS_INTEGER;  
13.   
14.    TYPE col_var_type IS TABLE OF VARCHAR2( 100 )  
15.                            INDEX BY PLS_INTEGER;  
16.   
17.    TYPE col_date_type IS TABLE OF tb_emp.hiredate%TYPE  
18.                             INDEX BY PLS_INTEGER;  
19.   
20.    TYPE ins_log_type IS TABLE OF PLS_INTEGER         -->此处较之前的示例多声明了一个联合数组  
21.                            INDEX BY PLS_INTEGER;     -->用于填充异常记录的元素值  
22.   
23.    empno_tab      col_num_type;  
24.    ename_tab      col_var_type;  
25.    hiredate_tab   col_date_type;  
26.    ins_log_tab    ins_log_type;  
27.    v_counter      PLS_INTEGER := 0;  
28.    v_total        INTEGER := 0;  
29.    errors         EXCEPTION;  
30.    PRAGMA EXCEPTION_INIT( errors, -24381 );  
31. BEGIN  
32.    FOR rec IN ( SELECT empno, ename, hiredate FROM emp )  
33.    LOOP  
34.       v_counter   := v_counter + 1;  
35.       empno_tab( v_counter ) := rec.empno;  
36.       ename_tab( v_counter ) := rec.ename;  
37.       hiredate_tab( v_counter ) := rec.hiredate;  
38.    END LOOP;  
39.   
40.    ename_tab( 2 ) := RPAD( ename_tab( 2 ), 15, '*' );    -->使记录2与记录5的ename列长度变长而产生异常  
41.    ename_tab( 5 ) := RPAD( ename_tab( 5 ), 15, '*' );  
42.    empno_tab( 6 ) := NULL;                          -->使第6条记录的empno为NULL值，由于表tb_emp的empno不允许为NULL而产生异常  
43.   
44.    FORALL i IN 1 .. empno_tab.COUNT  
45.    SAVE EXCEPTIONS  
46.       INSERT INTO tb_emp  
47.       VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );  
48.   
49.    COMMIT;  
50. EXCEPTION  
51.    WHEN errors THEN  
52.       FOR i IN 1 .. SQL%bulk_exceptions.COUNT  
53.       LOOP  
54.          ins_log_tab( i ) := SQL%bulk_exceptions( i ).error_index;   -->异常记录的索引值将填充ins_log_type联合数组  
55.       END LOOP;                                    -->此处的结果是ins_log_tab(1)=2,  ins_log_tab(2)=5,  ins_log_tab(2)=6  
56.   
57.       FORALL i IN VALUES OF ins_log_tab   -->使用VALUES OF子句为ins_log_type联合数组中的元素值  
58.          INSERT INTO tb_emp_ins_log  
59.          VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );  -->因此values中的i分别为2和5  
60.   
61.       COMMIT;  
62. END;  
63.   
64. PL/SQL procedure successfully completed.  
65.   
66. -->异常的记录被插入到表tb_emp_ins_log   
67. select * from tb_emp_ins_log;  
68.   
69.      EMPNO ENAME                                              HIREDATE  
70. ---------- -------------------------------------------------- ---------  
71.       7369 Henry**********                                    17-DEC-80  
72.       7566 JONES**********                                    02-APR-81  
73.            MARTIN                                             28-SEP-81  

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 的综合运用

[sql] view plaincopyprint?

1. -->下面的例子来自Oracle     http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm  
2. -- Create empty tables to hold order details  
3.   
4. CREATE TABLE valid_orders  
5. (  
6.    cust_name   VARCHAR2( 32 )  
7.   ,amount      NUMBER( 10, 2 )  
8. );  
9.   
10. CREATE TABLE big_orders AS  
11.    SELECT *  
12.    FROM   valid_orders  
13.    WHERE  1 = 0;  
14.   
15. CREATE TABLE rejected_orders AS  
16.    SELECT *  
17.    FROM   valid_orders  
18.    WHERE  1 = 0;  
19.   
20. DECLARE  
21.    -- Make collections to hold a set of customer names and order amounts.  
22.    SUBTYPE cust_name IS valid_orders.cust_name%TYPE;  
23.   
24.    TYPE cust_typ IS TABLE OF cust_name;  
25.   
26.    cust_tab             cust_typ;  
27.   
28.    SUBTYPE order_amount IS valid_orders.amount%TYPE;  
29.   
30.    TYPE amount_typ IS TABLE OF NUMBER;  
31.   
32.    amount_tab           amount_typ;  
33.   
34.    -- Make other collections to point into the CUST_TAB collection.  
35.    TYPE index_pointer_t IS TABLE OF PLS_INTEGER;  
36.   
37.    big_order_tab        index_pointer_t := index_pointer_t( );  
38.    rejected_order_tab   index_pointer_t := index_pointer_t( );  
39.   
40.    PROCEDURE setup_data IS  
41.    BEGIN  
42.       -- Set up sample order data, including some invalid orders and some 'big' orders.  
43.       cust_tab    :=  
44.          cust_typ( 'Company1','Company2','Company3','Company4','Company5' );  
45.       amount_tab  :=  
46.          amount_typ( 5000.01,0,150.25,4000.00,NULL );  
47.    END;  
48. BEGIN  
49.    setup_data( );  
50.    DBMS_OUTPUT.put_line( '--- Original order data ---' );  
51.   
52.    FOR i IN 1 .. cust_tab.LAST  
53.    LOOP  
54.       DBMS_OUTPUT.put_line( 'Customer #' || i || ', ' || cust_tab( i ) || ': $' || amount_tab( i ) );  
55.    END LOOP;  
56.   
57.    -- Delete invalid orders (where amount is null or 0).  
58.    FOR i IN 1 .. cust_tab.LAST  
59.    LOOP  
60.       IF amount_tab( i ) IS NULL OR amount_tab( i ) = 0 THEN  
61.          cust_tab.delete( i );  
62.          amount_tab.delete( i );  
63.       END IF;  
64.    END LOOP;  
65.   
66.    DBMS_OUTPUT.put_line( '--- Data with invalid orders deleted ---' );  
67.   
68.    FOR i IN 1 .. cust_tab.LAST  
69.    LOOP  
70.       IF cust_tab.EXISTS( i ) THEN  
71.          DBMS_OUTPUT.put_line( 'Customer #' || i || ', ' || cust_tab( i ) || ': $' || amount_tab( i ) );  
72.       END IF;  
73.    END LOOP;  
74.   
75.    -- Because the subscripts of the collections are not consecutive, use  
76.    -- FORALL...INDICES OF to iterate through the actual subscripts,  
77.    -- rather than 1..COUNT   
78.    FORALL i IN indices OF cust_tab  
79.       INSERT INTO valid_orders( cust_name, amount )  
80.       VALUES ( cust_tab( i ), amount_tab( i ) );  
81.   
82.    -- Now process the order data differently  
83.    -- Extract 2 subsets and store each subset in a different table  
84.    setup_data( );                    -- Initialize the CUST_TAB and AMOUNT_TAB collections again.  
85.   
86.    FOR i IN cust_tab.FIRST .. cust_tab.LAST  
87.    LOOP  
88.       IF amount_tab( i ) IS NULL OR amount_tab( i ) = 0 THEN  
89.          rejected_order_tab.EXTEND;                          -- Add a new element to this collection  
90.          -- Record the subscript from the original collection  
91.          rejected_order_tab( rejected_order_tab.LAST ) := i;  
92.       END IF;  
93.   
94.       IF amount_tab( i ) > 2000 THEN  
95.          big_order_tab.EXTEND;                            -- Add a new element to this collection  
96.          -- Record the subscript from the original collection  
97.          big_order_tab( big_order_tab.LAST ) := i;  
98.       END IF;  
99.    END LOOP;  
100.   
101.    -- Now it's easy to run one DML statement on one subset of elements,  
102.    -- and another DML statement on a different subset.  
103.    FORALL i IN VALUES OF rejected_order_tab  
104.       INSERT INTO rejected_orders  
105.       VALUES ( cust_tab( i ), amount_tab( i ) );  
106.   
107.    FORALL i IN VALUES OF big_order_tab  
108.       INSERT INTO big_orders  
109.       VALUES ( cust_tab( i ), amount_tab( i ) );  
110.   
111.    COMMIT;  
112. END;  
113.   
114. --- Original order data ---   
115. Customer #1, Company1: $5000.01  
116. Customer #2, Company2: $0  
117. Customer #3, Company3: $150.25  
118. Customer #4, Company4: $4000  
119. Customer #5, Company5: $  
120. --- Data with invalid orders deleted ---   
121. Customer #1, Company1: $5000.01  
122. Customer #3, Company3: $150.25  
123. Customer #4, Company4: $4000  
124.   
125. PL/SQL procedure successfully completed.  
126.   
127. SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;  
128.   
129. Customer                         Valid order amount  
130. -------------------------------- ------------------  
131. Company1                                    5000.01  
132. Company3                                     150.25  
133. Company4                                       4000  
134.   
135. SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;  
136.   
137. Customer                         Big order amount  
138. -------------------------------- ----------------  
139. Company1                                  5000.01  
140. Company4                                     4000  
141.   
142. SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;  
143.   
144. Customer                         Rejected order amount  
145. -------------------------------- ---------------------  
146. Company2                                             0  
147. Company5  
148.   
149. --Author: Robinson Cheng   
150. --Blog : http://blog.csdn.net/robinson_0612  
151.        
152. --  上面的例子对订单进行分类，并将其保存到三张不同类型的表中。   
153. --  1、首先定义了两个嵌套表cust_tab，amount_tab用于保存原始数据，setup_data( )则用来初始化数据。  
154. --  2、第一个for循环用于输出所有的订单，第二个for循环则用来将删除amount_tab中为NULL或0值的记录。  
155. --  3、第三个for循环则用来输出经过删除之后剩余的记录，使用exists方法判断。  
156. --  4、使用forall子句将所有有效的记录插入到valid_orders，注意此时使用了indices of，因此此时的两个嵌套表已为稀疏表。  
157. --  5、在这之后，使用setup_data( )重新初始化数据。  
158. --  6、将无效订单的下标记录到rejected_order_tab嵌套表，将amount > 2000订单的下标记录到big_order_tab。  
159. --  7、使用VALUES OF 子句将两个嵌套表中对应下表的记录插入到对应的表中。