PL/SQL中INSERT INTO SELECT与游标插入方法对比及适用场景

首先直接给结论：在绝大多数数据库操作场景中，INSERT INTO ... SELECT是更受青睐的方案，原因主要是性能优势——它属于数据库级别的集合操作，Oracle优化器可以对其进行各种优化（比如直接路径插入、减少日志生成等），而且避免了PL/SQL引擎和SQL引擎之间频繁的上下文切换，数据量越大，这个优势越明显。

下面具体拆解两种方案的适用场景：

一、优先选择INSERT INTO ... SELECT的场景 #

• 批量数据复制/迁移：比如你例子里这种，只是把food_tbl中类型为'C'的数据直接映射插入到candy_tbl，没有额外的逐行逻辑，这种场景下用集合操作效率最高。
• 数据量较大的插入操作：当你要插入几千、几万甚至更多行数据时，游标循环的性能会急剧下降，而INSERT INTO ... SELECT能保持稳定的高效。
• 简单的列映射插入：不需要对每行数据做额外校验、计算或调用其他业务逻辑，只是单纯的列对应插入。

你的示例代码用这种方式就是最适合的：

INSERT INTO candy_tbl (candy_name, candy_type, candy_qty) 
SELECT food_name, food_type, food_qty 
FROM food_tbl 
WHERE food_type = 'C';

二、游标循环（FOR LOOP）适用的场景 #

你提到的异常处理灵活性确实是游标循环的核心优势，但它只适合特定场景：

• 需要逐行执行复杂业务逻辑：比如每插入一行前，要根据当前行的数据调用其他存储过程、进行复杂的计算转换，或者和其他表做交互校验。
• 精细的异常控制：比如希望某一行插入失败时，跳过该行继续处理其他行，或者记录错误行的详细信息，而不是让整个批量操作直接失败。
• 逐行记录日志：需要对每一行的插入状态做单独日志记录，比如记录插入时间、处理结果等。

但你担心的性能问题是真实存在的：游标循环每一次迭代都会执行一次单独的INSERT语句，频繁在PL/SQL和SQL引擎之间切换，数据量越大，开销越高，甚至可能比集合操作慢几个数量级。

折衷方案：BULK COLLECT + FORALL #

如果既想要异常处理的灵活性，又不想牺牲性能，Oracle提供了批量操作的方案——BULK COLLECT批量获取数据，FORALL批量插入，还可以结合SAVE EXCEPTIONS来捕获异常行，不中断整体操作。

举个符合你需求的例子：

DECLARE
  -- 定义与查询结果匹配的记录类型和表类型
  TYPE candy_rec_type IS RECORD(
    food_name food_tbl.food_name%TYPE,
    food_type food_tbl.food_type%TYPE,
    food_qty food_tbl.food_qty%TYPE
  );
  TYPE candy_tab_type IS TABLE OF candy_rec_type;
  candy_tab candy_tab_type;
  
  -- 定义批量异常的异常类型
  ex_insert_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_insert_errors, -24381);
  error_idx NUMBER;
BEGIN
  -- 批量获取符合条件的数据，减少上下文切换
  SELECT food_name, food_type, food_qty
  BULK COLLECT INTO candy_tab
  FROM food_tbl
  WHERE food_type = 'C';

  -- 批量插入，同时启用异常保存，不会因为某一行失败而中断
  FORALL i IN candy_tab.FIRST .. candy_tab.LAST SAVE EXCEPTIONS
    INSERT INTO candy_tbl(candy_name, candy_type, candy_qty)
    VALUES(candy_tab(i).food_name, candy_tab(i).food_type, candy_tab(i).food_qty);

EXCEPTION
  WHEN ex_insert_errors THEN
    -- 遍历所有异常行，记录错误信息
    error_idx := SQL%BULK_EXCEPTIONS.FIRST;
    WHILE error_idx <= SQL%BULK_EXCEPTIONS.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('第 ' || error_idx || ' 行插入失败，错误码：' || -SQL%BULK_EXCEPTIONS(error_idx).ERROR_CODE || '，错误信息：' || SQLERRM(-SQL%BULK_EXCEPTIONS(error_idx).ERROR_CODE));
      error_idx := SQL%BULK_EXCEPTIONS.NEXT(error_idx);
    END LOOP;
END;
/

这种方案既保留了批量操作的高性能，又能像游标循环一样处理异常，甚至比普通游标循环的异常处理更高效。

总结 #

1. 简单批量插入、无额外逻辑：选INSERT INTO ... SELECT，性能最优。
2. 需要逐行复杂逻辑或精细异常控制：优先考虑BULK COLLECT + FORALL，平衡性能和灵活性。
3. 只有当逐行逻辑复杂到无法用批量方式实现时，才考虑普通游标循环。

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