资讯专栏INFORMATION COLUMN

Oracle数据库之FORALL与BULK COLLECT语句

developerworks / 2554人阅读

本文原发布于简书,地址为: Oracle数据库之FORALL与BULK COLLECT语句。
更多数据库资讯请参看github: 数据库知识汇总
1 PL/SQL块的执行过程

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

2 FORALL和BULK COLLECT特点

这两个语句在PL/SQL内部进行一种数组处理,BULK COLLECT提供对数据的高速检索,FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。Oracle数据库使用这些语句大大减少了PL/SQL与SQL语句执行引擎的环境切换次数,从而使其性能有了显著提高。

FORALL,用于增强PL/SQL引擎到SQL引擎的交换。

BULK COLLECT,用于增强SQL引擎到PL/SQL引擎的交换。

如果你要插入5000条数据,一般情况下,在pl/sql中用for循环,循环插入5000次,而用forall一次就可以插入5000条,提高了性能和速度。
3 FORALL介绍

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

3.1 FORALL语法
FORALL index_name 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语句。

3.2 FORALL案例

见sqlscripts/forall-bulkcollect包下的sql脚本事例

3.3 FORALL注意事项

使用FORALL时,应该遵循如下规则:

FORALL语句的执行体,必须是一个多带带的DML语句,比如INSERT,UPDATE或DELETE。

不要显式定义index_row,它被PL/SQL引擎隐式定义为PLS_INTEGER类型,并且它的作用域也仅仅是FORALL。

这个DML语句必须与一个集合的元素相关,并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。

lower_bound和upper_bound之间是按照步进 1 来递增的。

在sql_statement中,不能多带带地引用集合中的元素,只能批量地使用集合。

在sql_statement中使用的集合,下标不能使用表达式。

--error statement
--1.insert into test2 values dr_table(i);dbms_output.put_line(i);不正确,找不到i,因为forall中只能使用单条语句可以引用索引变量
--2.insert into test2 values(dr_table(i).id,dr_table(i).name);集合的field不可以在forall中使用,必须是整体使用
--3.insert into test2 values dr_table(i+1);错误,不可以对索引变量进行运算
--4.insert into test2 values(dr_table(i));报没有足够的值错误,此处外面不可以加括号,当有多个字段的时候,单个字段可以加括号
4 BULK COLLECT的使用 4.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一次即可提取所有行并绑定到记录变量,这就是所谓的批量绑定。

4.2 在FETCH INTO中使用BULK COLLECT

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

语法:

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

注意:

在使用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;
4.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;
4.4 BULK COLLECT的注意事项

BULK COLLECT INTO 的目标对象必须是集合类型。

只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。

不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。

复合目标(如对象类型)不能在RETURNING INTO子句中使用。

如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO子句中使用。

如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO子句中

5 FORALL与BULK COLLECT综合运用

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

-- create tb_emp_test
 CREATE TABLE tb_emp_test AS
    SELECT empno, ename, hiredate
   FROM   EMP_TEST
   WHERE  1 = 0;

 DECLARE
   -- declare cursor
   CURSOR EMP_CUR IS
     SELECT EMPNO, ENAME, HIREDATE FROM EMP_TEST;
   -- 基于游标的嵌套表类型
   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_TEST
    WHERE SAL > 1000;

   -- 使用FORALL语句将变量中的数据插入到表tb_emp
   FORALL I IN 1 .. EMP_TAB.COUNT
     INSERT INTO
       (SELECT EMPNO, ENAME, HIREDATE FROM TB_EMP_TEST)
     VALUES EMP_TAB
       (I);

   COMMIT;
   DBMS_OUTPUT.PUT_LINE("总共向 tb_emp 表中插入记录数: " || EMP_TAB.COUNT);
 END;
6 总结

limit减少内存占用,如果数据量较大一次性全部加载到内存中,对PGA来说压力太大,可采用limit的方法一次加载一定数量的数据,建议值通常为1000。使用limit时注意,循环的时候如果用while cursor_name%found loop,对于最后一次fetch的数据量不足设定值1000,%found条件就会不成立。示例使用v_oid_lst.count > 0作为判断条件。

