批量 SQL 之 FORALL 语句

    对PL/SQL而言,任何的PL/SQL块或者子程序都是PL/SQL引擎来处理,而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL引擎来处 理,SQL引擎处理完毕后向PL/SQL引擎返回数据。Pl/SQL与SQL引擎之间的通信则称之为上下文切换。过多的上下文切换将带来过量的性能负载。 因此为减少性能的FORALL与BULK COLLECT的子句应运而生。即仅仅使用一次切换多次执行来降低上下文切换次数。本文主要描述FORALL子句。 一、FORALL语法描述     FORALL loop_counter IN bounds_clause            -->注意FORALL块内不需要使用loop, end loop     SQL_STATEMENT [SAVE EXCEPTIONS];     bounds_clause的形式     lower_limit .. upper_limit                                     -->指明循环计数器的上限和下限,与for循环类似     INDICES OF collection_name BETWEEN lower_limit .. upper_limit  -->引用特定集合元素的下标(该集合可能为稀疏)     VALUES OF colletion_name                                       -->引用特定集合元素的值     SQL_STATEMENT部分:SQL_STATEMENT部分必须是一个或者多个集合的静态或者动态的DML(insert,update,delete)语句。     SAVE EXCEPTIONS部分:对于SQL_STATEMENT部分导致的异常使用SAVE EXCEPTIONS来保证异常存在时语句仍然能够继续执行。 二、使用 FORALL 代替 FOR 循环提高性能

-->下面的示例使用了FOR循环与FORALL循环操作进行对比,使用FORALL完成同样的功能,性能明显提高
CREATE TABLE t
(
   col_num   NUMBER
  ,col_var   VARCHAR2( 10 )
);

DECLARE
   TYPE col_num_type IS TABLE OF NUMBER            -->声明了两个联合数组
                           INDEX BY PLS_INTEGER;

   TYPE col_var_type IS TABLE OF VARCHAR2( 10 )
                           INDEX BY PLS_INTEGER;

   col_num_tab    col_num_type;
   col_var_tab    col_var_type;
   v_start_time   INTEGER;
   v_end_time     INTEGER;
BEGIN
   FOR i IN 1 .. 5000                    -->使用FOR循环向数组填充元素
   LOOP
      col_num_tab( i ) := i;
      col_var_tab( i ) := 'var_' || i;
   END LOOP;

   v_start_time := DBMS_UTILITY.get_time;  -->获得FOR循环向表t插入数据前的初始时间

   FOR i IN 1 .. 5000                   -->使用FOR循环向表t插入数据
   LOOP
      INSERT INTO t
      VALUES ( col_num_tab( i ), col_var_tab( i ) );
   END LOOP;

   v_end_time  := DBMS_UTILITY.get_time;
   DBMS_OUTPUT.put_line( 'Duration of the FOR LOOP: ' || ( v_end_time - v_start_time ) );
   v_start_time := DBMS_UTILITY.get_time;

   FORALL i IN 1 .. 5000          -->使用FORALL循环向表t插入数据
      INSERT INTO t
      VALUES ( col_num_tab( i ), col_var_tab( i ) );

   v_end_time  := DBMS_UTILITY.get_time;
   DBMS_OUTPUT.put_line( 'Duration of the FORALL STATEMENT: ' || ( v_end_time - v_start_time ) );
   COMMIT;
END;

Duration of the FOR LOOP: 68           -->此处的计时单位为百分之一秒,即0.68s,下同
Duration of the FORALL STATEMENT: 18

PL/SQL procedure successfully completed.

三、SAVE EXCEPTIONS          对于任意的SQL语句执行失败,将导致整个语句或整个事务会滚。而使用SAVE EXCEPTIONS可以使得在对应的SQL语句异常的情形下,FORALL 仍然可以继续执行。如果忽略了SAVE EXCEPTIONS时,当异常发生,FORALL语句就会停止执行。因此SAVE EXCEPTIONS使得FORALL子句中的DML下 产生的所有异常都将记录在SQL%BULK_EXCEPTIONS的游标属性中。SQL%BULK_EXCEPTIONS属性是个记录集合,其中的每条记录由两个字段组成,

ERROR_INDEX和ERROR_CODE。ERROR_INDEX字段会存储发生异常的FORALL语句的迭代编号,而ERROR_CODE则存储对应异常的ORACLE错误代码。类似于这样:(2,01400),(6,1476)和(10,12899)。存放在%BULK_EXCEPTIONS中的值总是与最近一次FORALL语句执行的结果相关,异常的个数存放在%BULK_EXCEPTIONS的COUNT属性中,%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

