概述

因为最近在做存储过程优化，碰到一个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循环的性能差异:

1、创建测试表

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

2、创建存储过程

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;
/

3、执行存储过程

exec bulktest;

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

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