在写plsql代码块,定义数值变量时,建议采用pls_integer类型,或者simple_integer类型。两者的区别:

Oracle9i之前有binary_integer类型,和11g中引入的pls_integer数值范围相同:-2147483647~+2147483647,但pls_integer有更高的性能。两者性能均优于number类型。
Oracle中也引入了simple_integer类型,不过不能包含null值,范围:-2147483648~2147483647,性能优于pls_integer。

使用ref cursor。

使用绑定变量。

自定义table类型。

Bulk collect into加载到内存中,处理完业务逻辑后forall批量插入到数据表中。

Forall可以使用returning bulk collect into,且可使用sql%rowcount返回其更新行数。

type numbers is table of number index by binary_integer/pls_integer/simple_integer; 其作用是:

1. 加了"index by binary_integer "后,numbers类型的下标就是自增长,numbers类型在插入元素时,不需要初始化,不需要每次extend增加一个空间。
2. 如果没有这句话"index by binary_integer",那就得要显示对初始化,且每插入一个元素到numbers类型的table中时,都需要先extend。

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/35374.html

相关文章

  • 工作过程遇到据库相关的知识点汇总

    摘要:工作过程遇到的,或者平时学习的数据库相关的知识点汇总。大数据量加载方法汇总大数据量更新方法去掉回车换行空格的方法系统参数详解包与语句的用法运算符和转义操作符抛出自定义错误列与逗号分隔字符串互相转换查看被锁的表以及如何解锁不区分表名大小写 工作过程遇到的,或者平时学习的数据库相关的知识点汇总。 Home Oracle 1 大数据量加载方法汇总 2 大数据量更新方法 3 去掉回车换行空格...

    Doyle 评论0 收藏0
  • 工作过程遇到据库相关的知识点汇总

    摘要:工作过程遇到的,或者平时学习的数据库相关的知识点汇总。大数据量加载方法汇总大数据量更新方法去掉回车换行空格的方法系统参数详解包与语句的用法运算符和转义操作符抛出自定义错误列与逗号分隔字符串互相转换查看被锁的表以及如何解锁不区分表名大小写 工作过程遇到的,或者平时学习的数据库相关的知识点汇总。 Home Oracle 1 大数据量加载方法汇总 2 大数据量更新方法 3 去掉回车换行空格...

    张迁 评论0 收藏0
  • 如何将其他RDBMS的数据到迁移到Trafodion

    摘要:为了避免这种情况,可以针对表短期内被两个以上的语句所加载执行一个大的数据压缩。通常,对一张大表执行数据压缩会花费大量的时间几分钟到几小时不等。 本文介绍了如何将数据从现有的RDBMS迁移到Trafodion数据库。从其它的RDBMS或外部数据源向Trafodion集群中导入大量的重要数据,可以通过下面两步完美实现: 在Trafodion集群中,将数据从源头导入Hive表。使用下列方...

    李文鹏 评论0 收藏0
  • 如何将其他RDBMS的数据到迁移到Trafodion

    摘要:为了避免这种情况,可以针对表短期内被两个以上的语句所加载执行一个大的数据压缩。通常,对一张大表执行数据压缩会花费大量的时间几分钟到几小时不等。 本文介绍了如何将数据从现有的RDBMS迁移到Trafodion数据库。从其它的RDBMS或外部数据源向Trafodion集群中导入大量的重要数据,可以通过下面两步完美实现: 在Trafodion集群中,将数据从源头导入Hive表。使用下列方...

    weknow619 评论0 收藏0
  • Hadoop家族学习路线图

    摘要:本文为家族开篇,家族学习路线图目录家族产品家族学习路线图家族产品截止到年,根据的统计,家族产品已经达到个接下来,我把这个产品,分成了类。家族学习路线图下面我将分别介绍各个产品的安装和使用,以我经验总结我的学习路线。 Hadoop家族系列文章, 主要介绍Hadoop家族产品,常用的项目包括Hadoop, Hive, Pig, HBase, Sqoop, Mahout, Zookeeper, ...

    includecmath 评论0 收藏0

发表评论

0条评论

最新活动
阅读需要支付1元查看
<