批量绑定(bulkbinds):FOR循环与FORALL的性能比较
通常在SQL语句中给PL/SQL变量赋值叫做绑定（Binding）,一次绑定一个完整的集合称为批量绑定（Bulk Binding）。
批量绑定（Bulk binds）可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.
批量绑定（Bulk binds）包括：
(i) Input collections, use the FORALL statement，一般用来改善DML（INSERT、UPDATE和DELETE) 操作的性能
(ii) Output collections, use BULK COLLECT clause，一般用来提高查询（SELECT）的性能
FORALL的语法如下：
FORALL index IN lower_bound..upper_bound sql_statement;
下面是一个简单测试，用以说明FORALL与FOR循环的性能差异。
SQL drop table blktest;
Table dropped.
Elapsed: 00:00:00.13
SQL
SQL CREATE TABLE blktest (num NUMBER(20), name varchar2(50));
Table created.
Elapsed: 00:00:00.08
SQL
SQL CREATE OR REPLACE PROCEDURE bulktest
2
IS
3
TYPE numtab IS TABLE OF NUMBER (20)
4
INDEX BY BINARY_INTEGER;
5
6
TYPE nametab IS TABLE OF VARCHAR2 (50)
7
INDEX BY BINARY_INTEGER;
8
9
pnums
numtab;
10
pnames
nametab;
11
t1
NUMBER;
12
t2
NUMBER;
13
t3
NUMBER;
14
BEGIN
15
FOR j IN 1 .. 1000000
16
LOOP
17
pnums (j)
:= j;
18
pnames (j)
:= 'Seq No. ' || TO_CHAR (j);
19
END LOOP;
20
21
SELECT DBMS_UTILITY.get_time
22
INTO t1
23
FROM DUAL;
24
25
FOR i IN 1 .. 1000000
26
LOOP
27
INSERT INTO blktest
28
VALUES (pnums (i), pnames (i));
29
END LOOP;
30
31
SELECT DBMS_UTILITY.get_time
32
INTO t2
33
FROM DUAL;
34
35
FORALL i IN 1 .. 1000000
36
INSERT INTO blktest
37
VALUES (pnums (i), pnames (i));
38
39
SELECT DBMS_UTILITY.get_time
40
INTO t3
41
FROM DUAL;
42
43
DBMS_OUTPUT.put_line ('Execution Time (hsecs)');
44
DBMS_OUTPUT.put_line ('---------------------');
45
DBMS_OUTPUT.put_line ('FOR loop: ' || TO_CHAR (t2 - t1));
46
DBMS_OUTPUT.put_line ('FORALL:
' || TO_CHAR (t3 - t2));
47
END;
48
/
Procedure created.
Elapsed: 00:00:01.46
SQL exec bulktest;
Execution Time (hsecs)
---------------------
FOR loop: 30361
FORALL:
4792
PL/SQL procedure successfully completed.
Elapsed: 00:06:32.92
我们可以看到FORALL较FOR循环性能大大提高。