FORALL使用--insert/delete/update操作的批绑定Bulk Binding

最新推荐文章于 2023-03-22 12:35:01 发布

pursuer211

最新推荐文章于 2023-03-22 12:35:01 发布

阅读量4.4k

点赞数

分类专栏： Oracle 文章标签： Oracle FORALL

本文链接：https://blog.csdn.net/pursuer211/article/details/17851625

版权

Oracle 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

从 Oracle8i 开始，出现了 FORALL 语句，可以帮助我们更快地执行 DML 语句。FORALL是Oracle在PL/SQL中提供的一种批量处理语法。它提供了比传统for loop更好的处理性能优势。两者的差异主要体现在处理引擎上下文切换上的性能损耗优势。在PL/SQL语句中出现语句，PL/SQL引擎会将SQL语句传递转给SQL引擎进行处理。SQL引擎处理后再将结果返回给PL/SQL引擎。这个过程我们称之为上下文切换（context switch）。在for语句运行的时候，伴随着频繁的上下文切换动作。使用FORALL，就可以避免出现这种情况。

在使用FORALL的时候，PL/SQL与SQL引擎的交互只有一次。所有的语句参数都是一次性的传递给SQL进行执行。这样，上下文切换动作的损耗就能得到节省。

当要在 Oracle 中进行批量 INSERT、UPDATE 和 DELETE 操作时，可以使用 FORALL 语句。与for语句语法格式不同的是：FORALL没有loop和end loop关键字配对。也就意味着forall语句后面只能跟一个SQL语句调用。

提醒：因为FORALL功能是随着Oracle版本的演进不断发展的，不同Oracle版本该功能会有所变化，所以本文除无特别说明外，举例的代码都是在Oracle 11g的环境下正常运行的。

1、FORALL使用语法

下面介绍FORALL三种语法的介绍，其中INDICES OF和VALUES OF是Oracle 10g引入的新特性，就是说在Oracle 10g之前，FORALL只能遍历下标连续的数组。

语法1：

FORALL 变量 IN 下限..上限
  sql 语句;

说明：
    1. 变量：是被遍历的的数组元素的下标。
    2. 下标必须是连续的，否则执行会报错。
    3. 执行的sql语句只能有一个。
    4. 在oracle 11g之前，数组是不能使用ROWTYPE和RECORD类型的，只是使用基本类型的数组。

语法2：

FORALL 变量 IN INDICES OF(跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值) 集合
  [BETWEEN 下限 AND 上限]
  sql 语句;

说明：
    1. 变量：是被遍历的的数组元素的下标。
    2. INDICES OF：可以是循环跳过没有赋值的元素，注意：被赋予NULL也算是有值。
    3. BETWEEN 下限 AND 上限：该子句是可选的，作用是把子句的'下限'到‘上限’范围之间的数值与数组元素下标做个交集，并遍历。这个交集可能是数组的全部元素，也可能是部分元素，或是空。如果不指定，就遍历数组全部元素。
    4. 执行的sql语句只能是一个。

语法3：

FORALL 变量 IN VALUES OF 集合
  sql 语句;

说明：
     1.  变量：被遍历元素的值，且该集合值的类型只能是PLS_INTEGER或是BINARY_INTEGER。
     2.  执行的sql只能是一个。

2、语法1使用说明

通过语法1的方式遍历数组，数组下标的上限和下限之间的数字必须是连续的，否则会报错。
下面以一个例子来说明FORALL语法1的使用：

1. 创建t_student表

create table t_student(
  gid number(38),
  name varchar2(100)
);

2. 批量插入数据

declare
  type stu_table_type is table of t_student%rowtype
    index by binary_integer;
  stu_table stu_table_type;
begin
  for i in 1..10 loop
    stu_table(i).gid:=i;
    stu_table(i).name:='NAME'||i;
  end loop;
  forall i in stu_table.first..stu_table.last
    insert into t_student values stu_table(i);
  commit;  
end;

如果stu_table数组中的数据下标不连续，比如下面的代码：

declare
  type stu_table_type is table of t_student%rowtype
    index by binary_integer;
  stu_table stu_table_type;
begin
  for i in 1..10 loop
    stu_table(i).gid:=i;
    stu_table(i).name:='NAME'||i;
  end loop;
  stu_table.delete(2);--删除数组第二个元素
  forall i in stu_table.first..stu_table.last
    insert into t_student values stu_table(i);
  commit;  
end;

执行上面代码会发生如下错误：ORA-22160:element at index [2] does not exist。

在oracle 10g之前，Oracle一致有这种限制，自从oracle 10g开始，数据库增加了indices of和values of两个子句，成功的解决了这个问题。下面分别介绍下这个两个子句语法的使用。

3、语法2使用说明

declare
  type student_tbl_type is table of t_student%rowtype
    index by binary_integer;
  student_tbl student_tbl_type;
begin
  for i in 1..10 loop
    student_tbl(i).gid:=i;
    student_tbl(i).name:='NAME'||i;
  end loop;
  student_tbl.delete(3);
  student_tbl.delete(6);
  student_tbl.delete(9);--删除3,6,9三个元素
  forall i in indices of student_tbl
    insert into t_student values student_tbl(i);
  commit;  
