plsql bulk collect 详解

1 概述

1. 作用：提高 'sql 引擎' 将 sql 语句执行情况返回给 'pl/sql 引擎' 的效率
   (1) bulk collect 会 '一次性' 将结果集绑定到一个 '集合变量' 中,
       并从 sql 引擎发送到 pl/sql 引擎，大大减少 "上下文切换"
   
2. "上下文切换": 
   (1) 在 pl/sql 中，'pl/sql 块' 由 'pl/sql 引擎' 处理
   (2) 而其中的 'sql 语句' 则由 'pl/sql 引擎' 发送至 'sql 引擎处理' 
   (3) 后者处理完毕后再向前者返回数据，两者之间的通信称为 "上下文切换"，
       过多的上下文切换将带来过量的性能负载。

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

扩展：plsql forall 详解

1.1 图示

2 三种使用方式

1. bulk collect 三种使用方式
   (1) select into   : 常规用法
   (2) fetch into    ：和游标一起使用
   (3) returning into：和 insert、update、delete 一起使用  

2. 注意
   (1) bulk collect into 后面一定是个 table 类型

基础数据准备:

create table stu_info (
  sno    number(3),
  sname  varchar2(30),
  sex   varchar(2)
);

insert into stu_info(sno, sname, sex) values(1, '瑶瑶', '女');
insert into stu_info(sno, sname, sex) values(2, '优优', '男');
insert into stu_info(sno, sname, sex) values(3, '倩倩', '女');
commit;

2.1 在 select into 中

declare
  type stu_info_table is table of scott.stu_info%rowtype;
  v_stu_info_rows stu_info_table;
begin
  -- 将结果集 一次性绑定 到记录记录变量中
  select si.sno, si.sname, si.sex
    bulk collect
    into v_stu_info_rows
    from stu_info si;

  -- 输出验证
  for i in v_stu_info_rows.first .. v_stu_info_rows.last loop
    dbms_output.put_line('sno: ' || v_stu_info_rows(i).sno || ', sname: ' || v_stu_info_rows(i)
                         .sname || ', sex: ' || v_stu_info_rows(i).sex);
  end loop;
end;

输出结果：

sno: 1, sname: 瑶瑶, sex: 女
sno: 2, sname: 优优, sex: 男
sno: 3, sname: 倩倩, sex: 女

2.2 在 fetch into 中

DECLARE
  TYPE stu_info_table IS TABLE OF scott.stu_info%ROWTYPE;
  v_stu_info_rows stu_info_table;

  CURSOR cur_stu_info IS
    SELECT si.sno, si.sname, si.sex FROM stu_info si;
BEGIN
  OPEN cur_stu_info;
 
  -- 效率高于单条 fetch
  FETCH cur_stu_info BULK COLLECT
    INTO v_stu_info_rows LIMIT 3; -- limit 3：每次插入 3 条 [可选项]
    
  -- 输出验证
  FOR i IN v_stu_info_rows.first .. v_stu_info_rows.last LOOP
    dbms_output.put_line('sno: ' || v_stu_info_rows(i).sno || ', sname: ' || v_stu_info_rows(i)
                         .sname || ', sex: ' || v_stu_info_rows(i).sex);
  END LOOP;

  CLOSE cur_stu_info;
END;

输出结果：

sno: 1, sname: 瑶瑶, sex: 女
sno: 2, sname: 优优, sex: 男
sno: 3, sname: 倩倩, sex: 女

2.3 在 returning into 中

declare
  type stu_info_table is table of scott.stu_info%rowtype;
  v_stu_info_rows stu_info_table;
begin
  -- delete 验证 (update、insert 同理)
  delete from stu_info si
   where si.sex = '女' return si.sno, si.sname, si.sex bulk collect into
   v_stu_info_rows;

  dbms_output.put_line('删除了 ' || sql%rowcount || ' 行记录');
  -- commit;

  -- 输出验证
  if v_stu_info_rows.count > 0 then
    for i in v_stu_info_rows.first .. v_stu_info_rows.last loop
      dbms_output.put_line('sno: ' || v_stu_info_rows(i).sno ||
                           ', sname: ' || v_stu_info_rows(i).sname ||
                           ', sex: ' || v_stu_info_rows(i).sex);
    end loop;
  end if;
end;

输出结果：

删除了 2 行记录
sno: 1, sname: 瑶瑶, sex: 女
sno: 3, sname: 倩倩, sex: 女

3 扩展：forall + bulk collect 综合运用

基础数据准备：

truncate table stu_info ;

insert into stu_info(sno, sname, sex) values(1, '瑶瑶', '女');
insert into stu_info(sno, sname, sex) values(2, '优优', '男');
insert into stu_info(sno, sname, sex) values(3, '倩倩', '女');
commit;

-- 测试表
create table stu_info_temp as select * from stu_info where 1 = 2;

开发实战：（将 stu_info 中的数据同步至 stu_info_temp）

-- 建议在 '测试窗口' Debug 瞅瞅下列执行步骤
DECLARE
  -- 声明游标
  CURSOR cur_stu_info IS
    SELECT si.sno, si.sname, si.sex FROM stu_info si;
  -- 定义基于游标类型的嵌套表 (也可用 RECORD 替换，只是写法会稍微麻烦点)
  TYPE stu_info_table IS TABLE OF cur_stu_info%ROWTYPE;
  -- 声明变量
  v_stu_info_rows stu_info_table;
BEGIN
  -- 开启游标
  OPEN cur_stu_info;

  LOOP
    -- 获取 cur_stu_info 数据
    FETCH cur_stu_info BULK COLLECT
      INTO v_stu_info_rows LIMIT 2; -- 数据有限,仅做测试,一般限制 500 左右
  
    EXIT WHEN v_stu_info_rows.count = 0; -- 注意此时游标退出使用了 xx.count，而不是 xx%notfound  
  
    -- 批量插入
    FORALL i IN 1 .. v_stu_info_rows.count
      INSERT INTO stu_info_temp VALUES v_stu_info_rows (i);
  END LOOP;

  -- 关闭游标
  CLOSE cur_stu_info;

  -- 提交数据
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- 异常时，也要关闭游标
    IF cur_stu_info%ISOPEN THEN
      CLOSE cur_stu_info;
    END IF;
  
    dbms_output.put_line(dbms_utility.format_error_backtrace);
    dbms_output.put_line(SQLCODE || ', ' || SQLERRM);
END;