PLSQL入门与精通（第59章：批量绑定变量提高效率）

从本章开始一个新的主题“批量绑定”。

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

在PL/SQL块中，使用数组值在循环处理中反复执行SQL语句时，如果改写成批量绑定的语法，性能会大幅度提高。

要理解批量绑定，我们需要知道plsql的2个引擎：
一个是PL/SQL引擎，一个是SQL引擎。

PL/SQL引擎：
无名块,存储程序等PL/SQL块先被送入到PL/SQL引擎，然后按顺序执行块内的处理。

SQL引擎：
如果PLSQL块内的处理遇到SQL文（INSERT、UPDATE、DELETE等）的时候，
将该SQL文及处理的值就会被发送到SQL引擎，然后再处理SQL文，
处理结束后又返回到PL/SQL引擎，再继续执行PLsqL块内剩余的处理。

也就是说，在PL/SQL块内执行SQL语句时需要2个引擎来回进行切换。
所以，如果循环中反复执行SQL语句时，2个引擎会反复进行切换，怎加了很大的开销，
结果使PL/SQL块的执行时间变长。

通常在这样的循环处理中，使用数组的值对SQL语句进行循环处理，需要反复进行变量绑定。
但是我们可以将这些数组的所有的值整个发送到SQL引擎中，然后统一执行SQL语句。
这样的话，无论循环次数有多少，引擎切换只需要一次就可以了，因此可以大幅缩短
PL/SQL块的执行时间。这就是批量绑定的概念。

批量绑定有2种处理需要批量绑定：
一个是上面解说的内容，是和DML文（INSERT，UPDATE，DELETE）进行的批量绑定。
另一个是SELECT INTO以及明示光标的FETCH INTO的查询的时候的批量绑定。
SELECT INTO和FETCH INTO是将结果的一行返回给变量的，
如果想要返回多行，则需要循环处理。
但是，如果使用批量绑定，则可以通过一次操作将多行返回到数组变量中。
也就是说两个引擎的切换只需要一次。

首先我么解说一下DML的批量绑定的方法，关于SELECT和FETCH的批量绑定我们后边在介绍。

让我们看下一下的具体的例子吧：

首先，用普通的手法：使用数组的值，用循环处理进行表的INSERT处理
之后再改成批量绑定的语法进行INSERT处理。

那么先创建一个表。

SQL> CREATE TABLE TEST01 ( A NUMBER, B VARCHAR2(10));

表创建成功。

那么，开始用普通的循环处理对这个表进行INSERT处理：

SQL> DECLARE

2 //
3 --A列用的配列
4 //
5 TYPE A_TAB TYPE IS TABLE OF NUMBER --类型声明
6 INDEX BY BINARY_INTEGER;
7 A_TAB A_TAB TYPE；–用该类型的定义变量

8 //
9 --B列用配列
10 //
11 TYPE B_TAB TYPE IS TABLE OF VARCHAR2（10）–类型声明
12 INDEX BY BINARY_INTEGER;
13 B_TAB B_TAB TYPE；–该类型的变量声明
14 BEGIN
15 //
16 --执行部
17 //
18 --数组中存储值
19 /************/
20 A_TAB(1) := 10; B_TAB(1) := ‘AB’;
21 A_TAB(2) := 20; B_TAB(2) := ‘CD’;
22 //
23 --使用该数组在循环处理中进行INSERT处理
24 /**********************************/
25 FOR I IN A_TAB.FIRST…A_TAB.LAST LOOP
26 INSERT INTO TEST01 VALUES(A_TAB(I), B_TAB(I));
27 END LOOP;
28 END;
29 /

PL/SQL过程成功完成。

PL/SQL过程成功完成。

例子解说如下：
第5-7行、第11-13行分别定义A列、B列的类型和数组变量（A_TAB、B_TAB）。
在执行部的第20-21行：这2个数组变量分别存储2个值。
在第25-27行，使用这2个数组变量，通过循环处理，对TEST01表进行INSERT处理。
第27行：A_TAB.FIRST”是数组A_TAB中最小的序列值，即“1”。
同样A_TAB.LAST”是排列A_TAB最大的序列值，也就是“2”。
因此第27行也可以写以下内容：
“FOR I IN 1…2 LOOP”这是指将字母变量I以1到2的值进行循环处理的意思。

我们确认一下INSERT的结果。

SQL> SELECT * FROM TEST01;

A B

---

10 AB
20 CD

两行INSERT成功。

我们看第二种方式，批量绑定的方式：

先删除上边的例子数据：
SQL> DELETE FROM TEST01;

删除了两行。

以下是批量绑定的例子：

SQL> DECLARE
2 //
3 --A列用的配列
4 //
5 TYPE A_TAB TYPE IS TABLE OF NUMBER --类型声明
6 INDEX BY BINARY_INTEGER;
7 A A_TAB A_TAB TYPE；–该类型的变量声明

8 //
9 --B列用的配列
10 //
11 TYPE B_TAB TYPE IS TABLE OF VARCHAR2（10）–类型声明
12 INDEX BY BINARY_INTEGER;
13 B_TAB B_TAB TYPE；–该类型的变量声明

14 BEGIN
15 //
16 --执行部
17 //

18 --数组中存储值
19 /***********************/
20 A_TAB(1) := 10; B_TAB(1) := ‘AB’;
21 A_TAB(2) := 20; B_TAB(2) := ‘CD’;

22 //
23 --使用该配列进行批量绑定，再进行INSERT处理
24 //
25 FORALL I IN A_TAB.FIRST…A_TAB.LAST
26 INSERT INTO TEST01 VALUES(A_TAB(I), B_TAB(I));
27 END;
28 /

PL/SQL过程成功完成。

SQL> SELECT * FROM TEST01;

A B

---

10 AB
20 CD

下面是上述两个处理的不同之处，即循环处理和批量绑定处理，请仔细比较一下他们的差别。

22 //
23 --使用该配列在循环处理中进行INSERT
24 //
25 FOR I IN A_TAB.FIRST…A_TAB.LAST LOOP
26 INSERT INTO TEST01 VALUES(A_TAB(I), B_TAB(I));
27 END LOOP;

22 //
23–使用该配列进行批量绑定，在进行INSERT处理
24 //
25 FORALL I IN A_TAB.FIRST…A_TAB.LAST
26 INSERT INTO TEST01 VALUES(A_TAB(I), B_TAB(I));

不同点如下：

1.批量绑定用的不是“FOR”而是“FORALL”。
2.批量绑定中没有“LOOP”和“END LOOP；”（即不是循环处理）

他们差别很小。

另外我们需要注意的是，LOOP～END LOOP之间的SQL语句如果是多个的时候时，
不能用批量绑定的语法。

总结一下DML文（INSERT，UPDATE，DELETE）的批量绑定的基本结构：

FORALL 带字母变量 IN 下限值…上限值
使用数组的一个DML语句；

（※无需声明附加字符变量）

以上就是我们介绍的内容，但实际效果到底怎么样，我们下次测试一下。