Oracle数据库之FORALL与BULK COLLECT语句

我们再来看一下PL/SQL块的执行过程：当PL/SQL运行时引擎处理一块代码时，它使用PL/SQL引擎来执行过程化的代码，而将SQL语句发送给SQL引擎来执行；SQL引擎执行完毕后，将结果再返回给PL/SQL引擎。这种在PL/SQL引擎和SQL引擎之间的交互，称为上下文交换（context switch）。每发生一次交换，就会带来一定的额外开销。

1. FORALL，用于增强PL/SQL引擎到SQL引擎的交换。
2. BULK COLLECT，用于增强SQL引擎到PL/SQL引擎的交换。（前面我们已经介绍过了）

1. FORALL介绍

使用FORALL，可以将多个DML批量发送给SQL引擎来执行，最大限度地减少上下文交互所带来的开销。下面是 FORALL 的一个示意图：

语法：

1 FORALL index_name IN 
2     { lower_bound .. upper_bound
3     | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]
4     | VALUES OF index_collection
5     }
6  [ SAVE EXCEPTIONS ] dml_statement; 

说明：

index_name：一个无需声明的标识符，作为集合下标使用。

lower_bound .. upper_bound：数字表达式，来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次。

INDICES OF collection_name：用于指向稀疏数组的实际下标。跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值。

VALUES OF index_collection_name：把该集合中的值当作下标，且该集合值的类型只能是 PLS_INTEGER/BINARY_INTEGER。

SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

dml_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句。

2. FORALL的使用

示例所使用表结构：

1 CREATE TABLE tmp_tab(
2   id NUMBER(5),
3   name VARCHAR2(50)
4 ); 

示例1，使用FORALL批量插入、修改、删除数据：

1 --批量插入
 2 DECLARE
 3   -- 定义索引表类型
 4   TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY  BINARY_INTEGER;
 5   tb_table tb_table_type;
 6 BEGIN
 7   FOR i IN 1..100 LOOP
 8     tb_table(i).id:=i;
 9     tb_table(i).name:='NAME'||i;
10   END LOOP;
11 
12   FORALL i IN 1..tb_table.count
13     INSERT INTO  tmp_tab VALUES tb_table(i);
14 END;
 


 1 --批量修改
 2 DECLARE
 3   TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
 4   tb_table tb_table_type;
 5 BEGIN
 6   FOR i IN 1..100 LOOP
 7     tb_table(i).id:=i;
 8     tb_table(i).name:='MY_NAME_'||i;
 9   END LOOP;
10   FORALL i IN 1..tb_table.count
11     UPDATE tmp_tab t SET row = tb_table(i) WHERE t.id =tb_table(i).id;
12 END; 

--批量删除
 DECLARE
  TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
  tb_table tb_table_type;
 BEGIN
   FOR i IN 1..10 LOOP
    tb_table(i).id:=i;
    tb_table(i).name:='MY_NAME_'||i;
   END LOOP;
   FORALL i IN 1..tb_table.count
    DELETE FROM tmp_tab WHERE id =tb_table(i).id;
 END; 

示例2，使用INDICES OF子句：

1 DECLARE
 2   TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
 3   demo_table demo_table_type;
 4 BEGIN
 5   FOR i IN 1..10 LOOP
 6     demo_table(i).id:=i;
 7     demo_table(i).name:='NAME'||i;
 8   END LOOP;
 9   -- 使用集合的delete方法移除第3、6、9三个成员
10   demo_table.delete(3);
11   demo_table.delete(6);
12   demo_table.delete(9);
13   FORALL i IN INDICES OF  demo_table
14     INSERT INTO tmp_tab VALUES demo_table(i);
15 END ;

示例3，使用VALUES OF子句：

1 DECLARE
 2 TYPE index_poniter_type IS TABLE OF pls_integer;
 3   index_poniter index_poniter_type;
 4   TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
 5   demo_table demo_table_type;
 6 BEGIN 
 7   index_poniter := index_poniter_type(1,3,5,7);
 8   FOR i IN 1..10 LOOP 
 9     demo_table(i).id:=i;
10     demo_table(i).name:='NAME'||i;
11   END LOOP;
12   FORALL i IN VALUES OF index_poniter
13     INSERT INTO  tmp_tab VALUES demo_table(i);
14 END; 

3. FORALL注意事项

