批量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
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:cmzcj@163.com