end;

查看执行结果：

SQL> select t.gid, t.name from T_STUDENT t;
                                    GID NAME
--------------------------------------- --------------------------------------------------------------------------------
                                      1 NAME1
                                      2 NAME2
                                      4 NAME4
                                      5 NAME5
                                      7 NAME7
                                      8 NAME8
                                     10 NAME10
7 rows selected

从执行结果可见：插入的元素虽然没有3,6,9三个元素，即数组是不连续的，通过indices of 子句，可以实现不连续数组的FORALL循环操作。

下面通过使用"between 下限 and 上限"子句举例：

declare
  type student_tbl_type is table of t_student%rowtype
    index by binary_integer;
  student_tbl student_tbl_type;
begin
  for i in 1..10 loop
    student_tbl(i).gid:=i;
    student_tbl(i).name:='NAME'||i;
  end loop;
  student_tbl.delete(3);
  student_tbl.delete(6);
  student_tbl.delete(9);--删除3,6,9三个元素
  forall i in indices of student_tbl between 5 and 18  --指定5到18范围内下标数组元素
    insert into t_student values student_tbl(i);
  commit;  
end;

查看执行结果：

SQL> select t.gid, t.name from T_STUDENT t;
                                    GID NAME
--------------------------------------- --------------------------------------------------------------------------------
                                      5 NAME5
                                      7 NAME7
                                      8 NAME8
                                     10 NAME10

从上面可以看出插入数据的数组是between and子句和数组元素的交集。

4、语法3使用说明

把该集合中的值当作下标，且该集合值的类型只能是PLS_INTEGER或BINARY_INTEGER。

declare
  type index_poniter_type is table of pls_integer;
  index_poniter index_poniter_type;
  type student_tbl_type is table of t_student%rowtype
    index by binary_integer;
  student_tbl student_tbl_type;
begin
  index_poniter:=index_poniter_type(1,3,5,7);
  for i in 1..10 loop
    student_tbl(i).gid:=i;
    student_tbl(i).name:='NAME'||i;
  end loop;
  forall i in values of index_poniter
    insert into t_student values student_tbl(i);
  commit;
end;

查看执行结果：

SQL> select t.gid, t.name from T_STUDENT t;
                                    GID NAME
--------------------------------------- --------------------------------------------------------------------------------
                                      1 NAME1
                                      3 NAME3
                                      5 NAME5
                                      7 NAME7

5、FORALL中的SQL语句批绑定

在介绍内容之前先举一个例子：

declare
  v_gid t_student.gid%TYPE;
  v_name t_student.name%TYPE;
begin
  v_name := 'CHENZHEN';
  forall i in 1..10
    update t_student set name = v_name where gid = i;
  commit;  
end;

    执行这段代码会报错，错误信息有两个：
    1）PLS-00430:FORALL interation variable I is not allowed in this context
    2）PLS-00435:DML statement without BULK In-BIND cannot be used inside FORALL。


    从错误信息可知：
     1） 在上下文中不允许使用 FORALL 循环变量i，循环变量i只能作为被遍历数组的下标来使用。
     2）没有批绑定的的SQL语句是不能放在FORALL中的。那么什么是绑定，什么又是批绑定？  
　   在SQL语句中，为PL/SQL变量指定值称为挷定(binding)；在DML语句中操作数组，这个过程称为批挷定（bulk binding）。 
　   批挷定包括： 
　　 1．带INSERT，UPDATE和DELETE语句的批挷定：在FORALL语句中嵌入SQL语句；
　　 2．带SELECT语句的批挷定：在SELECT语句中用BULK COLLECT 语句代替INTO。


     在FORALL中的SQL语句一定要有对数组的操作，而且循环变量只能作为数组的下标来使用，如下面的语句：

declare
  TYPE name_type is table of t_student.name%TYPE index by binary_integer;
  TYPE gid_type is table of t_student.gid%TYPE index by binary_integer;
  v_name name_type;
  v_gid gid_type;
begin
  for i in 1..10 loop
    v_name(i) := 'CHENZHEN';
    v_gid(i) := i;
  end loop;

  forall i in 1..10
    update t_student set name = v_name(i) where gid = v_gid(i);
  commit;  
end;

6、FORALL 中的 RETURNING

FORALL 中的 RETURNING 必须使用 BULK COLLECT INTO

如下代码所示：

declare
  type student_tbl_type is table of t_student%rowtype
    index by binary_integer;
  type gid_tbl_type is table of number
    index by binary_integer;
  student_tbl student_tbl_type;
  gid_tbl  gid_tbl_type;
  
begin
  for i in 1..10 loop
    student_tbl(i).gid:=i;
    student_tbl(i).name:='NAME'||i;
  end loop;
  forall i in 1..10
    delete from t_student where gid = student_tbl(i).gid
       returning gid bulk collect into gid_tbl;
  commit;
  
 for i in 1..gid_tbl.count loop
    dbms_output.put_line(gid_tbl(i));
  end loop;  
end;

7、Oracle不同版本FORALL的使用差异