1、%BULK_EXCEPTIONS的用法
CREATE TABLE tb_emp AS              -->创建表tb_emp
   SELECT empno, ename, hiredate
   FROM   emp
   WHERE  1 = 2;

ALTER TABLE tb_emp MODIFY(empno NOT NULL);   -->为表添加约束

DECLARE
   TYPE col_num_type IS TABLE OF NUMBER            -->一共定义了3个联合数组类型
                           INDEX BY PLS_INTEGER;

   TYPE col_var_type IS TABLE OF VARCHAR2( 100 )
                           INDEX BY PLS_INTEGER;

   TYPE col_date_type IS TABLE OF DATE
                            INDEX BY PLS_INTEGER;

   empno_tab      col_num_type;
   ename_tab      col_var_type;
   hiredate_tab   col_date_type;
   v_counter      PLS_INTEGER := 0;
   v_total        INTEGER := 0;
   errors         EXCEPTION;                      -->声明异常
   PRAGMA EXCEPTION_INIT( errors, -24381 );
BEGIN
   FOR rec IN ( SELECT empno, ename, hiredate FROM emp )   -->使用for循环将数据填充到联合数组
   LOOP
      v_counter   := v_counter + 1;
      empno_tab( v_counter ) := rec.empno;
      ename_tab( v_counter ) := rec.ename;
      hiredate_tab( v_counter ) := rec.hiredate;
   END LOOP;

   empno_tab( 2 ) := NULL;                                -->对部分数据进行处理以产生异常
   ename_tab( 5 ) := RPAD( ename_tab( 5 ), 15, '*' );
   empno_tab( 10 ) := NULL;

   FORALL i IN 1 .. empno_tab.COUNT                      -->使用forall将联合数组中的数据插入到表tb_emp
   SAVE EXCEPTIONS
      INSERT INTO tb_emp
      VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );

   COMMIT;

   SELECT COUNT( * ) INTO v_total FROM tb_emp;

   DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' );
EXCEPTION
   WHEN errors THEN
      DBMS_OUTPUT.put_line( 'There are ' || SQL%bulk_exceptions.COUNT || ' exceptions' );

      FOR i IN 1 .. SQL%bulk_exceptions.COUNT            -->SQL%bulk_exceptions.COUNT记录异常个数来控制迭代
      LOOP
         DBMS_OUTPUT.
          put_line(
                       'Record '
                    || SQL%bulk_exceptions( i ).error_index
                    || ' caused error '
                    || i
                    || ': '
                    || SQL%bulk_exceptions( i ).error_code
                    || ' '
                    || SQLERRM( -SQL%bulk_exceptions( i ).error_code ) );   -->使用SQLERRM根据错误号抛出具体的错误信息
      END LOOP;
END;

There are 3 exceptions
Record 2 caused error 1: 1400 ORA-01400: cannot insert NULL into ()
Record 5 caused error 2: 12899 ORA-12899: value too large for column  (actual: , maximum: )
Record 10 caused error 3: 1400 ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

2、%BULK_ROWCOUNT
	%BULK_ROWCOUNT也是专门为FORALL设计的,用于保存第i个元素第i次insert或update或delete所影响到的行数。如果第i次操作没有行被影
响,则%BULK_ROWCOUNT返回为零值。FORALL语句和%BULK_ROWCOUNT属性使用同样的下标索引。如果FORALL使用下标索引的范围在5到8的话,那么
%BULK_ROWCOUNT的也是5到8。需要注意的是一般情况下,对于insert .. values而言,所影响的行数为1,即%BULK_ROWCOUNT的值为1。而对于
insert .. select方式而言,%BULK_ROWCOUNT的值就有可能大于1。update与delete语句存在0,1,以及大于1的情形。

DECLARE
   TYPE dept_tab_type IS TABLE OF NUMBER;

   dept_tab   dept_tab_type := dept_tab_type( 10, 20, 50 );    -->声明及初始化嵌套表
BEGIN
   FORALL i IN dept_tab.FIRST .. dept_tab.LAST                 -->使用FORALL更新
      UPDATE emp
      SET    sal          = sal * 1.10
      WHERE  deptno = dept_tab( i );

   -- COMMIT;
   FOR i IN 1 .. dept_tab.COUNT                               -->循环输出每次执行SQL语句影响的行数
   LOOP
      DBMS_OUTPUT.put_line( 'Dept no ' || dept_tab( i ) || ' has ' || SQL%bulk_rowcount (i) || ' rows been updated' );
   END LOOP;

   -- Did the 3rd UPDATE statement affect any rows?
   IF SQL%bulk_rowcount (3) = 0 THEN
      DBMS_OUTPUT.put_line( 'The deptno 50 has not child record' );
   END IF;
