批量绑定（1.1）— BULK COLLECT

在 PL/SQL 编码中，经常会从表中获取结果集，然后进行一些逻辑处理，再生成新的数据。我们想到的最直接的方法就是使用游标。从 Oracle8i 开始，出现了一个新的子句：BULK COLLECT。可以降低 SQL 引擎到 PL/SQL 引擎的上下文交换（context switch）次数，从而实现数据的高速检索。

 

1. 速度比较

 

从表中获取结果集，我们常用的方法就是使用游标循环，我们看看它的执行速度：

 

SQL> set serveroutput on

SQL> declare

  2    type tnt_ic01 is table of ic01%rowtype;

  3    nt_ic01 tnt_ic01 := tnt_ic01();

  4    n_startTime number;

  5  begin

  6    n_startTime := dbms_utility.get_time;

  7    for rec_ic01 in( select * from ic01 where rownum < 100000 ) loop

  8      nt_ic01.extend;

  9      nt_ic01( nt_ic01.last ) := rec_ic01;

 10    end loop;

 11    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 12    dbms_output.put_line( 'USED TIEM: ' || ( dbms_utility.get_time - n_startTime ) );

 13  end;

 14  /

COUNT: 99999

USED TIEM: 255

 

PL/SQL 过程已成功完成。

 

我们再看一下完成同样的功能，BULK COLLECT 的执行速度：

 

SQL> declare

  2    type tnt_ic01 is table of ic01%rowtype;

  3    nt_ic01 tnt_ic01;

  4    n_startTime number;

  5  begin

  6    n_startTime := dbms_utility.get_time;

  7    select *

  8      bulk collect into nt_ic01

  9      from ic01

 10     where rownum < 100000;

 11    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 12    dbms_output.put_line( 'USED TIEM: ' || ( dbms_utility.get_time - n_startTime ) );

 13  end;

 14  /

COUNT: 99999

USED TIEM: 159

 

PL/SQL 过程已成功完成。

 

BULK COLLECT 要比游标循环快得多。如果你的代码中有游标循环，并且你也正为这里执行缓慢而发愁，不妨试试 BULK COLLECT。

 

2. 没有 NO_DATA_FOUND 异常

 

不过要记得，SELECT ... BULK COLLECT INTO ... 不会引起 NO_DATA_FOUND 异常，虽然它的语法看起来和 SELECT ... INTO ... 类似。要判断 BULK COLLECT 是否获得数据，可以通过 COUNT 方法：

 

SQL> declare

  2    type tnt_ic01 is table of ic01%rowtype;

  3    nt_ic01 tnt_ic01;

  4    rec_ic01 ic01%rowtype;

  5  begin

  6    --1. normal select, can cause no_data_found

  7    begin

  8      select *

  9        into rec_ic01

 10        from ic01

 11       where 1 = 0;

 12    exception

 13      when no_data_found then

 14        dbms_output.put_line( 'error1: no_data_found exception accured!' );

 15    end;

 16

 17    --2. bulk collect, won't cause no_data_found

 18    select *

 19      bulk collect into nt_ic01

 20      from ic01

 21     where 1 = 0;

 22    if nt_ic01.count = 0 then

 23      dbms_output.put_line( 'error2: There is no data in nt_ic01.' );

 24    end if;

 25  end;

 26  /

error1: no_data_found exception accured!

error2: There is no data in nt_ic01.

 

PL/SQL 过程已成功完成。

 

3. 其他使用

 

除了 SELECT INTO 外，还可以在 FETCH INTO 和 RETURNING INTO 子句中使用 BULK COLLECT：

 

-- 1. fetch collect into

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is table of cur_ic01%rowtype;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14  end;

 15  /

COUNT: 9

 

PL/SQL 过程已成功完成。

 

-- 2. returning into

SQL> select * from test_return;

 

AAC001               AAC003
-------------------- ----------
1304012080949        转入人7
1304012080950        转入人8
1304012080951        转入人9
1304012080952        转入

 