在oracle 11g之前，Bulk Binding与RECORD、%ROWTYPE是不能在一块使用的，也就是说，BULK In-BIND只能与简单类型的数组一块使用，这样导致如果有多个字段需要用BULK In-BIND来处理。虽然oracle 11g已经解决了这个问题，但是如果使用的是11g之前的版本，一定要注意，否则程序将会报错。

如下面这段代码在oracle 11g下没有任何问题，但是在oracle 10g即之前版本，将无法编译通过。

declare
  type stu_table_type is table of t_student%rowtype   --%ROWTYPE类型的数组
    index by binary_integer;
  stu_table stu_table_type;
begin
  for i in 1..10 loop
    stu_table(i).gid:=i;
    stu_table(i).name:='NAME'||i;
  end loop;
  forall i in stu_table.first..stu_table.last
    insert into t_student values stu_table(i);
  commit;  
end;

    错误信息是：PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records（PLS-00436:实施限制:不能引用记录的BULK In-BIND表的字段）。
    虽然oracle 10g及之前的版本,Bulk Binding与RECORD、%ROWTYPE是不能在一块使用,我们可以通过下面实现来解决，代码如下：

declare
  type name_table_type is table of t_student.name%type index by binary_integer; --基本类型的数组
  type gid_table_type is table of t_student.gid%type index by binary_integer; --基本类型的数组

  v_name_table name_table_type;
  v_gid_table gid_table_type;
begin
  for i in 1..10 loop
    v_gid_table(i) :=i;
    v_name_table(i) :='NAME'||i;
  end loop;
  forall i in v_gid_table.first..v_gid_table.last
    insert into t_student(gid, name) values(v_gid_table(i), v_name_table(i));
  commit;  
end;

8、FOR和FORALL性能比较

上面通过理论分析知道FORALL执行效率比FOR高，究竟高多少，我们还没有见识，下面就通过一段代码对他们做个比较：

DECLARE
  TYPE gid_tbl_type IS TABLE OF t_student.gid%TYPE INDEX BY PLS_INTEGER;
  TYPE name_tbl_type IS TABLE OF t_student.name%TYPE INDEX BY PLS_INTEGER;
  gid_tbl gid_tbl_type;
  name_tbl name_tbl_type;
  t1 INTEGER;
  t2 INTEGER;
  t3 INTEGER;
BEGIN
  FOR j IN 1..100000 LOOP  -- load index-by tables
     gid_tbl(j) := j;
     name_tbl(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;
  t1 := DBMS_UTILITY.get_time;
  FOR i IN 1..50000 LOOP  -- use FOR loop
     INSERT INTO t_student(gid, name) VALUES (gid_tbl(i), name_tbl(i));
  END LOOP;
  COMMIT;
  t2 := DBMS_UTILITY.get_time;
  FORALL i IN 50001..100000  -- use FORALL statement
     INSERT INTO t_student(gid, name) VALUES (gid_tbl(i), name_tbl(i));
  COMMIT;   
  t3 := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
  DBMS_OUTPUT.PUT_LINE('---------------------');
  DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100));
  DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)/100));
END;

执行结果：

Execution Time (secs)
---------------------
FOR loop: 2.21
FORALL:   .3

同样执行50000条insert语句，FOR花了2.21秒，FORALL花了0.3秒，也就是FORALL的执行效率是FOR的约7.4倍。这种效率上的优越性还是很高的。

9、FORALL的Sql%rowcount和sql%bulk_rowcount属性

在forall操作中，Sql%rowcount表示commit之前（所以该属性要执行在commit之前，否则获得将是改变0条数据）被影响的记录总行数，而sql%bulk_rowcount(i)则表示FORALL遍历每一个元素，DML操作所影响的行数，该值是存在一个集合里，FORALL中的第n条dml语句处理的行数存储在该集合的第n个元素中。

declare
  TYPE name_type is table of t_student.name%TYPE index by binary_integer;
  TYPE gid_type is table of t_student.gid%TYPE index by binary_integer;
  v_name name_type;
  v_gid gid_type;
begin
  for i in 1..10 loop
    v_name(i) := 'CHENZHEN'||i;
    v_gid(i) := i;
  end loop;
  forall i in 1..10
    update t_student set name = v_name(i) where gid = v_gid(i); 
  dbms_output.put_line(sql%rowcount||'行记录被更新！');
  for i in 1 .. v_name.count loop
    dbms_output.put_line('名称为'||v_name(i)||'的记录共有'||sql%bulk_rowcount(i)||'条被更新！');
  end loop;
  commit; 
end;

执行结果如下：

10行记录被更新
名称为CHENZHEN1的记录共有1条被更新
名称为CHENZHEN2的记录共有1条被更新
名称为CHENZHEN3的记录共有1条被更新
名称为CHENZHEN4的记录共有1条被更新
名称为CHENZHEN5的记录共有1条被更新
名称为CHENZHEN6的记录共有1条被更新
名称为CHENZHEN7的记录共有1条被更新
名称为CHENZHEN8的记录共有1条被更新
名称为CHENZHEN9的记录共有1条被更新
名称为CHENZHEN10的记录共有1条被更新