【PL/SQL】迅速批量插入 FORALL/ BULK COLLECT

蜗牛

看完这章你会学习到以下内容:

  1. 为什么Forall会比单纯的Insert语句要快?
  2. 适用范围以及如何使用?
  3. 补充知识点

执行SQL语句的逻辑过程:
这里引入一个重要的概念上下文交换

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

如下如所示:
上下文交换


Q:为什么Forall会比单纯的Insert语句要快?

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

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

20150616225900936.gif


Q:什么情况下,多少数据量下比较适用?
至少百万级别或以上,效果会比较明显。


Forall 语法说明介绍:

1.index_name:一个无需声明的标识符,作为集合下标使用。
2.lower_bound .. upper_bound:数字表达式,来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次。
3.INDICES OF collection_name:用于指向稀疏数组的实际下标。跳过没有赋值的元素,例如被 DELETE 的元素,NULL 也算值。
4.VALUES OF index_collection_name:把该集合中的值当作下标,且该集合值的类型只能是 PLS_INTEGER/BINARY_INTEGER。
5.SAVE EXCEPTIONS:可选关键字,表示即使一些DML语句失败,直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。
6.dml_statement:静态语句,例如:UPDATE或者DELETE;或者动态(EXECUTE IMMEDIATE)DML语句。

FORALL的使用
示例所使用表结构:

 CREATE TABLE tmp_tab(
  id NUMBER(5),
  name VARCHAR2(50)
);    -- 创建一张新表
--批量插入
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;

 --用临时表数据导入到Tmp_tab上。
  FORALL i IN 1..tb_table.count
    INSERT INTO  tmp_tab VALUES tb_table(i); 
END;

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中使用的集合,下标不能使用表达式。

补充知识点:
1.INDEX BY BINARY_INTEGER 什么意思?
参考路径记录表TABLE中 INDEX BY BINARY_INTEGER 的作用
2.创建索引表,创建语法
图片.png
3.Forall还能批量更新,批量删除。
参考路径Oracle数据库之FORALL与BULK COLLECT语句
参考路径oracle forall关键字进行批量操作

阅读 1.2k

21 声望
8 粉丝
0 条评论
21 声望
8 粉丝
文章目录
宣传栏