END;

Dept no 10 has 3 rows been updated
Dept no 20 has 5 rows been updated
Dept no 50 has 0 rows been updated
The deptno 50 has not child record

PL/SQL procedure successfully completed.

四、INDICES OF 选项     INDICES OF 选项用于处理稀疏集合类型。即当集合(嵌套表或联合数组)中的元素被删除之后,对稀疏集合实现迭代。

-->下面的脚本同前面的示例基本相似,所不同的是使用了delete方式删除其中的部分记录,导致集合变得稀疏。
-->其次在forall子句处使用indices OF方式来控制循环。		
TRUNCATE TABLE tb_emp;

DECLARE
   TYPE col_num_type IS TABLE OF NUMBER
                           INDEX BY PLS_INTEGER;

   TYPE col_var_type IS TABLE OF VARCHAR2( 100 )
                           INDEX BY PLS_INTEGER;

   TYPE col_date_type IS TABLE OF DATE
                            INDEX BY PLS_INTEGER;

   empno_tab      col_num_type;
   ename_tab      col_var_type;
   hiredate_tab   col_date_type;
   v_counter      PLS_INTEGER := 0;
   v_total        INTEGER := 0;
BEGIN
   FOR rec IN ( SELECT empno, ename, hiredate FROM emp )
   LOOP
      v_counter   := v_counter + 1;
      empno_tab( v_counter ) := rec.empno;
      ename_tab( v_counter ) := rec.ename;
      hiredate_tab( v_counter ) := rec.hiredate;
   END LOOP;

   empno_tab.delete( 2 );       -->此处删除了数组中的第二个元素,导致数组变为稀疏型
   ename_tab.delete( 2 );
   hiredate_tab.delete( 2 );

   FORALL i IN indices OF empno_tab   -->此处使用了indices OF empno_tab,则所有未被delete的元素都将进入循环
      INSERT INTO tb_emp
      VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );

   COMMIT;

   SELECT COUNT( * ) INTO v_total FROM tb_emp;

   DBMS_OUTPUT.put_line( v_total || ' rows were inserted to tb_emp' );
END;

13 rows were inserted to tb_emp

PL/SQL procedure successfully completed.

五、VALUES OF 选项     VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。     VALUES OF选项使用时有一些限制           如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引           VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER           当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常               

TRUNCATE TABLE tb_emp;
				
CREATE TABLE tb_emp_ins_log AS                    -->创建一张与tb_emp结构类似的表tb_emp_ins_log
   SELECT *
   FROM   tb_emp
   WHERE  1 = 0;

ALTER TABLE tb_emp_ins_log MODIFY(ename VARCHAR2(50));   -->修改列ename的长度

DECLARE
   TYPE col_num_type IS TABLE OF tb_emp.empno%TYPE
                           INDEX BY PLS_INTEGER;

   TYPE col_var_type IS TABLE OF VARCHAR2( 100 )
                           INDEX BY PLS_INTEGER;

   TYPE col_date_type IS TABLE OF tb_emp.hiredate%TYPE
                            INDEX BY PLS_INTEGER;

   TYPE ins_log_type IS TABLE OF PLS_INTEGER         -->此处较之前的示例多声明了一个联合数组
                           INDEX BY PLS_INTEGER;     -->用于填充异常记录的元素值

   empno_tab      col_num_type;
   ename_tab      col_var_type;
   hiredate_tab   col_date_type;
   ins_log_tab    ins_log_type;
   v_counter      PLS_INTEGER := 0;
   v_total        INTEGER := 0;
   errors         EXCEPTION;
   PRAGMA EXCEPTION_INIT( errors, -24381 );
BEGIN
   FOR rec IN ( SELECT empno, ename, hiredate FROM emp )
   LOOP
      v_counter   := v_counter + 1;
      empno_tab( v_counter ) := rec.empno;
      ename_tab( v_counter ) := rec.ename;
      hiredate_tab( v_counter ) := rec.hiredate;
   END LOOP;

   ename_tab( 2 ) := RPAD( ename_tab( 2 ), 15, '*' );    -->使记录2与记录5的ename列长度变长而产生异常
   ename_tab( 5 ) := RPAD( ename_tab( 5 ), 15, '*' );
   empno_tab( 6 ) := NULL;                          -->使第6条记录的empno为NULL值,由于表tb_emp的empno不允许为NULL而产生异常

   FORALL i IN 1 .. empno_tab.COUNT
   SAVE EXCEPTIONS
      INSERT INTO tb_emp
      VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );

   COMMIT;
