oracle批量绑定的概念

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

declare

  type id_table_type is table of number ( 6 ) index by binary_integer ;

  type name_table_type is table of varchar2 ( 10 ) index by binary_integer ;

 id_table id_table_type;

 name_table name_table_type;

 start_time number ( 10 );

 end_time number ( 10 );

begin

  for i in 1 .. 100000 loop

    id_table(i) := i;

    name_table(i) := 'name' ||i;

  end loop ;

  delete from demo;

 start_time := dbms_utility.get_time;

  for i in 1 .. 100000 loop

    insert into demo values (id_table(i),name_table(i));

  end loop ;

 end_time := dbms_utility.get_time;

 dbms_output.put_line( ' 花费时间： ' ||to_char((end_time-start_time)/ 100 ));

 

  delete from demo;

 start_time := dbms_utility.get_time;

  forall i in 1 .. 100000

    insert into demo values (id_table(i),name_table(i));

 end_time := dbms_utility.get_time;

 dbms_output.put_line( ' 花费时间： ' ||to_char((end_time-start_time)/ 100 ));

end ;

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

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

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

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

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

declare

  type test_table_type is table of varchar2 ( 100 );

 test_table test_table_type := test_table_type( 'name2' , 'name' , 'asdf' );

begin

  forall i in 1 ..test_table. count

    update demo set name = 'zhanglei' where name = test_table(i);

 dbms_output.put_line( ' 第二个元素更新的行数： ' || sql % bulk_rowcount ( 1 ));

dbms_output.put_line( ' 第二个元素更新的行数： ' || sql % bulk_rowcount ( 2 ));

 dbms_output.put_line( ' 第二个元素更新的行数： ' || sql % bulk_rowcount ( 3 ));

end ;

bulk collect子句用于取得批量数据，它只适用于select into语句，fetch into语句和dml返回子句。语法为 select * bulk collect into collection_name …其中collection_name为集合名称。

declare

  type test_table_type is table of varchar2 ( 20 ) index by binary_integer ;

 test_table test_table_type;

begin

  select name bulk collect into test_table from demo where name = 'zhanglei' ;

  for i in 1 ..test_table. count loop

    dbms_output.put_line(test_table(i));

  end loop ;

end ;

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

declare

  type test_table_type is table of varchar2 ( 20 ) index by binary_integer ;

 test_table test_table_type;

begin

  delete from demo where name = 'zhanglei'

  returning name bulk collect into test_table;

  for i in 1 ..test_table. count loop

    dbms_output.put_line(test_table(i));

  end loop ;

end ;