Oracle Bulk Binding

郑全

说明：以下内容整理自网络

-----------------------------------------

1. 通常在SQL语句中给PL/SQL变量赋值叫做绑定（Binding）,一次绑定一个完整的集合称为批量绑定（Bulk Binding）。

2. 批量绑定（Bulk binds）可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能. 产生的结果是在单程访问SQL Server引擎过程中执行的一组迭代，而不是SQL与PL/SQL之间的交替。这减少了两个引擎间上下文切换的额外负担。

    缺点是：如果任何数据值产生了无效的更新（UPDATE），整组迭代都将被退回。

3. 批量绑定（Bulk binds）包括：
   (1) Input collections, use the FORALL statement，一般用来改善DML（INSERT、UPDATE和DELETE) 操作的性能。
   (2) Output collections, use BULK COLLECT clause，一般用来提高查询（SELECT）的性能。

从9i开始，oracle提供批量绑定关键词forall和bulk collect通过批量绑定技术，极大地加快了数据的处理速度。在不使用批量绑定的时候，为了将嵌套表中的数据插入数据库表中，需要循环执行insert语句，而使用forall关键词可以让所有插入内容集中在一次insert中执行，从而加快了执行速度。Bulk collect子句用于取得批量数据，该子句只能用于select语句、fetch语句和DML返回子句中；而forall语句只适用于执行批量的DML操作。

 

4. 以下通过实例说明：

   1）下面咱们对比一下for与forall的效率：

a. 创建测试表：

 

[sql] view plaincopy
01.SQL> create table test(id number(10),name varchar2(80)); 
02. 
03.表已创建。 

 

b. 执行测试PL/SQL block：

 

 

[sql] view plaincopy
01.declare 
02.  type id_table_type is table of number(10) index by binary_integer; 
03.  type name_table_type is table of varchar2(80) index by binary_integer; 
04.  id_table id_table_type; 
05.  name_table name_table_type; 
06.  start_time number(10); 
07.  end_time number(10); 
08.begin 
09.  for i in 1..100000 loop 
10.    id_table(i) := i; 
11.    name_table(i) :='dylan'||i; 
12.  end loop; 
13.  delete from test; 
14.  start_time:=dbms_utility.get_time; 
15.  for i in 1..100000 loop 
16.     insert into test values(id_table(i), name_table(i)); 
17.  end loop; 
18.  end_time := dbms_utility.get_time; 
19.  dbms_output.put_line('循环cost: '||to_char((end_time-start_time)/100)); 
20.  delete from test; 
21.  start_time := dbms_utility.get_time; 
22.  forall i in 1..100000 
23.      insert into test values(id_table(i), name_table(i)); 
24.  end_time := dbms_utility.get_time; 
25.  dbms_output.put_line('批量绑定cost: '||to_char((end_time-start_time)/100)); 
26.end; 
27./ 

 c. 测试结果：

----------------------------------

循环cost: 3.8
批量绑定cost: .27

 PL/SQL 过程已成功完成。

    2）在9i中使用forall语句时，必须具有连续的元素；从10g开始，通过使用indices of子句和values of子句，可以使用不连续的集合元素，这里forall跟for不一样的是它并不是一个循环语句。从10g开始，forall的语句有三种执行语法：

      Forall index in lower_bound..upper_bound sql_statement; 其中index是隐含定义的整数变量，lower_bound和upper_bound为集合元素的上下限。

     Forall index in indices of collection [between lower_bound..upper_bound] sql_statement;其中collection为嵌套表名称，这里只取嵌套表collection中下标位于lower_bound和upper_bound之间的非空元素值。

     Forall index in values of index_collection sql_statement; 其中index_collection为存储下标的集合变量，就是说本集合变量的内容为要取的集合collection的下标，例如(2,3,5)。

      另外，为了记录forall更新的行数，特别定义了sql%bulk_rowcount，使用方法如下：
  

[sql] view plaincopy
01.declare 
02. type test_table_type is table of varchar2(100); 
03. test_table test_table_type := test_table_type('dylan2','name','asdf'); 
04.begin 
05. forall i in 1..test_table.count 
06.    update test set name = 'lucy' where name = test_table(i); 
07. dbms_output.put_line('第二个元素更新的行数：'||sql%bulk_rowcount(1)); 
08. dbms_output.put_line('第二个元素更新的行数：'||sql%bulk_rowcount(2)); 
09. dbms_output.put_line('第二个元素更新的行数：'||sql%bulk_rowcount(3)); 
10.end; 

------------------------------------------
第二个元素更新的行数：1
第二个元素更新的行数：0
第二个元素更新的行数：0

 PL/SQL 过程已成功完成。

      3）bulk collect子句的另外一个使用环境就是在DML的返回子句中，执行dml操作会改变数据库数据，为了取得dml操作改变的数据，可以使用returning子句，为了取得dml所作用的多行数据，则需要使用bulk collect子句。例如：
 

[sql] view plaincopy
01.declare 
02. type test_table_type is table of varchar2(20) index by binary_integer; 
03. test_table test_table_type; 
04.begin 
05. delete from test where name like 'dylan2222%' 
06. returning name bulk collect into test_table; 
07. for i in 1..test_table.count loop 
08.    dbms_output.put_line(test_table(i)); 
09. end loop; 
10.end; 

 

 ----------------------------
 dylan2222
 dylan22220
 dylan22221
 dylan22222
 dylan22223
 dylan22224
 dylan22225
 dylan22226
 dylan22227
 dylan22228
 dylan22229

 PL/SQL 过程已成功完成。

       4）使用SAVE EXCEPTION语句处理Forall异常：

 

[sql] view plaincopy
01.declare 
02.type NumList is table of number; 
03.num_tab NumList :=NumList(100,0,110,300,0,199,200,0,400); 
04.bulk_errors EXCEPTION; 
05.PRAGMA EXCEPTION_INIT (bulk_errors, -24381); 
06.begin 
07.   forall i in num_tab.first..num_tab.last 
08.   SAVE EXCEPTIONS 
09.   delete from orders where order_total < 500000/num_tab(i); 
10.EXCEPTION WHEN bulk_errors THEN 
11.   dbms_output.put_line('Number of errors is: '|| SQL%BULK_EXCEPTIONS.COUNT); 
12.   for j in 1..SQL%BULK_EXCEPTIONS.COUNT loop 
13.   dbms_output.put_line(to_char(SQL%BULK_EXCEPTIONS(j).error_index) 
14.   || '/' || SQLERRM(-SQL%BULK_EXCEPTIONS(j).error_code)); 
15.   end loop; 
16.end; 

 Number of errors is: 3
 2/ORA-01476: 除数为 0
 5/ORA-01476: 除数为 0
 8/ORA-01476: 除数为 0

 PL/SQL 过程已成功完成。

------------------------
present by dylan.