FORALL全解析

最新推荐文章于 2024-04-03 16:20:16 发布

Fan_-_

最新推荐文章于 2024-04-03 16:20:16 发布

阅读量2.8k

点赞数

分类专栏： PLSQL Oralce开发

本文链接：https://blog.csdn.net/ashic/article/details/50831537

版权

Oralce开发同时被 2 个专栏收录

19 篇文章 0 订阅

订阅专栏

PLSQL

11 篇文章 0 订阅

订阅专栏

FORALL语句会从PL/SQL引擎会向SQL引擎发送SQL语句，后者会向PL/SQL引擎返回结果。PL/SQL和SQL引擎之间的通信称为上下文切换。这种上下文切换存在一定的性能负载。

1、FORALL语句

FORALL语句会从PL/SQL引擎向SQL引擎批量发送INSERT、UPDATE和DELETE语句，而不是每次发送一条语句。例如，考虑下面的数值型FOR循环会10次迭代执行INSERT语句：

FOR i IN 1..10 LOOP

INSERT INTO table_name

VALUES (...);

END LOOP;

该INSERT语句会从PL/SQL引擎发送到SQL引擎10次。也就是说，会发生10次上下文其换。如果使用FORALL语句替换这个FOR循环，只需要发送一次INSERT语句，但是会执行10次。在这种情况下，在PL/SQL和SQL之间只会发生一次上下文切换。

FORALL语句具有如下结构

 
 FORALL loop_counter IN bounds_clause
SQL_STATEMENT [SAVE EXCEPTIONS];

其中，bounds_clause是下面形式之一

 
 lower_limit..upper_limit  就是 1..10
INDICES OF collection_name BETWEEN lower_limit..upper_limit
VALUES OF collection_name

后面这俩，第二种形式INDICES OF会引用特定集合中单个元素的下标。这个集合也许是嵌套表，或者具有数字下标的联合数组

第三种形式VALUES OF会引用特定集合中单个元素的值，这个集合可能是嵌套表或者联合数组。

接下来，SQL_STATEMENT是引用一个或者多个集合的静态或者动态的INSERT、UPDATE或者DELETE语句。最后，即使当SQL_STATEMENT导致异常时，选项SAVE EXCEPTIONS仍旧能够保证FORALL语句继续执行

实验前创建表

CREATE TABLE test(row_num number,row_text varchar2(10));

 
 declare
    type row_num_type is table of number index by simple_integer;
    type row_text_type is table of varchar2(10) index by simple_integer;
    row_num_tab row_num_type;
    row_text_tab row_text_type;
    v_total number;
begin
    for i in 1..10 loop
        row_num_tab(i) := i;
        row_text_tab(i) := 'row'||i;
    end loop;
    forall i in 1..10 
        insert into test(row_num,row_text)
        values (row_num_tab(i),row_text_tab(i));
    commit;
    select count(*) into v_total from test;
    dbms_output.put_line(v_total||' rows insterted');
end;
/

结合动态sql

 
 declare
    type row_num_type is table of number index by simple_integer;
    type row_text_type is table of varchar2(10) index by simple_integer;
    row_num_tab row_num_type;
    row_text_tab row_text_type;
    v_total number;
    sql_stmt varchar2(300);
begin
    for i in 1..10 loop
        row_num_tab(i) := i;
        row_text_tab(i) := 'row'||i;
    end loop;
    sql_stmt := 'insert into test(row_num,row_text) values (:1,:2)';
    forall i in 1..10 
        execute immediate sql_stmt using row_num_tab(i),row_text_tab(i);
    commit;
    select count(*) into v_total from test;
    dbms_output.put_line(v_total||' rows insterted');
end;
/
10 rows insterted
PL/SQL procedure successfully completed.

比较for loop和forall statement时间差异

这里用到dbms_utility.get_time包,精度为百分之1秒

 
 declare
    type row_num_type is table of number index by simple_integer;
    type row_text_type is table of varchar2(10) index by simple_integer;
    row_num_tab row_num_type;
    row_text_tab row_text_type;
    v_total number;
    v_start_time integer;
    v_end_time integer;