EXCEPTION
   WHEN errors THEN
      FOR i IN 1 .. SQL%bulk_exceptions.COUNT
      LOOP
         ins_log_tab( i ) := SQL%bulk_exceptions( i ).error_index;   -->异常记录的索引值将填充ins_log_type联合数组
      END LOOP;                                    -->此处的结果是ins_log_tab(1)=2,  ins_log_tab(2)=5,  ins_log_tab(2)=6

      FORALL i IN VALUES OF ins_log_tab   -->使用VALUES OF子句为ins_log_type联合数组中的元素值
         INSERT INTO tb_emp_ins_log
         VALUES ( empno_tab( i ), ename_tab( i ), hiredate_tab( i ) );  -->因此values中的i分别为2和5

      COMMIT;
END;

PL/SQL procedure successfully completed.

-->异常的记录被插入到表tb_emp_ins_log
select * from tb_emp_ins_log;

     EMPNO ENAME                                              HIREDATE
---------- -------------------------------------------------- ---------
      7369 Henry**********                                    17-DEC-80
      7566 JONES**********                                    02-APR-81
           MARTIN                                             28-SEP-81

六、INDICES OF 与 VALUES OF 的综合运用

-->下面的例子来自Oracle		http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm
-- Create empty tables to hold order details

CREATE TABLE valid_orders
(
   cust_name   VARCHAR2( 32 )
  ,amount      NUMBER( 10, 2 )
);

CREATE TABLE big_orders AS
   SELECT *
   FROM   valid_orders
   WHERE  1 = 0;

CREATE TABLE rejected_orders AS
   SELECT *
   FROM   valid_orders
   WHERE  1 = 0;

DECLARE
   -- Make collections to hold a set of customer names and order amounts.
   SUBTYPE cust_name IS valid_orders.cust_name%TYPE;

   TYPE cust_typ IS TABLE OF cust_name;

   cust_tab             cust_typ;

   SUBTYPE order_amount IS valid_orders.amount%TYPE;

   TYPE amount_typ IS TABLE OF NUMBER;

   amount_tab           amount_typ;

   -- Make other collections to point into the CUST_TAB collection.
   TYPE index_pointer_t IS TABLE OF PLS_INTEGER;

   big_order_tab        index_pointer_t := index_pointer_t( );
   rejected_order_tab   index_pointer_t := index_pointer_t( );

   PROCEDURE setup_data IS
   BEGIN
      -- Set up sample order data, including some invalid orders and some 'big' orders.
      cust_tab    :=
         cust_typ( 'Company1','Company2','Company3','Company4','Company5' );
      amount_tab  :=
         amount_typ( 5000.01,0,150.25,4000.00,NULL );
   END;
BEGIN
   setup_data( );
   DBMS_OUTPUT.put_line( '--- Original order data ---' );

   FOR i IN 1 .. cust_tab.LAST
   LOOP
      DBMS_OUTPUT.put_line( 'Customer #' || i || ', ' || cust_tab( i ) || ': $' || amount_tab( i ) );
   END LOOP;

   -- Delete invalid orders (where amount is null or 0).
   FOR i IN 1 .. cust_tab.LAST
   LOOP
      IF amount_tab( i ) IS NULL OR amount_tab( i ) = 0 THEN
         cust_tab.delete( i );
         amount_tab.delete( i );
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line( '--- Data with invalid orders deleted ---' );

   FOR i IN 1 .. cust_tab.LAST
   LOOP
      IF cust_tab.EXISTS( i ) THEN
         DBMS_OUTPUT.put_line( 'Customer #' || i || ', ' || cust_tab( i ) || ': $' || amount_tab( i ) );
      END IF;
   END LOOP;

   -- Because the subscripts of the collections are not consecutive, use
   -- FORALL...INDICES OF to iterate through the actual subscripts,
   -- rather than 1..COUNT
   FORALL i IN indices OF cust_tab
      INSERT INTO valid_orders( cust_name, amount )
      VALUES ( cust_tab( i ), amount_tab( i ) );

   -- Now process the order data differently
   -- Extract 2 subsets and store each subset in a different table
   setup_data( );                    -- Initialize the CUST_TAB and AMOUNT_TAB collections again.

   FOR i IN cust_tab.FIRST .. cust_tab.LAST
   LOOP
      IF amount_tab( i ) IS NULL OR amount_tab( i ) = 0 THEN
         rejected_order_tab.EXTEND;                          -- Add a new element to this collection
         -- Record the subscript from the original collection
         rejected_order_tab( rejected_order_tab.LAST ) := i;
      END IF;

      IF amount_tab( i ) > 2000 THEN
         big_order_tab.EXTEND;                            -- Add a new element to this collection
         -- Record the subscript from the original collection
         big_order_tab( big_order_tab.LAST ) := i;
      END IF;
   END LOOP;

   -- Now it's easy to run one DML statement on one subset of elements,
   -- and another DML statement on a different subset.
   FORALL i IN VALUES OF rejected_order_tab
      INSERT INTO rejected_orders
      VALUES ( cust_tab( i ), amount_tab( i ) );

   FORALL i IN VALUES OF big_order_tab
      INSERT INTO big_orders
      VALUES ( cust_tab( i ), amount_tab( i ) );

   COMMIT;
