在 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> |
如果你的工作，机器也可以做。那总有一天，你也要变成机器。
