Oracle数据库之FORALL与BULK COLLECT语句

时间：2020-12-09 03:54:01

Oracle数据库之FORALL与BULK COLLECT语句

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

Oracle数据库之FORALL与BULK COLLECT语句

从Oracle 8i开始，PL/SQL得到了两点增强，可以将PL/SQL引擎和SQL引擎之间的多次上下文交换压缩为一次交换：

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

1. FORALL介绍

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

Oracle数据库之FORALL与BULK COLLECT语句

语法：

FORALL index IN

    { lower_bound .. upper_bound

    | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]

    | VALUES OF index_collection

    }

 [ 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的使用

示例所使用表结构：

create table tmp_tab(

  id number(5),

  name varchar2(50)

);

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

--批量插入

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..100 loop

    tb_table(i).id:=i;

    tb_table(i).name:='NAME'||i;

  end loop;

  forall i in 1..tb_table.count

    insert into tmp_tab values tb_table(i);

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..100 loop

    tb_table(i).id:=i;

    tb_table(i).name:='MY_NAME_'||i;

  end loop;

  forall i in 1..tb_table.count

    update tmp_tab t set row = tb_table(i) where t.id = tb_table(i).id;

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子句：

declare

  type demo_table_type is table of tmp_tab%rowtype index by binary_integer;

  demo_table demo_table_type;

begin

  for i in 1..10 loop

    demo_table(i).id:=i;

    demo_table(i).name:='NAME'||i;

  end loop;

  -- 使用集合的delete方法移除第3、6、9三个成员

  demo_table.delete(3);

  demo_table.delete(6);

  demo_table.delete(9);

  forall i in indices of demo_table

    insert into tmp_tab values demo_table(i);

end;

示例3，使用VALUES OF子句：

declare

  type index_poniter_type is table of pls_integer;

  index_poniter index_poniter_type;

  type demo_table_type is table of tmp_tab%rowtype index by binary_integer;

  demo_table demo_table_type;

begin

  index_poniter := index_poniter_type(1,3,5,7);

  for i in 1..10 loop

    demo_table(i).id:=i;

    demo_table(i).name:='NAME'||i;

  end loop;

  forall i in values of index_poniter

    insert into tmp_tab values demo_table(i);

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

示例：

DECLARE

   -- 定义记录类型

   TYPE emp_rec_type IS RECORD

   (

      empno      emp.empno%TYPE,

      ename      emp.ename%TYPE,

      hiredate   emp.hiredate%TYPE

   );

   -- 定义基于记录的嵌套表

   TYPE nested_emp_type IS TABLE OF emp_rec_type;

   -- 声明变量

   emp_tab   nested_emp_type;

BEGIN

   -- 使用BULK COLLECT将所得的结果集一次性绑定到记录变量emp_tab中

   SELECT empno, ename, hiredate

   BULK   COLLECT INTO emp_tab

   FROM   emp;  

   FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP

      DBMS_OUTPUT.PUT_LINE('当前记录： '

                ||emp_tab(i).empno||chr(9)

                ||emp_tab(i).ename||chr(9)

                ||emp_tab(i).hiredate);

   END LOOP;

END;

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

5.2 在FETCH INTO中使用BULK COLLECT

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

语法：

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

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

示例：

DECLARE

   CURSOR emp_cur IS

      SELECT empno, ename, hiredate FROM emp;  

   TYPE emp_rec_type IS RECORD

   (

      empno      emp.empno%TYPE,

      ename      emp.ename%TYPE ,

      hiredate   emp.hiredate%TYPE

   );

   -- 定义基于记录的嵌套表

   TYPE nested_emp_type IS TABLE OF emp_rec_type;

   -- 声明集合变量

   emp_tab     nested_emp_type;

   -- 定义了一个变量来作为limit的值

   v_limit     PLS_INTEGER := 5;

   -- 定义变量来记录FETCH次数

   v_counter   PLS_INTEGER := 0;

BEGIN

   OPEN emp_cur; 

   LOOP

      -- fetch时使用了BULK COLLECT子句

      FETCH emp_cur

      BULK   COLLECT INTO emp_tab

      LIMIT v_limit; -- 使用limit子句限制提取数据量  

      EXIT WHEN emp_tab.COUNT = 0; -- 注意此时游标退出使用了emp_tab.COUNT，而不是emp_cur%notfound

      v_counter   := v_counter + 1;  -- 记录使用LIMIT之后fetch的次数  

      FOR i IN emp_tab.FIRST .. emp_tab.LAST

      LOOP

         DBMS_OUTPUT.PUT_LINE( '当前记录： '

                    ||emp_tab(i).empno||CHR(9)

                    ||emp_tab(i).ename||CHR(9)

                    ||emp_tab(i).hiredate);

      END LOOP;

   END LOOP;  

   CLOSE emp_cur;  

   DBMS_OUTPUT.put_line( '总共获取次数为：' || v_counter );

END;

5.3 在RETURNING INTO中使用BULK COLLECT

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

示例：

DECLARE

   TYPE emp_rec_type IS RECORD

   (

      empno      emp.empno%TYPE,

      ename      emp.ename%TYPE,

      hiredate   emp.hiredate%TYPE

   );

   TYPE nested_emp_type IS TABLE OF emp_rec_type;

   emp_tab   nested_emp_type;

BEGIN

   DELETE FROM emp WHERE deptno = 20

   RETURNING empno, ename, hiredate   -- 使用returning 返回这几个列

   BULK   COLLECT INTO emp_tab;       -- 将返回的列的数据批量插入到集合变量    

   DBMS_OUTPUT.put_line( '删除 ' || SQL%ROWCOUNT || ' 行记录' );

   COMMIT;  

   IF emp_tab.COUNT > 0 THEN   -- 当集合变量不为空时，输出所有被删除的元素

      FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP

         DBMS_OUTPUT.PUT_LINE('当前记录：'

                    || emp_tab( i ).empno || CHR( 9 )

                    || emp_tab( i ).ename || CHR( 9 )

                    || emp_tab( i ).hiredate

                    || ' 已被删除' );

      END LOOP;

   END IF;

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的两个重要方式，我们可以将其结合使用以提高性能。

示例：

-- 创建表tb_emp

CREATE TABLE tb_emp AS

   SELECT empno, ename, hiredate

   FROM   emp

   WHERE  1 = 0;  

DECLARE

   -- 声明游标

   CURSOR emp_cur IS

      SELECT empno, ename, hiredate FROM emp;

   -- 基于游标的嵌套表类型

   TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE;

   -- 声明变量

   emp_tab   nested_emp_type;

BEGIN

   SELECT empno, ename, hiredate

   BULK   COLLECT INTO emp_tab

   FROM   emp

   WHERE  sal > 1000;

   -- 使用FORALL语句将变量中的数据插入到表tb_emp

   FORALL i IN 1 .. emp_tab.COUNT

      INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)

      VALUES emp_tab( i );  

   COMMIT;

   DBMS_OUTPUT.put_line('总共向 tb_emp 表中插入记录数： ' || emp_tab.COUNT);

END;

相关文章