begin
    for i in 1..1000 loop
        row_num_tab(i) := i;
        row_text_tab(i) := 'row'||i;
    end loop;
    v_start_time := dbms_utility.get_time;
    dbms_output.put_line('v_start_time :'||v_start_time);
    for i in 1..1000 loop
        insert into test(row_num,row_text)
        values (row_num_tab(i),row_text_tab(i));
    end loop;
    v_end_time    := dbms_utility.get_time;
    dbms_output.put_line('v_end_time :'||v_end_time);
    dbms_output.put_line('duration of the for loop '||(v_end_time - v_start_time));
    select count(*) into v_total from test;
    dbms_output.put_line(v_total||' rows insterted');
    v_start_time := dbms_utility.get_time;
    dbms_output.put_line('v_start_time :'||v_start_time);
    forall i in 1..1000 
        insert into test(row_num,row_text)
        values (row_num_tab(i),row_text_tab(i));
    commit;
    v_end_time    := dbms_utility.get_time;
    dbms_output.put_line('v_end_time :'||v_end_time);
    dbms_output.put_line('duration of the forall statement '||(v_end_time - v_start_time));
    select count(*) into v_total from test;
    dbms_output.put_line(v_total||' rows insterted');
    commit;
end;
/
v_start_time :449982608
v_end_time :449982611
duration of the for loop 3
1000 rows insterted
v_start_time :449982611
v_end_time :449982611
duration of the forall statement 0
2000 rows insterted

1.1 SAVE EXECPTIONS选项

使用save exceptions选项能够实现:即使当对应的sql语句导致异常，forall语句仍旧能够继续执行。这些异常被存储在名为SQL%BULK_EXCEPTIONS的游标属性中。SQL%BULK_EXCEPTIONS游标属性是个记录集合，其中每个记录由两个字段组成：ERROR_INDEX和ERROR_CODE。ERROR_INDEX字段会存储发生异常的FORALL语句的迭代编号，ERROR_CODE会存储对应于抛出异常的oracle错误代码

可以是用SQL%BULK_EXCEPTIONS.COUNT来检索FORALL语句执行过程中所发生的异常数量。注意，一贯不会保存单个的错误消息，但是可以使用SQLERRM函数进行查询。

 
 TRUNCATE TABLE TEST;
DECLARE
  -- Define collection types and variables
  TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
  row_num_tab  row_num_type;
  row_text_tab row_text_type;
  -- Define user-defined exception and associated Oracle
  -- error number with it
  errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(errors, -24381);
BEGIN
  -- Populate collections
  FOR i IN 1 .. 10 LOOP
    row_num_tab(i) := i;
    row_text_tab(i) := 'row ' || i;
  END LOOP;
  -- Modify 1, 5, and 7 elements of the V_ROW_TEXT collection
  -- These rows will cause exception in the FORALL statement
  row_text_tab(1) := RPAD(row_text_tab(1), 11, ' ');
  row_text_tab(5) := RPAD(row_text_tab(5), 11, ' ');
  row_text_tab(7) := RPAD(row_text_tab(7), 11, ' ');
  -- Populate TEST table
  FORALL i IN 1 .. 10 SAVE EXCEPTIONS /* 要是不写这个save exceptions就会直接抛出错误
  ORA-12899: value too large for column "SCOTT"."TEST"."ROW_TEXT" (actual: 11, maximum: 10)*/
    INSERT INTO test
      (row_num, row_text)
    VALUES
      (row_num_tab(i), row_text_tab(i));
  COMMIT;
EXCEPTION
  WHEN errors THEN
    -- Display total number of exceptions encountered
    DBMS_OUTPUT.PUT_LINE('There were ' || SQL%BULK_EXCEPTIONS.COUNT ||
                         ' exceptions');
    -- Display detailed exception information
    FOR i in 1 .. 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));
    END LOOP;