使用FORALL时，应该遵循如下规则：

1. FORALL语句的执行体，必须是一个单独的DML语句，比如INSERT，UPDATE或DELETE。
2. 不要显式定义index_row，它被PL/SQL引擎隐式定义为PLS_INTEGER类型，并且它的作用域也仅仅是FORALL。
3. 这个DML语句必须与一个集合的元素相关，并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。
4. lower_bound和upper_bound之间是按照步进 1 来递增的。
5. 在sql_statement中，不能单独地引用集合中的元素，只能批量地使用集合。
6. 在sql_statement中使用的集合，下标不能使用表达式。

4. BULK COLLECT介绍

BULK COLLECT子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。

通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。下面逐一描述BULK COLLECT在这几种情形下的用法。

5. BULK COLLECT的使用

5.1 在SELECT INTO中使用BULK COLLECT

示例：

1 DECLARE
 2    -- 定义记录类型
 3    TYPE emp_rec_type IS RECORD            
 4    (  
 5       empno      emp.empno%TYPE,
 6       ename      emp.ename%TYPE, 
 7       hiredate   emp.hiredate%TYPE  
 8    );  
 9    -- 定义基于记录的嵌套表
10    TYPE nested_emp_type IS TABLE OF emp_rec_type;  
11    -- 声明变量
12    emp_tab   nested_emp_type;  
13 BEGIN
14    -- 使用BULK COLLECT将所得的结果集一次性绑定到记录变量emp_tab中  
15    SELECT empno, ename, hiredate  
16    BULK   COLLECT INTO emp_tab       
17    FROM   emp;  
18 
19    FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP  
20       DBMS_OUTPUT.PUT_LINE('当前记录： '
21                 ||emp_tab(i).empno||chr(9)
22                 ||emp_tab(i).ename||chr(9)
23                 ||emp_tab(i).hiredate);  
24    END LOOP;
25 END;

说明：使用BULK COLLECT一次即可提取所有行并绑定到记录变量，这就是所谓的批量绑定。

5.2 在FETCH INTO中使用BULK COLLECT

在游标中可以使用BLUK COLLECT一次取出一个数据集合，比用游标单条取数据效率高，尤其是在网络不大好的情况下。

语法：

FETCH ... BULK COLLECT INTO ...[LIMIT row_number];

在使用BULK COLLECT子句时，对于集合类型会自动对其进行初始化以及扩展。因此如果使用BULK COLLECT子句操作集合，则无需对集合进行初始化以及扩展。由于BULK COLLECT的批量特性，如果数据量较大，而集合在此时又自动扩展，为避免过大的数据集造成性能下降，因此可以使用LIMIT子句来限制一次提取的数据量。LIMIT子句只允许出现在FETCH操作语句的批量中。

示例：

1 DECLARE  
 2    CURSOR emp_cur IS  
 3       SELECT empno, ename, hiredate FROM emp;  
 4 
 5    TYPE emp_rec_type IS RECORD  
 6    (  
 7       empno      emp.empno%TYPE,
 8       ename      emp.ename%TYPE ,
 9       hiredate   emp.hiredate%TYPE  
10    );  
11    -- 定义基于记录的嵌套表  
12    TYPE nested_emp_type IS TABLE OF emp_rec_type;
13    -- 声明集合变量 
14    emp_tab     nested_emp_type;
15    -- 定义了一个变量来作为limit的值  
16    v_limit     PLS_INTEGER := 5;
17    -- 定义变量来记录FETCH次数
18    v_counter   PLS_INTEGER := 0;  
19 BEGIN  
20    OPEN emp_cur; 
21 
22    LOOP 
23       -- fetch时使用了BULK COLLECT子句  
24       FETCH emp_cur  
25       BULK   COLLECT INTO emp_tab         
26       LIMIT v_limit; -- 使用limit子句限制提取数据量  
27 
28       EXIT WHEN emp_tab.COUNT = 0; -- 注意此时游标退出使用了emp_tab.COUNT，而不是emp_cur%notfound  
29       v_counter   := v_counter + 1;  -- 记录使用LIMIT之后fetch的次数  
30 
31       FOR i IN emp_tab.FIRST .. emp_tab.LAST  
32       LOOP  
33          DBMS_OUTPUT.PUT_LINE( '当前记录： '
34                     ||emp_tab(i).empno||CHR(9)
35                     ||emp_tab(i).ename||CHR(9)
36                     ||emp_tab(i).hiredate);  
37       END LOOP;  
38    END LOOP;  
39 
40    CLOSE emp_cur;  
41 
42    DBMS_OUTPUT.put_line( '总共获取次数为：' || v_counter );  
43 END;

