plsql forall 详解

1 概述

1. 作用：提高在 pl/sql 块中，处理 dml 语句的效率

2. "上下文切换": 
   (1) 在 pl/sql 中，'pl/sql 块' 由 'pl/sql 引擎' 处理
   (2) 而其中的 'sql 语句' 则由 'pl/sql 引擎' 发送至 'sql 引擎处理' 
   (3) 后者处理完毕后再向前者返回数据，两者之间的通信称为 "上下文切换"，
       过多的上下文切换将带来过量的性能负载。

3. pl/sql 和 sql 引擎之间的 交互（"上下文切换"）
   (1) forall      : 用于增强 'pl/sql 引擎' -> 'sql 引擎' 的交互
   (2) bulk collect: 用于增强 'sql 引擎' -> 'pl/sql 引擎' 的交互

扩展： pl/sql bulk collect 详解

1.1 图示

举个例子: 在 pl/sql 中处理 10 个 update 语句
(1) 一般的 : 'pl/sql 引擎' '每次' 发送 1 个 update 语句给 'sql 引擎' 处理， "上下文切换" 1 次，共计 10 次
(2) forall: 'pl/sql 引擎' '一次性' 发送 10 个 update 语句给 'sql 引擎' 处理， "上下文切换" 1 次，共计 1 次

2 forall 的三种用法

1. 三种用法
   (1) forall i in index_min .. index_max: '下标必须存在'，否则报错
   (2) forall i in indices of collection : 若下标 '不存在，就跳过'（上述的扩展）
   (3) forall i in values of collection  : 仅插入 values 中 '存在的记录'
 
2. 注意：forall 后只能紧跟 1 条 dml 语句

3. 基础数据准备
   create table stu_info (
     sno   number(10),
     sname varchar2(10)
   );

2.1 forall i in index_min … index_max

-- 注意：'下标必须存在'，否则报错
declare
  type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
  v_stu_info_rows stu_info_table;
begin
  -- 制造数据
  for i in 1 .. 3 loop
    v_stu_info_rows(i).sno := i;
    v_stu_info_rows(i).sname := 'a' || i;
  end loop;

  -- 演示报错（下标为 2 的元素不存在）
  -- v_stu_info_rows.delete(2);

  -- insert 演示（update、delete 同理）
  forall i in v_stu_info_rows.first .. v_stu_info_rows.last
    insert into stu_info values v_stu_info_rows (i); 
    
  -- commit;
end;

测试结果：

sno	sname
1	a1	
2	a2	
3	a3	

2.2 forall i in indices of collection

-- 注意：若下标 '不存在，就跳过'
declare
  type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
  v_stu_info_rows stu_info_table;
begin
  -- 制造数据
  for i in 1 .. 3 loop
    v_stu_info_rows(i).sno := i;
    v_stu_info_rows(i).sname := 'a' || i;
  end loop;

  -- 演示报错（下标为 2 的元素不存在）
  v_stu_info_rows.delete(2);

  -- insert 演示（update、delete 同理）
  forall i in indices of v_stu_info_rows
    insert into stu_info values v_stu_info_rows (i); 
    
  -- commit;
end;

测试结果：（跳过了 sno = 2 的记录）

sno	sname
1	a1
3	a3

2.3 forall i in values of collection

-- 注意： 仅插入 values 中 '存在的记录'
declare
  type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
  type index_table is table of pls_integer;

  v_stu_info_rows stu_info_table;
  v_index_table   index_table;
begin
  -- 制造数据
  for i in 1 .. 3 loop
    v_stu_info_rows(i).sno := i;
    v_stu_info_rows(i).sname := 'a' || i;
  end loop;

  -- values 记录列表
  v_index_table := index_table(1, 3);

  -- insert 演示（update、delete 同理）
  forall i in values of v_index_table
    insert into stu_info values v_stu_info_rows (i);

  -- commit;
end;

测试结果：(没有 sno = 2 的记录）

sno	sname
1	a1
3	a3

提示：一般用 pls_integer，除非批处理业务量大于 21,4748,3647，才考虑用 binary_integer

关键字	解释
pls_integer	检索速度快，超过最大长度时会溢出（最大长度：-2^31 至 2^31 - 1）
binary_integer	检索速度一般，超过最大长度是不会溢出

3 forall 与 for 效率对比

结论：

dml 执行效率： forall > for

数据准备：

-- 清空数据，方便测试
truncate table stu_info; 

create table stu_info_for as select * from stu_info where 1 = 2; -- for
create table stu_info_forall as select * from stu_info where 1 = 2; -- forall

效率验证：（50W 数据插入）

declare
  type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
  v_stu_info_rows stu_info_table;
  v_init_time     pls_integer;
  v_for_time      pls_integer;
  v_forall_time   pls_integer;
begin
  -- 制造数据
  for i in 1 .. 500000 loop
    v_stu_info_rows(i).sno := i;
    v_stu_info_rows(i).sname := 'a' || i;
  end loop;

  -- 初始化时间
  v_init_time := dbms_utility.get_time;

  -- 1 演示：for 
  for i in 1 .. 500000 loop
    insert into stu_info_for values v_stu_info_rows (i);
  end loop;

  v_for_time := dbms_utility.get_time;

  -- 2 演示：forall
  forall i in 1 .. 500000
    insert into stu_info_forall values v_stu_info_rows (i);

  v_forall_time := dbms_utility.get_time;

  dbms_output.put_line('for 用时：' || to_char(v_for_time - v_init_time));
  dbms_output.put_line('forall 用时：' || to_char(v_forall_time - v_for_time));
   -- commit;
exception
  when others then
    dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

测试结果：（forall > for，且数据量越大，差异越大）

for 用时：2326
forall 用时：30

提示：dbms_utility.get_time -> 100 = 1s
验证思路：Oracle dbms_utility.get_time 用法