【IT168 技术文档】1.案例起因

    公司有两套系统，一套是供应链系统，另一套是物流系统。这两套系统分别是由两家供应商提供的。基于的数据库平台都是oracle 9i。它们之间通过一个独立的小程序进行数据交互工作，该小程序由我们项目组自行开发，我们称之为数据交互程序，该数据交互程序每隔一定时间启动。物流系统运行过程中，会不断的把它内部产生的数据插入到一个接口表里，而只要该数据交互程序启动，就会读取该接口表里的数据并进行处理，从而修改供应链系统里的数据。处理完毕以后，将处理成功的数据插入一个历史表并更新接口表里该记录的状态为成功，而没有成功的也要更新接口表里该记录的状态为失败。

    刚开始，数据量还不是很大，所以数据交互程序运行一次所花的时间并不多。但是运行了半年以后，该数据交互程序运行越来越慢，发展到后来运行一次需要30分钟。我仔细检查了该交互程序以后，发现该程序主要调用一个后台存储过程，该存储过程逻辑上说比较简单，只不过是打开一个cursor，获取接口表里所有符合条件的数据，然后对每一条数据都调用另外一个存储过程进行处理，处理成功则插入历史表并设置成功标记，否则设置失败标记。于是，想到可以使用批量绑定改写一下该存储过程，看看效果。改写完以后，发现运行效率大大提高，现在运行一次只需要10分钟。下面就详细介绍一下如何使用批量绑定。

    2.批量绑定的含义

    oracle从8i开始引入了一个新的PL/SQL的技术：批量绑定(BULK BINDING)。原来需要很多单独的重复的SELECT、INSERT、UPDATE或DELETE的语句才能完成的功能，包括从表里获取数据和把数据插入表等，现在可以成批的一次性完成了，从而极大的提高了性能。

    当运行PL/SQL时，由PL/SQL引擎将代码中的SQL语句传递给SQL引擎，从而由SQL引擎解析并执行SQL，然后再由SQL引擎将数据结果返回给PL/SQL引擎。PL/SQL引擎和SQL引擎之间的数据交互叫做上下文切换。而批量绑定能够提高性能的本质就在于它极大降低了上下文切换的次数。

    为了实现批量绑定，oracle提供了两种方法：

    使用FORALL为INSERT、UPDATE或DELETE提供批量绑定；
    使用BULK COLLECT为SELECT提供批量绑定。

    BULK COLLECT在SQL引擎将数据结果返回给PL/SQL引擎前，把输出结果批量绑定到一个结果集里。然后再一次性的传递给PL/SQL引擎。可以在SELECT INTO、FETCH INTO和RETURNING INTO里使用。语法结构为：

    [SELECT INTO|FETCH INTO|RETURNING INTO]
    BULK COLLECT INTO collection_name[,collection_name]...

    FORALL则是在PL/SQL引擎将DML的数据传递给SQL引擎之前，把输入数据批量绑定到一个结果集里。然后再一次性的传递给SQL引擎。语法结构为：

    FORALL index IN lower_bound..upper_boundsql_statement;

    尽管FORALL含有循环的机制，但是它并不是一个FOR循环。SQL引擎对每一个index都会执行一遍sql_statement。顺带提一下，9i下对于FORALL的批量绑定来说有个问题，就是如果集合的lower_bound和upper_bound之间如果不是连续的，而是存在跳号则会报如下的错：

    ORA-22160: element at index [N] does not exist

    比如某个集合共有4个元素，其下标分别为1、2、4、5，则如果使用FORALL将报22160的错误。这个问题在10g中通过引入INDICES OF和VALUES OF，从而得到了很好的解决。INDICES OF用在被绑定的集合是不连续的情况下。比如：

FORALL index IN INDICES OF scatter_array1
INSERT INTO t(col1,col2...colN)
VALUES (scatter_array1(index),scatter_arrary2(index)...scatter_arraryN(index));

    这样，就不用担心scatter_array1里的下标是否是连续的了。

    VALUES OF用在被绑定的集合是不连续的，但是在sql_statement里所引用的集合则是连续的。可以理解为使用一个不连续的指针数组，该数组里的值都是指针，这些指针指向一个连续的数组，通过将这些指针作为该连续数组里的元素下标，就可以获得该连续数组里的指定元素的值了。比如：

FORALL index IN INDICES OF scatter_pointer_array
INSERT INTO t(col1,col2...colN)
VALUES (continue_array1(index),continue_arrary2(index)...continue_arraryN(index));