说明:以下内容整理自网络
-----------------------------------------
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. |