Oracle中如何使用FORALL替换循环实现INSERT与UPDATE操作

改造后使用FORALL的存储过程 #

首先修正原代码中INSERT语句漏写i.前缀的bug，同时替换逐行循环为批量操作：

create or replace procedure insert_charge is
  -- 定义记录类型存储需要处理的字段
  type emp_rec is record(
    name emp.name%type,
    hire_date emp.hire_date%type,
    salary emp.salary%type
  );
  -- 定义基于上面记录的集合类型
  type emp_tab is table of emp_rec index by pls_integer;
  v_emp_data emp_tab;
begin
  -- 批量查询符合条件的数据存入集合
  select t.name, t.hire_date, t.salary
  bulk collect into v_emp_data
  from emp t
  where t.create_date >= (sysdate - 30)
    and t.salary = 0;

  -- 批量插入charge表
  forall i in 1..v_emp_data.count
    insert into charge(name, hire_date, salary)
    values(v_emp_data(i).name, v_emp_data(i).hire_date, v_emp_data(i).salary);

  -- 批量更新emp表状态
  forall i in 1..v_emp_data.count
    update emp l
    set l.status = 1
    where l.name = v_emp_data(i).name
      and l.status = 0
      and l.hire_date = v_emp_data(i).hire_date;

  -- 统一提交，原逐行提交的逻辑会严重影响性能，且无法保证两次操作的原子性
  commit;
exception
  when others then
    rollback;
    -- 可按需添加异常抛出/日志打印逻辑
    raise;
end insert_charge;

关键改动说明 #

• 先自定义记录和集合类型，通过BULK COLLECT一次性把所有符合条件的emp数据加载到内存集合中，避免逐行查询的上下文切换开销
• 用FORALL代替普通FOR循环执行批量DML，减少PL/SQL引擎到SQL引擎的交互次数，数据量越大性能提升越明显
• 把循环内的逐行提交改成最终统一提交，既保证了插入和更新操作的原子性，也避免了频繁提交的IO开销

如果你需要保留原逻辑中逐行提交的特性（极端场景下避免大事务），可以将集合拆分成分批处理，每批执行完FORALL后提交一次即可，不需要回到逐行循环的写法。

内容的提问来源于stack exchange，提问作者Umid Umaraliev