PLSQL存储过程使用Bulk Collections优化插入无数据问题咨询

核心错误点（导致无数据写入） #

• 集合赋值不匹配：你定义的prod_ids_t是仅存储apld_bill_rt.intl_prod_id单字段的集合，但BULK COLLECT对应的SELECT语句同时查询了intl_prod_id和bill_date两个字段，会导致运行时集合赋值失败，后续l_prod_ids.COUNT为0，循环根本不会执行。
• 关联条件缺失：INSERT语句中的JOIN FCBSADM.PROD_DST pds ON 后面没有填写关联条件，会导致关联结果为空，即使循环执行也查不到任何可插入的数据。
• 业务逻辑丢失：原逻辑中存在ab.bill_date = pid_billdate的入参过滤条件，新版代码完全删除了该规则；同时循环内的INSERT没有和批量查询出来的l_prod_ids(indx)做关联匹配，完全没有用到之前批量查询的产品ID，逻辑和原版本完全不一致。
• 冗余查询开销：循环内的INSERT用了=ANY子查询，和前面批量查产品ID的逻辑完全重复，既没有用到批量查询的结果，还额外增加了不必要的查询开销。

另外你当前的写法本质还是逐次执行INSERT，完全没有发挥Bulk Collections的批量操作优势，性能甚至不如原版本。

正确的Bulk Collections优化实现 #

直接将全量待插入数据批量读取到集合，再用FORALL批量插入，完全不需要嵌套循环，性能提升非常明显：

CREATE OR REPLACE procedure FCBSADM.extract_monthly_data(pid_billdate DATE) is
    -- 定义存储待插入GPU表全量数据的集合类型
    TYPE gpu_tab_t IS TABLE OF gpu%ROWTYPE INDEX BY PLS_INTEGER;
    l_gpu_data gpu_tab_t;
begin
    -- 清空目标表
    execute immediate 'truncate table gpu';

    -- 一次性查询所有待插入数据，批量存入集合
    SELECT AB.ACCT_BILL_ID,
           AB.BILL_NO,
           AB.INV_ID,
           AB.BILL_DATE,
           ba2.bill_acct_id,
           ba1.bill_acct_id parent_bill_acct_id,
           AB.DUE_DATE,
           PG.CMPG_ID,
           ABR.NET_AMT,
           AB.DUE_AMT,
           P.PROD_NUM,
           pds.DST_ID,
           ABR.DESCR,
           p.intl_prod_id
    BULK COLLECT INTO l_gpu_data
    FROM apld_bill_rt abr,
         acct_bill ab,
         prod p,
         FCBSADM.PROD_DST pds,
         bill_acct_prod bap,
         bill_acct ba1,
         bill_acct ba2,
         prod_cmpg pg
    WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
      AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
      AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
      AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
      AND bap.intl_prod_id = abr.intl_prod_id
      AND ABR.CHRG_TP = 'INSTALLMENT'
      AND abr.cdate > SYSDATE - 5
      AND abr.TAX_CATG_ID = 'NOTAX'
      AND ab.bill_date = pid_billdate
      AND bap.intl_prod_id = pds.intl_prod_id
      AND bap.intl_prod_id = p.intl_prod_id
      AND p.intl_prod_id = pg.intl_prod_id(+);

    -- 批量插入所有数据
    FORALL i IN 1 .. l_gpu_data.COUNT
        INSERT INTO gpu VALUES l_gpu_data(i);
    
    COMMIT;

    -- 如果数据量过大会占用过多内存，可加LIMIT分批处理，示例：
    /*
    CURSOR gpu_cur IS
        SELECT AB.ACCT_BILL_ID,... -- 同上查询语句
    OPEN gpu_cur;
    LOOP
        FETCH gpu_cur BULK COLLECT INTO l_gpu_data LIMIT 10000; -- 每次取1万条
        EXIT WHEN l_gpu_data.COUNT = 0;
        FORALL i IN 1 .. l_gpu_data.COUNT
            INSERT INTO gpu VALUES l_gpu_data(i);
        COMMIT;
    END LOOP;
    CLOSE gpu_cur;
    */
end;
/

额外优化建议 #

• 这种整表插入的场景优先用单SQL实现：INSERT /*+ append */ INTO gpu SELECT ...，直接走直路径插入减少日志开销，性能比PLSQL批量操作还要高。
• 关联查询涉及的字段（比如acct_bill_id、intl_prod_id、bill_date）建议建索引，能大幅提升查询速度。

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