批量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!
11 rows are inserted into tb_emp!
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
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綜合示例
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_tab2 nested_emp_type;
v_limit integer := 5;
v_counter integer := 0;
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的次數
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
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
本文原創，轉載請注明出處、作者
如有錯誤，歡迎指正
作者：czmmiao 文章出處：http://czmmiao.iteye.com/blog/1825825
![JDBC連接配接資料庫（statement）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)