END;
/
There were 3 exceptions
Record 1 caused error 1: 12899 ORA-12899: value too large for column  (actual: , maximum: )
Record 5 caused error 2: 12899 ORA-12899: value too large for column  (actual: , maximum: )
Record 7 caused error 3: 12899 ORA-12899: value too large for column  (actual: , maximum: )

1.2 INDICES OF

正如前面所提到的那样，使用INDICES OF选项，可以循环处理稀疏的集合，回想一下，这种集合也许是嵌套表，或者联合数组。

引用的是下标

 
 declare
  TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
  row_num_tab  row_num_type;
  row_text_tab row_text_type;
  v_total number;
begin
    FOR i IN 1 .. 10 LOOP
        row_num_tab(i) := i;
        row_text_tab(i) := 'row ' || i;
      END LOOP;
      row_num_tab.DELETE(1);row_text_tab.DELETE(1);
      row_num_tab.DELETE(3);row_text_tab.DELETE(3);
      row_num_tab.DELETE(5);row_text_tab.DELETE(5);
      FORALL i in INDICES OF row_num_tab
          insert into test(row_num,row_text)
          values(row_num_tab(i),row_text_tab(i));
      commit;
      select count(*) into v_total from test;
      dbms_output.put_line('There are '||v_total||' rows in the test table');
 end;
 /
There are 7 rows in the test table
PL/SQL procedure successfully completed.
scott@ORCL>select * from test;
   ROW_NUM ROW_TEXT
---------- ----------
         2 row 2
         4 row 4
         6 row 6
         7 row 7
         8 row 8
         9 row 9
        10 row 10
7 rows selected.

为让当前嵌套变得稀疏，删除第1,3,5元素，这样的化，FORALL语句会迭代7次，第7行数据会添加到TEST表。下面的输出可以说明这一点

here are 7 rows in the test table

PL/SQL procedure successfully completed.

1.3 VALUES OF选项

VALUES OF用于一种不同的情况：绑定数组可以是稀疏数组，也可以不是，但我只想使用该数组中元素的一个子集。那么我就可以使用VALUES OF来指向我希望在DML操作中使用的值。

VALUES OF最关键的是他引用的是特定集合中单个元素的值

 
 SQL> declare
  2    TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
  row_num_tab  row_num_type;
  row_text_tab row_text_type;
begin
FOR i IN 1 .. 10 LOOP
    row_num_tab(i) := i;
    row_text_tab(i) := 'row ' || i;
  END LOOP;
 11  
  row_num_tab.DELETE(1);row_text_tab.DELETE(1);
  row_num_tab.DELETE(3);row_text_tab.DELETE(3);
  row_num_tab.DELETE(5);row_text_tab.DELETE(5);
 15  
  FORALL i in values OF row_num_tab
  insert into test(row_num,row_text)
  values(row_num_tab(i),row_text_tab(i));
  commit;
 end;
 21   /
  TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
                       *
ERROR at line 2:
ORA-06550: line 2, column 24:
PLS-00667: Element type of associative array should be pls_integer or binary_integer
ORA-06550: line 16, column 15:
PL/SQL: Statement ignored

values of后面跟的集合必须是pls_integer?

事实上values of使用的是联合数组，他必须使用PLS_INTEGER或BINARY_INTEGER进行索引

   
   create table test_exc(row_num number,row_text varchar2(50));
DECLARE
   -- Define collection types and variables
   TYPE row_num_type  IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;
   TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
   TYPE exc_ind_type  IS TABLE OF PLS_INTEGER  INDEX BY PLS_INTEGER;
   row_num_tab  row_num_type;
   row_text_tab row_text_type;
   exc_ind_tab  exc_ind_type;
   -- Define user-defined exception and associated Oracle
   -- error number with it
   errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(errors, -24381);
