当Oracle运行PL/SQL时会使用两套引擎，所有procedural code由PL/SQL engine 完成，所有SQL由SQL engine处理。所以如果Oracle从一个collection中循环执行相同的DML操作，那么为了避免两套engine切换所消耗的系统资源，可以使用bulk binds来把所有的DML操作binding到一次操作中完成。这将极大提高PL/SQL的执行效率。
以下是简单的测试，用两种方式插入100000条数据，可以看到效率提高了7倍左右。
SQL
>
CREATE
TABLE
test1(
2
id
NUMBER
(
10
),
3
description
VARCHAR2
(
50
));
Table
created SQL
>
ALTER
TABLE
test1
ADD
(
2
CONSTRAINT
test1_pk
PRIMARY
KEY
(id));
Table
altered SQL
>
SET
TIMING
ON
; SQL
>
DECLARE
2
TYPE id_type
IS
TABLE
OF
test1.id
%
TYPE;
3
TYPE description_type
IS
TABLE
OF
test1.description
%
TYPE;
4
5
t_id id_type :
=
id_type();
6
t_description description_type :
=
description_type();
7
BEGIN
8
FOR
i
IN
1
..
100000
LOOP
9
t_id.extend;
10
t_description.extend;
11
12
t_id(t_id.last) :
=
i;
13
t_description(t_description.last) :
=
'
Description:
'
||
To_Char(i);
14
END
LOOP;
15
16
FOR
i
IN
t_id.first .. t_id.last LOOP
17
INSERT
INTO
test1 (id, description)
18
VALUES
(t_id(i), t_description(i));
19
END
LOOP;
20
21
COMMIT
;
22
END
;
23
/
PL
/
SQL
procedure
successfully completed Executed
in
141.233
seconds SQL
>
truncate
table
test1;
Table
truncated Executed
in
0.631
seconds SQL
>
SQL
>
DECLARE
2
TYPE id_type
IS
TABLE
OF
test1.id
%
TYPE;
3
TYPE description_type
IS
TABLE
OF
test1.description
%
TYPE;
4
5
t_id id_type :
=
id_type();
6
t_description description_type :
=
description_type();
7
BEGIN
8
FOR
i
IN
1
..
100000
LOOP
9
t_id.extend;
10
t_description.extend;
11
12
t_id(t_id.last) :
=
i;
13
t_description(t_description.last) :
=
'
Description:
'
||
To_Char(i);
14
END
LOOP;
15
16
FORALL i
IN
t_id.first .. t_id.last
17
INSERT
INTO
test1 (id, description)
18
VALUES
(t_id(i), t_description(i));
19
20
COMMIT
;
21
END
;
22
/
PL
/
SQL
procedure
successfully completed Executed
in
27.52
seconds SQL
>
select
count
(
*
)
from
test1;
COUNT
(
*
)
--
--------
100000
Executed
in
0.04
seconds
