oracle快速删除表记录forall,【PL/SQL】迅速批量插入 FORALL/ BULK COLLECT

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

为什么Forall会比单纯的Insert语句要快？

适用范围以及如何使用？

补充知识点

执行SQL语句的逻辑过程：

这里引入一个重要的概念上下文交换。

当PL/SQL运行时引擎处理一块代码时，它使用PL/SQL引擎来执行过程化的代码，而将SQL语句发送给SQL引擎来执行；SQL引擎执行完毕后，将结果再返回给PL/SQL引擎。这种在PL/SQL引擎和SQL引擎之间的交互，称为上下文交换(context switch)。

缺点隐忧：每发生一次交换，就会带来一定的额外开销。

如下如所示：

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

FORALL，用于增强PL/SQL引擎到SQL引擎的交换。

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时，应该遵循如下规则：

FORALL语句的执行体，必须是一个单独的DML语句，比如INSERT，UPDATE或DELETE。

不要显式定义index_row，它被PL/SQL引擎隐式定义为PLS_INTEGER类型，并且它的作用域也仅仅是FORALL。

这个DML语句必须与一个集合的元素相关，并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。

lower_bound和upper_bound之间是按照步进 1 来递增的。

在sql_statement中，不能单独地引用集合中的元素，只能批量地使用集合。

在sql_statement中使用的集合，下标不能使用表达式。