5.3 在RETURNING INTO中使用BULK COLLECT

BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用。当与这几个DML语句结合时，需要使用RETURNING子句来实现批量绑定。

示例：

1 DECLARE  
 2    TYPE emp_rec_type IS RECORD  
 3    (  
 4       empno      emp.empno%TYPE, 
 5       ename      emp.ename%TYPE, 
 6       hiredate   emp.hiredate%TYPE  
 7    );   
 8    TYPE nested_emp_type IS TABLE OF emp_rec_type;  
 9    emp_tab   nested_emp_type;
10 BEGIN
11    DELETE FROM emp WHERE deptno = 20  
12    RETURNING empno, ename, hiredate   -- 使用returning 返回这几个列  
13    BULK   COLLECT INTO emp_tab;       -- 将返回的列的数据批量插入到集合变量    
14 
15    DBMS_OUTPUT.put_line( '删除 ' || SQL%ROWCOUNT || ' 行记录' );  
16    COMMIT;  
17 
18    IF emp_tab.COUNT > 0 THEN   -- 当集合变量不为空时，输出所有被删除的元素  
19       FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP  
20          DBMS_OUTPUT.PUT_LINE('当前记录：'  
21                     || emp_tab( i ).empno || CHR( 9 )  
22                     || emp_tab( i ).ename || CHR( 9 )  
23                     || emp_tab( i ).hiredate  
24                     || ' 已被删除' );  
25       END LOOP;  
26    END IF;  
27 END;

6. BULK COLLECT的注意事项

1. BULK COLLECT INTO 的目标对象必须是集合类型。
2. 只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
3. 不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。
4. 复合目标(如对象类型)不能在RETURNING INTO子句中使用。
5. 如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO子句中使用。
6. 如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO子句中。

   7. FORALL与BULK COLLECT综合运用

   FORALL与BULK COLLECT是实现批量SQL的两个重要方式，我们可以将其结合使用以提高性能。

   示例：

   1 -- 创建表tb_emp
    2 CREATE TABLE tb_emp AS  
    3    SELECT empno, ename, hiredate  
    4    FROM   emp  
    5    WHERE  1 = 0;  
    6 
    7 DECLARE
    8    -- 声明游标
    9    CURSOR emp_cur IS
   10       SELECT empno, ename, hiredate FROM emp;
   11    -- 基于游标的嵌套表类型
   12    TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE;
   13    -- 声明变量
   14    emp_tab   nested_emp_type; 
   15 BEGIN  
   16    SELECT empno, ename, hiredate  
   17    BULK   COLLECT INTO emp_tab
   18    FROM   emp  
   19    WHERE  sal > 1000;
   20 
   21    -- 使用FORALL语句将变量中的数据插入到表tb_emp  
   22    FORALL i IN 1 .. emp_tab.COUNT                     
   23       INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)  
   24       VALUES emp_tab( i );  
   25 
   26    COMMIT;  
   27    DBMS_OUTPUT.put_line('总共向 tb_emp 表中插入记录数： ' || emp_tab.COUNT);  
   28 END; 

1. 什么是事务

在数据库中事务是工作的逻辑单元，一个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。

如：网上转帐就是典型的要用事务来处理，用以保证数据的一致性。

2. 事务特性

SQL92标准定义了数据库事务的四个特点：（面试时可能会问的）

• 原子性(Atomicity)：一个事务里面所有包含的SQL语句是一个执行整体，不可分割，要么都做，要么都不做。
• 一致性(Consistency)：事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
• 隔离性(Isolation)：是指数据库允许多个并发事务同时对其中的数据进行读写和修改的能力，隔离性可以防止事务的并发执行时，由于他们的操作命令交叉执行而导致的数据不一致状态。
• 持久性 (Durability) : 是指当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。

一组SQL语句操作要成为事务，数据库管理系统必须保证这组操作的原子性（Atomicity）、一致性（consistency）、隔离性（Isolation）和持久性（Durability），这就是ACID特性。