END;

--- Original order data ---
Customer #1, Company1: $5000.01
Customer #2, Company2: $0
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
Customer #5, Company5: $
--- Data with invalid orders deleted ---
Customer #1, Company1: $5000.01
Customer #3, Company3: $150.25
Customer #4, Company4: $4000

PL/SQL procedure successfully completed.

SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;

Customer                         Valid order amount
-------------------------------- ------------------
Company1                                    5000.01
Company3                                     150.25
Company4                                       4000

SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;

Customer                         Big order amount
-------------------------------- ----------------
Company1                                  5000.01
Company4                                     4000

SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;

Customer                         Rejected order amount
-------------------------------- ---------------------
Company2                                             0
Company5

--Author: Robinson Cheng
--Blog : http://blog.csdn.net/robinson_0612
     
--	上面的例子对订单进行分类,并将其保存到三张不同类型的表中。
--	1、首先定义了两个嵌套表cust_tab,amount_tab用于保存原始数据,setup_data( )则用来初始化数据。
--	2、第一个for循环用于输出所有的订单,第二个for循环则用来将删除amount_tab中为NULL或0值的记录。
--	3、第三个for循环则用来输出经过删除之后剩余的记录,使用exists方法判断。
--	4、使用forall子句将所有有效的记录插入到valid_orders,注意此时使用了indices of,因此此时的两个嵌套表已为稀疏表。
--	5、在这之后,使用setup_data( )重新初始化数据。
--	6、将无效订单的下标记录到rejected_order_tab嵌套表,将amount > 2000订单的下标记录到big_order_tab。
--	7、使用VALUES OF 子句将两个嵌套表中对应下表的记录插入到对应的表中。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏后端技术探索

分表查询统计的一个具体案例

问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

651
来自专栏乐沙弥的世界

PL/SQL --> PL/SQL记录

PL/SQL记录有着类似于表的数据结构,是一个或多个字段且拥有数据类型的集合体。定义了PL/SQL记录类型之后,可以定义PL/SQL记录变

723
来自专栏别先生

MYSQL操作数据表中的记录

36:操作数据表中的记录 插入记录   INSERT INTO 表名  VALUES();   或者INSERT 表名  VALUES(); ? ? ? ? ...

1995
来自专栏深度学习之tensorflow实战篇

ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)/ ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY CO

ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法      今天在使用多字段去重时,由于某些字段有多种可...

2553
来自专栏芋道源码1024

数据库中间件 Sharding-JDBC 源码分析 —— SQL 解析(三)之查询SQL

1. 概述 2. SelectStatement 2.1 AbstractSQLStatement 2.2 SQLToken 3. #query() 3.1 ...

3868
来自专栏PHP在线

如何在 MySQL 中判断中文字符?

原文出处: 温国兵 一 引子 在生产环境中,经常会有这样的场景:获得中文数据。那问题就来了,怎么才能匹配出中文字符呢? 本文提供两种方法。 二 演示 2.1 ...

30411
来自专栏数据之美

图文并茂详解 SQL JOIN

Join是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另...

2608
来自专栏熊二哥

那些年我们写过的T-SQL(中篇)

中篇的重点在于,在复杂情况下使用表表达式的查询,尤其是公用表表达式(CTE),也就是非常方便的WITH AS XXX的应用,在SQL代码,这种方式至少可以提高一...

1827
来自专栏pangguoming

SQL语句大小写是否区分的问题,批量修改整个数据库所有表所有字段大小写

一、实例介绍 SQL语句大小写到底是否区分呢?我们先从下面的这个例子来看一下: 例: --> 创建表,插入数据: declare @maco table (nu...

3967
来自专栏乐沙弥的世界

SQL基础-->分组与分组函数

使用group by column1,column2,..按columm1,column2进行分组,即column1,column2组合相同的值为一个组

1052

扫码关注云+社区