SQL> declare
  2    type tnt_return is table of test_return%rowtype;
  3    nt_return tnt_return;
  4  begin
  5    update test_return
  6       set aac003 = 'yuechaotian'
  7     where rownum < 3
  8     returning aac001, aac003 bulk collect into nt_return;
  9
 10    for n_pointer in 1..nt_return.count loop
 11      dbms_output.put_line( 'AAC001=' || nt_return(n_pointer).aac001
 12                      || ' | AAC003=' || nt_return(n_pointer).aac003 );
 13    end loop;
 14  end;
 15  /
AAC001=1304012080949 | AAC003=yuechaotian
AAC001=1304012080950 | AAC003=yuechaotian

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_return;

AAC001               AAC003
-------------------- ------------
1304012080949        yuechaotian
1304012080950        yuechaotian
1304012080951        转入人9
1304012080952        转入

SQL>

 

4. 联合数组中使用 BULK COLLECT

 

前面我们举的例子，都是用的嵌套表，其实也可以使用联合数组：

 

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is table of cur_ic01%rowtype index by pls_integer;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14  end;

 15  /

COUNT: 9

 

PL/SQL 过程已成功完成。

 

BULK COLLECT 生成的集合，下表是默认从1开始的数字，步进为1，所以联合数组的索引，不能使用 varchar2 类型。如果你不小心使用了 varchar2 类型作为联合数组的索引，就会出现下面的错误：

 

ERROR 位于第 11 行:

ORA-06550: 第 11 行, 第 36 列:

PLS-00657: 实施约束: 不支持带有 VARCHAR2 关键字的关联性数组的批 SQL。

 

5. VARRAY 数组中使用 BULK COLLECT

 

而对于 VARRAY 数组，BULK COLLECT 也适用，不过定义时，其长度必须得大于或等于结果集的长度：

 

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is varray(9) of ic01%rowtype;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14  end;

 15  /

COUNT: 9

 

PL/SQL 过程已成功完成。

 

如果 VARRAY 数组长度定义得过小，则会提示错误：

 

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is varray(8) of ic01%rowtype;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14  end;

 15  /

declare

*

ERROR 位于第 1 行:

ORA-22165: 指定的索引 [9] 必须介于 [1] 到 [8] 之间

ORA-06512: 在 line 11

 

如果 VARRAY 数组长度定义得过大，没什么影响，而且也不会占用多余的内存空间：

 

SQL> declare

  2    cursor cur_ic01 is

  3      select *

  4        from ic01

  5       where rownum < 10;

  6

  7    type tnt_ic01 is varray(10) of ic01%rowtype;

  8    nt_ic01 tnt_ic01;

  9  begin

 10    open cur_ic01;

 11    fetch cur_ic01 bulk collect into nt_ic01;

 12    close cur_ic01;

 13    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 14    dbms_output.put_line( 'LIMIT: ' || nt_ic01.limit );

 15  end;

 16  /

COUNT: 9

LIMIT: 10

 

PL/SQL 过程已成功完成。

 

不过推荐首选联合数组，或是嵌套表，在这里虽然可以使用 VARRAY 数组，但显然是不太合适的，就像steven 所介绍的那样。

 

6. 动态 SQL 中使用 BULK COLLECT

 

Oracle9i 之前，BULK COLLECT 只能用在静态 SQL 中，从 Oracle9i 开始，动态 SQL 中也可以使用 BULK COLLECT：

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> declare

  2    type tnt_ic01 is table of ic01%rowtype;

  3    nt_ic01 tnt_ic01;

  4

  5    v_sql_cursor varchar2(1000);

  6  begin

  7    v_sql_cursor := 'select * from ic01 where rownum < 100000';

  8    execute immediate v_sql_cursor bulk collect into nt_ic01;

  9    dbms_output.put_line( 'COUNT: ' || nt_ic01.count );

 10  end;

 11  /

COUNT: 99999

 

PL/SQL 过程已成功完成。

 

SQL>