oracle数据库里的三种循环（oracle批量绑定bulkbinds）

因为最近在做存储过程优化，碰到一个update语句的问题，有大佬建议用批量绑定的方式来做优化，所以研究了下这块，并简单做了个实验。

通常在SQL语句中给PL/SQL变量赋值叫做绑定（Binding）,一次绑定一个完整的集合称为批量绑定（Bulk Binding）。

批量绑定(Bulk Binding)通过一次绑定一个完整的集合，然后在PL/SQL块内部按照数组进行数据处理来加快SQL语句的效率。

通过两个DML语句：Bulk Collect和ForAll来实现。

Bulk Collect—用来提高查询(select)的性能

ForAll—用来提高(Insert,Delete,Update)的性能。

批量绑定（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循环的性能差异:

set linesize 1000; set serveroutput on; drop table blktest; CREATE TABLE blktest (num NUMBER(20), name varchar2(50));

CREATE OR REPLACE PROCEDURE bulktest IS TYPE numtab IS TABLE OF NUMBER (20) INDEX BY BINARY_INTEGER; TYPE nametab IS TABLE OF VARCHAR2 (50) INDEX BY BINARY_INTEGER; pnums numtab; pnames nametab; t1 NUMBER; t2 NUMBER; t3 NUMBER; BEGIN FOR j IN 1 .. 1000000 LOOP pnums (j) := j; pnames (j) := 'Seq No. ' || TO_CHAR (j); END LOOP; SELECT DBMS_UTILITY.get_time INTO t1 FROM DUAL; FOR i IN 1 .. 1000000 LOOP INSERT INTO blktest VALUES (pnums (i), pnames (i)); END LOOP; SELECT DBMS_UTILITY.get_time INTO t2 FROM DUAL; FORALL i IN 1 .. 1000000 INSERT INTO blktest VALUES (pnums (i), pnames (i)); SELECT DBMS_UTILITY.get_time INTO t3 FROM DUAL; DBMS_OUTPUT.put_line ('Execution Time (hsecs)'); DBMS_OUTPUT.put_line ('---------------------'); DBMS_OUTPUT.put_line ('FOR loop: ' || TO_CHAR (t2 - t1)); DBMS_OUTPUT.put_line ('FORALL: ' || TO_CHAR (t3 - t2)); END; /

exec bulktest;

可以看到forall比for loop循环要快很多。

上面主要是通过实验来介绍下forall批量绑定，大家有空可以测试下，后面会分享批量绑定的一些改写例子，感兴趣的朋友可以关注下！