批量绑定(bulk binds):FOR循环与FORALL的性能比较

核心提示：通常在SQL语句中给PL/SQL变量赋值叫做绑定（Binding）,一次绑定一个完整的集合称为批量绑定（Bulk Binding），批量绑定（Bulk binds）可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.批量绑定（Bulk binds）包括：(i) Inp

通常在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循环性能大大提高。