BEGIN
   -- Populate collections
   FOR i IN 1..10 LOOP
      row_num_tab(i)  := i;
      row_text_tab(i) := 'row '||i;
   END LOOP;
   -- Modify 1, 5, and 7 elements of the V_ROW_TEXT collection
   -- These rows will cause exception in the FORALL statement
   row_text_tab(1) := RPAD(row_text_tab(1), 11, ' ');
   row_text_tab(5) := RPAD(row_text_tab(5), 11, ' ');
   row_text_tab(7) := RPAD(row_text_tab(7), 11, ' ');
   -- Populate TEST table
   FORALL i IN 1..10 SAVE EXCEPTIONS
      INSERT INTO test (row_num, row_text)
      VALUES (row_num_tab(i), row_text_tab(i));
COMMIT;
EXCEPTION
   WHEN errors THEN
      -- Populate V_EXC_IND_TAB collection to be used in the VALUES
      -- OF clause
      FOR i in 1.. SQL%BULK_EXCEPTIONS.COUNT LOOP
         exc_ind_tab(i) := SQL%BULK_EXCEPTIONS(i).error_index;
      END LOOP;
      -- Insert records that caused exceptions in the TEST_EXC
      -- table
      FORALL i in VALUES OF exc_ind_tab
         INSERT INTO test_exc (row_num, row_text)
         VALUES (row_num_tab(i), row_text_tab(i));
   COMMIT;
END;
/
scott@ORCL>select * from test;
   ROW_NUM ROW_TEXT
---------- ----------
         2 row 2
         3 row 3
         4 row 4
         6 row 6
         8 row 8
         9 row 9
        10 row 10
7 rows selected.
test表插入的值应该没什么好解释的
scott@ORCL>select * from test_exc;
   ROW_NUM ROW_TEXT
---------- --------------------------------------------------
         1 row 1
         5 row 5
         7 row 7
values of引用的是集合的值,所以i是在exc_ind_tab的值中循环
 exc_ind_tab(1) = 1
 exc_ind_tab(2) = 5
 exc_ind_tab(3) = 7
所以i in 1,5,7
row_num_tab(1) = 1;row_text_tab(1) = row 1;
row_num_tab(5) = 5;row_text_tab(5) = row 5;
row_num_tab(7) = 7;row_text_tab(7) = row 7;
所以一个批量插入FORALL 异常模板可以是这样
1.输出错误信息
2.将错误信息插入err_log错日志表
3.将出错的行，插入text_exc表
创建一个err_log表
create table err_log(id number,msg varchar2(500));
DECLARE
   errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(errors, -24381);
