如何在PLSQL中使用FORALL获取按ID分组的更新计数

方案1：直接在FORALL迭代中捕获单ID更新条数 #

FORALL的每一次迭代对应一个指定ID的更新操作，此时sql%rowcount会返回当前迭代里更新的记录数，我们可以直接将这个数值和对应ID关联存储，不用额外处理明细，效率更高。

修改后的代码 #

DECLARE
    TYPE t_ids IS TABLE OF test_update.name_id%TYPE;
    TYPE t_name IS TABLE OF test_update.name_name%TYPE;
    -- 定义关联数组存储每个ID的更新计数
    TYPE t_update_count IS TABLE OF PLS_INDEX INDEX BY test_update.name_id%TYPE;
    
    l_ids t_ids := t_ids(1,2,3);
    l_name t_name := t_name('XXX','YYY','ZZZ');
    l_update_counts t_update_count;
BEGIN
    FORALL i IN INDICES OF l_ids
        UPDATE test_update
        SET name_name = l_name(i)
        WHERE name_id = l_ids(i);
        -- 捕获当前ID的更新条数
        l_update_counts(l_ids(i)) := sql%rowcount;
    
    -- 输出分组统计结果
    FOR i IN INDICES OF l_ids LOOP
        DBMS_OUTPUT.PUT_LINE('Total update for Id ' || l_ids(i) || ': ' || l_update_counts(l_ids(i)));
    END LOOP;
END;
/

方案2：基于RETURNING收集的明细统计分组计数 #

如果需要保留每条更新的明细数据，可以先通过RETURNING BULK COLLECT获取所有更新记录，再遍历明细集合统计每个ID的更新次数。

代码示例 #

DECLARE
    TYPE t_ids IS TABLE OF test_update.name_id%TYPE;
    TYPE t_name IS TABLE OF test_update.name_name%TYPE;
    TYPE t_update_count IS TABLE OF PLS_INDEX INDEX BY test_update.name_id%TYPE;
    
    l_ids t_ids := t_ids(1,2,3);
    l_name t_name := t_name('XXX','YYY','ZZZ');
    l_updated_ids t_ids;
    l_updated_name t_name;
    l_update_counts t_update_count;
BEGIN
    FORALL i IN INDICES OF l_ids
        UPDATE test_update
        SET name_name = l_name(i)
        WHERE name_id = l_ids(i)
        RETURNING name_id, name_name BULK COLLECT INTO l_updated_ids, l_updated_name;
    
    -- 遍历明细统计每个ID的更新次数
    FOR i IN 1..l_updated_ids.COUNT LOOP
        IF l_update_counts.EXISTS(l_updated_ids(i)) THEN
            l_update_counts(l_updated_ids(i)) := l_update_counts(l_updated_ids(i)) + 1;
        ELSE
            l_update_counts(l_updated_ids(i)) := 1;
        END IF;
    END LOOP;
    
    -- 输出统计结果
    FOR rec IN l_update_counts.FIRST..l_update_counts.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Total update for Id ' || rec || ': ' || l_update_counts(rec));
    END LOOP;
END;
/

两种方案对比 #

• 方案1更高效，不需要存储所有更新明细，适合只需要分组统计结果的场景。
• 方案2保留了每条更新的具体数据，适合既需要统计又要记录更新明细的场景。

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