SQL存储过程中插入后获取各IN变量对应的插入行数

要分别获取ADDRTYPE IN ('H', 'O')中每个类型对应的插入行数，有以下几种可行方案：

方案1：拆分INSERT语句分别执行 #

把原有的单条INSERT拆成两条，针对每个ADDRTYPE值单独执行，每次执行后用sql%rowcount记录对应行数。这种方式简单直观，适合IN条件值较少的场景：

DECLARE
    v_h_count NUMBER;
    v_o_count NUMBER;
BEGIN
    -- 插入ADDRTYPE='H'的数据
    INSERT INTO TABLEB (ID, USERNAME, CITY)
    SELECT B.ID, B.USERNAME, B.ADDRESS
    FROM TABLEB B
    WHERE B.CITY = 'XYZ'
      AND B.ADDRTYPE = 'H';
    v_h_count := sql%rowcount;

    -- 插入ADDRTYPE='O'的数据
    INSERT INTO TABLEB (ID, USERNAME, CITY)
    SELECT B.ID, B.USERNAME, B.ADDRESS
    FROM TABLEB B
    WHERE B.CITY = 'XYZ'
      AND B.ADDRTYPE = 'O';
    v_o_count := sql%rowcount;

    DBMS_OUTPUT.put_line('成功加载数据：ADDRTYPE=H 插入' || v_h_count || '行，ADDRTYPE=O 插入' || v_o_count || '行。');
END;
/

方案2：先统计再插入（需注意数据一致性） #

先通过分组统计获取各ADDRTYPE的待插入行数，再执行INSERT。但要注意如果统计和插入之间有其他会话修改了TABLEB的数据，统计结果和实际插入行数可能不一致：

DECLARE
    v_h_count NUMBER;
    v_o_count NUMBER;
BEGIN
    -- 提前统计各类型数量
    SELECT 
        SUM(CASE WHEN ADDRTYPE = 'H' THEN 1 ELSE 0 END),
        SUM(CASE WHEN ADDRTYPE = 'O' THEN 1 ELSE 0 END)
    INTO v_h_count, v_o_count
    FROM TABLEB
    WHERE CITY = 'XYZ'
      AND ADDRTYPE IN ('H', 'O');

    -- 执行插入
    INSERT INTO TABLEB (ID, USERNAME, CITY)
    SELECT B.ID, B.USERNAME, B.ADDRESS
    FROM TABLEB B
    WHERE B.CITY = 'XYZ'
      AND B.ADDRTYPE IN ('H', 'O');

    DBMS_OUTPUT.put_line('成功加载数据：ADDRTYPE=H 插入' || v_h_count || '行，ADDRTYPE=O 插入' || v_o_count || '行。');
END;
/

方案3：用RETURNING子句+BULK COLLECT获取实际插入的类型 #

通过RETURNING ... BULK COLLECT INTO收集所有插入记录的ADDRTYPE，再统计每个类型的数量。这种方式能保证统计的是实际插入的行数，避免数据不一致问题：

DECLARE
    TYPE addrtype_list IS TABLE OF TABLEB.ADDRTYPE%TYPE;
    v_addrtypes addrtype_list;
    v_h_count NUMBER := 0;
    v_o_count NUMBER := 0;
BEGIN
    INSERT INTO TABLEB (ID, USERNAME, CITY)
    SELECT B.ID, B.USERNAME, B.ADDRESS
    FROM TABLEB B
    WHERE B.CITY = 'XYZ'
      AND B.ADDRTYPE IN ('H', 'O')
    RETURNING B.ADDRTYPE BULK COLLECT INTO v_addrtypes;

    -- 统计各类型数量
    FOR i IN 1..v_addrtypes.COUNT LOOP
        IF v_addrtypes(i) = 'H' THEN
            v_h_count := v_h_count + 1;
        ELSIF v_addrtypes(i) = 'O' THEN
            v_o_count := v_o_count + 1;
        END IF;
    END LOOP;

    DBMS_OUTPUT.put_line('成功加载数据：ADDRTYPE=H 插入' || v_h_count || '行，ADDRTYPE=O 插入' || v_o_count || '行。');
END;
/

注：你的原始代码中INSERT INTO TABLEB同时SELECT FROM TABLEB，这会导致插入重复数据，可能是笔误（比如应该从TABLEA查询），请根据实际业务场景调整表名。

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