如何结合条件使用Bulk Collect For Loop优化PL/SQL分表插入

问题背景 #

源表中同一发票编号（Invnum）对应多条明细记录，每条记录重复携带发票表头标识，需要将数据拆分到独立的INVOICE_HEADER表（存储发票汇总信息）和INVOICE_LINE表（存储明细记录），通过唯一的InvId关联两张表。当前采用普通FOR LOOP结合IF ELSE逻辑维护唯一InvId，希望获取更高效的实现方式，尤其是结合BULK COLLECT的PL/SQL方案。

示例表结构与数据 #

源表（SOURCE_INVOICES）

Header表（INVOICE_HEADER）

Line表（INVOICE_LINE）

方案1：纯SQL实现（最高效） #

优先推荐纯SQL方案，Oracle对集合操作的优化远优于过程化循环，无需编写PL/SQL即可完成拆分：

步骤1：插入Header表 #

通过分组汇总生成Header数据，用序列生成唯一InvId：

INSERT INTO INVOICE_HEADER (InvId, InvNum, Amt)
SELECT SEQ_INVOICE_ID.NEXTVAL, Invnum, SUM(amount)
FROM SOURCE_INVOICES
GROUP BY Invnum;

步骤2：插入Line表 #

利用Header表的InvId关联源表数据插入Line表：

INSERT INTO INVOICE_LINE (InvId, linetype, amount, linenumber)
SELECT h.InvId, s.linetype, s.amount, s.linenumber
FROM SOURCE_INVOICES s
JOIN INVOICE_HEADER h ON s.Invnum = h.InvNum;

如果需要一次性完成（避免中间提交），可以用INSERT ALL语句：

INSERT ALL
  INTO INVOICE_HEADER (InvId, InvNum, Amt) VALUES (inv_id, invnum, total_amt)
  INTO INVOICE_LINE (InvId, linetype, amount, linenumber) VALUES (inv_id, linetype, amount, linenumber)
SELECT SEQ_INVOICE_ID.NEXTVAL AS inv_id,
       s.Invnum,
       SUM(s.amount) OVER (PARTITION BY s.Invnum) AS total_amt,
       s.linetype,
       s.amount,
       s.linenumber
FROM SOURCE_INVOICES s;

方案2：BULK COLLECT + FORALL的PL/SQL实现 #

如果必须用PL/SQL（比如需要额外业务逻辑处理），BULK COLLECT结合FORALL可以大幅减少上下文切换，比普通FOR LOOP效率高很多：

实现思路 #

1. 用BULK COLLECT批量获取源表分组后的发票信息及明细
2. 用临时结构存储发票的InvId映射关系
3. 批量插入Header表，再批量插入Line表

代码示例 #

CREATE OR REPLACE PROCEDURE SP_SPLIT_INVOICES IS
    -- 定义源表数据类型
    TYPE t_source_rec IS RECORD (
        invnum     SOURCE_INVOICES.Invnum%TYPE,
        linetype   SOURCE_INVOICES.linetype%TYPE,
        amount     SOURCE_INVOICES.amount%TYPE,
        linenumber SOURCE_INVOICES.linenumber%TYPE
    );
    TYPE t_source_tab IS TABLE OF t_source_rec;
    v_source_data t_source_tab;

    -- 定义Header数据类型（含生成的InvId）
    TYPE t_header_rec IS RECORD (
        inv_id     INVOICE_HEADER.InvId%TYPE,
        invnum     INVOICE_HEADER.InvNum%TYPE,
        total_amt  INVOICE_HEADER.Amt%TYPE
    );
    TYPE t_header_tab IS TABLE OF t_header_rec;
    v_header_data t_header_tab;

    -- 定义Invnum到InvId的映射索引表
    TYPE t_inv_map IS TABLE OF INVOICE_HEADER.InvId%TYPE INDEX BY VARCHAR2(20);
    v_inv_map t_inv_map;

BEGIN
    -- 1. 批量获取源表数据（按Invnum排序，确保同一发票的记录连续）
    SELECT invnum, linetype, amount, linenumber
    BULK COLLECT INTO v_source_data
    FROM SOURCE_INVOICES
    ORDER BY invnum, linenumber;

    -- 2. 生成Header数据及映射关系
    IF v_source_data IS NOT EMPTY THEN
        v_header_data := t_header_tab();
        v_inv_map.DELETE;

        DECLARE
            v_current_invnum SOURCE_INVOICES.Invnum%TYPE := v_source_data(1).invnum;
            v_total_amt      NUMBER := 0;
        BEGIN
            FOR i IN v_source_data.FIRST .. v_source_data.LAST LOOP
                IF v_source_data(i).invnum != v_current_invnum THEN
                    -- 新增一条Header记录
                    v_header_data.EXTEND;
                    v_header_data(v_header_data.LAST).inv_id := SEQ_INVOICE_ID.NEXTVAL;
                    v_header_data(v_header_data.LAST).invnum := v_current_invnum;
                    v_header_data(v_header_data.LAST).total_amt := v_total_amt;
                    -- 记录映射关系
                    v_inv_map(v_current_invnum) := v_header_data(v_header_data.LAST).inv_id;

                    -- 重置当前发票信息
                    v_current_invnum := v_source_data(i).invnum;
                    v_total_amt := v_source_data(i).amount;
                ELSE
                    v_total_amt := v_total_amt + v_source_data(i).amount;
                END IF;
            END LOOP;

            -- 处理最后一条发票的Header
            v_header_data.EXTEND;
            v_header_data(v_header_data.LAST).inv_id := SEQ_INVOICE_ID.NEXTVAL;
            v_header_data(v_header_data.LAST).invnum := v_current_invnum;
            v_header_data(v_header_data.LAST).total_amt := v_total_amt;
            v_inv_map(v_current_invnum) := v_header_data(v_header_data.LAST).inv_id;
        END;

        -- 3. 批量插入Header表
        FORALL i IN v_header_data.FIRST .. v_header_data.LAST
            INSERT INTO INVOICE_HEADER (InvId, InvNum, Amt)
            VALUES (v_header_data(i).inv_id, v_header_data(i).invnum, v_header_data(i).total_amt);

        -- 4. 批量插入Line表
        FORALL i IN v_source_data.FIRST .. v_source_data.LAST
            INSERT INTO INVOICE_LINE (InvId, linetype, amount, linenumber)
            VALUES (v_inv_map(v_source_data(i).invnum), v_source_data(i).linetype, v_source_data(i).amount, v_source_data(i).linenumber);
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END SP_SPLIT_INVOICES;
/

优化说明 #

• BULK COLLECT：一次性将源表数据加载到内存集合，减少与数据库的交互次数
• FORALL：批量执行DML操作，比普通Loop的单次插入效率提升数倍
• 排序源数据：确保同一发票的记录连续，简化分组汇总逻辑
• INDEX BY索引表：用索引表存储Invnum到InvId的映射，查找效率为O(1)

方案对比 #

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