PLSQL迅速批量插入-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 引擎来执行，最大限度地减少上下文交互所带来的开销。

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. 创建索引表, 创建语法

3.Forall 还能批量更新，批量删除。
参考路径 Oracle 数据库之 FORALL 与 BULK COLLECT 语句
参考路径 oracle forall 关键字进行批量操作