PL/SQL新手求助：BULK COLLECT和FORALL批量插入代码的正确性与优化

先看你的代码，有几个编译错误需要先修正：

1. 游标定义顺序错误：你在声明FETCH_ARRAY类型时引用了A_CUR%ROWTYPE，但A_CUR游标是在类型之后才定义的，PL/SQL编译时会找不到这个游标类型，得把游标定义移到类型前面。
2. 语法缺失：EXIT WHEN A_CUR%NOTFOUND后面少了分号，会导致编译失败。
3. 插入语句不够严谨：INSERT INTO DEF VALUES A_ARRAY(i)没有显式指定列名，如果DEF表的列顺序和源表不一致，或者以后表结构变更，会导致数据插入错误，最好显式列出列名。

修正后的基础代码如下：

DECLARE
  CURSOR A_CUR IS 
    SELECT * FROM ABC 
    UNION ALL 
    SELECT * FROM BCD;
  TYPE FETCH_ARRAY IS TABLE OF A_CUR%ROWTYPE;
  A_ARRAY FETCH_ARRAY;
BEGIN
  OPEN A_CUR;
  LOOP
    FETCH A_CUR BULK COLLECT INTO A_ARRAY LIMIT 1000;
    FORALL i IN 1..A_ARRAY.COUNT
      INSERT INTO DEF (A, B, C) VALUES A_ARRAY(i);
    -- 每批插入后提交，避免UNDO空间耗尽
    COMMIT;
    EXIT WHEN A_CUR%NOTFOUND;
  END LOOP;
  CLOSE A_CUR;
  -- 最终提交确保剩余数据写入
  COMMIT;
END;
/

接下来针对百万级数据的高效实现，给你几个关键优化建议：

• 调整批量大小（LIMIT值）：原代码用的1000是基础值，你可以根据数据库内存情况调整到更大的值（比如5000、10000）。更大的批量能减少PL/SQL与SQL引擎的上下文切换次数，提升效率，但注意不要太大导致内存溢出，建议测试后找到最优值。
• 优先考虑纯SQL插入（如果业务允许）：如果你的复杂查询可以直接用INSERT ... SELECT完成，这是效率最高的方式——单条SQL操作能让数据库做更多优化（比如并行执行）：

  INSERT /*+ APPEND PARALLEL(DEF 4) */ INTO DEF (A, B, C)
  SELECT * FROM ABC
  UNION ALL
  SELECT * FROM BCD;
  COMMIT;
  

  这里的APPEND提示会让数据库直接在表的高水位线之后写入数据，减少日志生成；PARALLEL提示开启并行插入（需要数据库支持并行，且表为分区表或允许并行操作）。
• 批量提交优化：原代码最后一次性提交，百万级数据会占用大量UNDO空间，可能导致UNDO溢出。建议每插入N批后提交一次，比如每5批提交一次，同时循环结束后再做一次最终提交。
• 临时禁用约束和索引：如果DEF表上有非必要的约束、索引，可以在插入前临时禁用，插入完成后再重建。每插入一条数据都维护索引和检查约束会大幅降低速度，百万级数据下这个优化效果很明显。
• 使用并行执行包（DBMS_PARALLEL_EXECUTE）：如果数据量达到千万级，可以用Oracle的DBMS_PARALLEL_EXECUTE包把数据分成多个分片，并行插入，充分利用数据库的多核资源。

最后提醒：所有优化操作一定要先在非生产环境验证，尤其是并行操作、禁用约束这类操作，避免影响生产数据。

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