BEGIN
EXCEPTION
	WHEN errors THEN
		for i in 1..SQL%BULK_EXCEPTIONS.COUNT loop
    		dbms_output.put_line('line '||SQL%BULK_EXCEPTIONS(i).INDEX_CODE||' has error. error is '||sqlerrm(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    		insert into err_log values(SQL%BULK_EXCEPTIONS(i).INDEX_CODE,sqlerrm(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    		exc_ind_tab(i) := SQL%BULK_EXCEPTIONS(i).error_index;
		end loop;
		commit;
/* 把出问题的值插入test_exc表*/
		forall i in values of exc_ind_tab
			INSERT INTO test_exc (row_num, row_text) VALUES (row_num_tab(i), row_text_tab(i));
   		COMMIT;
END;
/

我的实验

实验1：

INDICES OF不是稀疏的也可以

 
 declare
  TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
  row_num_tab  row_num_type;
  row_text_tab row_text_type;
begin
    FOR i IN 1 .. 10 LOOP
        row_num_tab(i) := i;
        row_text_tab(i) := 'row ' || i;
      END LOOP;
      row_num_tab.DELETE(1);row_text_tab.DELETE(1);
      row_num_tab.DELETE(3);row_text_tab.DELETE(3);
      row_num_tab.DELETE(5);row_text_tab.DELETE(5);
      FORALL i in INDICES OF row_num_tab
          insert into test(row_num,row_text)
          values(row_num_tab(i),row_text_tab(i));
      commit;
 end;
 /
 declare
  TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
  row_num_tab  row_num_type;
  row_text_tab row_text_type;
begin
    FOR i IN 1 .. 10 LOOP
        row_num_tab(i) := i;
        row_text_tab(i) := 'row ' || i;
      END LOOP;
      FORALL i in INDICES OF row_num_tab
          insert into test(row_num,row_text)
          values(row_num_tab(i),row_text_tab(i));
      commit;
 end;
 /
PL/SQL procedure successfully completed.
SQL> select * from test;
   ROW_NUM ROW_TEXT
---------- ----------
         1 row 1
         2 row 2
         3 row 3
         4 row 4
         5 row 5
         6 row 6
         7 row 7
         8 row 8
         9 row 9
        10 row 10
10 rows selected.

实验2：

 
 declare
  TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
  row_num_tab  row_num_type;
  row_text_tab row_text_type;
begin
        row_num_tab(1) := 10;
        row_text_tab(1) := 'row ' || 10;
        row_num_tab(2) := 9;
        row_text_tab(2) := 'row ' || 9;
        row_num_tab(3) := 8;
        row_text_tab(3) := 'row ' || 8;
        row_num_tab(4) := 7;
        row_text_tab(4) := 'row ' || 7;
        row_num_tab(5) := 6;
        row_text_tab(5) := 'row ' || 6;
        row_num_tab(6) := 5;
        row_text_tab(6) := 'row ' || 5;
        row_num_tab(7) := 4;
        row_text_tab(7) := 'row ' || 4;
        row_num_tab(8) := 3;
        row_text_tab(8) := 'row ' || 3;
        row_num_tab(9) := 2;
        row_text_tab(9) := 'row ' || 2;
        row_num_tab(10) := 1;
        row_text_tab(10) := 'row ' || 1;
      FORALL i in INDICES OF row_num_tab
          insert into test(row_num,row_text)
          values(row_num_tab(i),row_text_tab(i));
      commit;
 end;
 /
 SQL> select * from test;
   ROW_NUM ROW_TEXT
---------- ----------
        10 row 10
         9 row 9
         8 row 8
         7 row 7
         6 row 6
         5 row 5
         4 row 4
         3 row 3
         2 row 2
         1 row 1
10 rows selected.

FORALL i in INDICES OF row_num_tab

这个i是在 row_num_tab集合的下表中循环，下表就是1~10

否则如果是值得话，第一行就是row_text_tab(10)，显然不是这样

实验3：

 
 declare
  TYPE row_num_type IS TABLE OF pls_integer INDEX BY PLS_INTEGER;
  TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
  row_num_tab  row_num_type;
  row_text_tab row_text_type;
begin
        row_num_tab(1) := 10;
        row_text_tab(1) := 'row ' || 10;
        row_num_tab(2) := 9;
        row_text_tab(2) := 'row ' || 9;
        row_num_tab(3) := 8;
        row_text_tab(3) := 'row ' || 8;
        row_num_tab(4) := 7;
        row_text_tab(4) := 'row ' || 7;
        row_num_tab(5) := 6;
        row_text_tab(5) := 'row ' || 6;
        row_num_tab(6) := 5;
        row_text_tab(6) := 'row ' || 5;
        row_num_tab(7) := 4;
        row_text_tab(7) := 'row ' || 4;
        row_num_tab(8) := 3;
        row_text_tab(8) := 'row ' || 3;
        row_num_tab(9) := 2;
        row_text_tab(9) := 'row ' || 2;
        row_num_tab(10) := 1;
        row_text_tab(10) := 'row ' || 1;
      FORALL i in values OF row_num_tab
          insert into test(row_num,row_text)
          values(row_num_tab(i),row_text_tab(i));
      commit;
 end;
 /
 PL/SQL procedure successfully completed.
SQL> select * from test;
   ROW_NUM ROW_TEXT
---------- ----------
         1 row 1
         2 row 2
         3 row 3
         4 row 4
         5 row 5
         6 row 6
         7 row 7
         8 row 8
         9 row 9
        10 row 10
10 rows selected.