Oracle PL/SQL开发基础（第二十弹：批量绑定和BULK COLLECT）

时间：2021-03-09 11:42:57

批量绑定

在编写PL/SQL代码时，PL/SQL引擎通常会与SQL引擎进行交互，比如将SQL语句发送到PL/SQL引擎，SQL引擎在执行了SQL语句后，会返回一些数据给PL/SQL引擎。

频繁的交互会大大降低效率，例如下面的示例将在一个循环中向SQL引擎发送多条DELETE指令，会导致效率非常低下。

DECLARE
   TYPE dept_type IS VARRAY (20) OF NUMBER;  --定义嵌套表变量 
   depts dept_type:=dept_type (10, 30, 70);  --实例化嵌套表，分配3个元素
BEGIN
FOR i IN depts.FIRST..depts.LAST --循环嵌套表元素 
 LOOP
DELETE FROM emp
WHERE deptno = depts (i);       --向SQL引擎发送SQL命令执行SQL操作
END LOOP;
END;

可以看到要删除emp表中特定部门编号的记录，代码通过循环依次向SQL引擎发送SQL语句，这样的操作方式会降低执行的性能，特别是当元素个数比较多的时候。
如果使用PL/SQL的批量绑定，将一次性向SQL引擎发送所有的SQL语句，会显著的提高执行的性能。在示例中，一次一个depts集合元素的传递是造成性能降低的关键点，可以批量传递集合中的元素来执行，这个过程称为批量绑定。
要使用批量绑定，可以使用FORALL语句，该语句将输入的集合送到SQL引擎之前，通知PL/SQL引擎将集合中的所有元素进行批量绑定。

FORALL语句并不是一个FOR循环，它仅包含了一个重复的步骤，用来通知PL/SQL引擎在将SQL语句发送给SQL引擎之前，将集合中的所有元素批量绑定，以便一次性将多个绑定到SQL语句的变量一次性发送给SQL引擎。

FORALL使用如下：

FORALL index IN lower_bound..upper_bound
sql_statement;

语法的含义如下：
- index：只能在FORALL语句块内作为集合下标使用。
- SQL语句：必须是使用了集合元素的INSERT、UPDATE或DELETE语句。
- bound：有效范围是连续的索引号。在这个范围内，SQL引擎为每个索引号执行一次SQL语句。

将上面的示例通过FORALL改写：

DECLARE
   TYPE dept_type IS VARRAY (20) OF NUMBER;                  --定义嵌套表变量
   depts   dept_type := dept_type (10, 30, 70);   --实例化嵌套表，分配3个元素 
BEGIN
 FORALL i IN depts.FIRST .. depts.LAST --循环嵌套表元素
DELETE FROM emp
WHERE deptno = depts (i);       --向SQL引擎发送SQL命令执行SQL操作
   FOR i IN 1..depts.COUNT LOOP  
   DBMS_OUTPUT.put_line (   '部门编号'
                         || depts (i)
                         || '的删除操作受影响的行为：'
                         || SQL%BULK_ROWCOUNT (i)
                        );
END LOOP;
END;

由于FORALL不是一个循环语句，因此不需要使用LOOP和END LOOP。在代码中，还使用了SQL游标变量来获取当前批量绑定操作中，特定的集合元素所影响的行。
由于批量绑定是一次性对多个SQL语句进行操作，因此要获取当前哪个SQL语句执行后受影响的行数信息，可以使用%BULK_ROWCOUNT，该变量接收一个集合元素的索引值，在示例中使用了循环语句依次获取受影响的行结果。

BULK COLLECT

FORALL关键字用来批量绑定多个集合的变量到SQL引擎，与之相反的是，BULK COLLECT关键字则可以批量地从SQL引擎中批量接收数据到一个集合，可以在SELECT-INTO，FETCH-INTO和RETURNING-INTO自居中使用BULK COLLECT。

SQL引擎能批量绑定出现在INTO列表后的所有集合，对应的字段可以保存为标量类型或复合类型的值，其中也包括对象类型。SQL引擎会初始化和扩展集合（但是，它不能把变长数组的长度扩大到超过变长数组的最大长度值），然后从索引1开始，连续地插入元素并覆盖先前已存在的元素。

如：

DECLARE
TYPE numtab IS TABLE OF emp.empno%TYPE;     --员工编号嵌套表
TYPE nametab IS TABLE OF emp.ename%TYPE;    --员工名称嵌套表
   nums    numtab;                             --定义嵌套表变量，不需要初始化 
   names   nametab;
BEGIN
SELECT empno, ename
   BULK COLLECT INTO nums, names
     FROM emp;                                --从emp表中查出员工编号和名称，批量插入到集合
FOR i IN 1 .. nums.COUNT                   --循环显示集合内容
LOOP
      DBMS_OUTPUT.put ('num(' || i || ')=' || nums (i)||'   ');
      DBMS_OUTPUT.put_line ('names(' || i || ')=' || names (i));      
END LOOP;
END;

使用BULK COLLECT有一些限制：
- 不能对使用字符串类型作为键的索引表使用BULK COLLECT子句。
- 只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这种特性的错误。
- BULK COLLECTINTO的目标对象必须是集合类型。
标签：

相关文章