Oracle中Merge语句执行后SQL%ROWCOUNT返回0问题排查

你遇到的问题核心是SQL%ROWCOUNT被后续的DDL操作重置了，具体原因和解决步骤如下：

原因分析 #

1. SQL%ROWCOUNT的时效性：这个内置变量仅保留最近一次执行的SQL语句影响的行数。你在MERGE之后执行了EXECUTE IMMEDIATE 'ALTER INDEX ... REBUILD'，这属于DDL语句——Oracle执行DDL时会触发隐式提交，同时将SQL%ROWCOUNT重置为0。等你执行完COMMIT再读取SQL%ROWCOUNT时，它已经是DDL操作的结果（DDL不影响数据行数，所以返回0），而非MERGE语句的实际影响行数。
2. MERGE语句存在笔误：INSERT(x.col_1, x.col_1)重复指定了col_1，应该是要插入col_1和col_2，这个属于逻辑错误，建议修正。
3. 关于索引重建：正常情况下，MERGE插入数据后Oracle会自动维护索引，除非索引此前被标记为不可用，否则无需手动执行REBUILD操作。

解决办法 #

在MERGE执行完成后立即捕获SQL%ROWCOUNT的值，存入临时变量，后续操作再使用该变量即可。修改后的代码如下：

CREATE OR REPLACE FUNCTION merge_fn(
  p_val NUMBER
) RETURN merge_tab
IS
  col_1_idx VARCHAR2(50) := 'col_1_idx';
  teilenummer_trim_idx VARCHAR2(50) := 'teilenummer_trim_idx';
  v_merge_tab merge_tab := merge_tab();
  v_row_count NUMBER; -- 新增变量存储MERGE影响行数
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

IF p_val = 1 THEN
    MERGE INTO table1 x
    USING (SELECT col_1, col_2  FROM table2) y
    ON (x.col_1  = y.col_1)
    WHEN NOT MATCHED THEN
    INSERT(x.col_1, x.col_2)  -- 修正重复的col_1
    VALUES(y.col_1, y.col_2);

    -- 立即获取MERGE的影响行数
    v_row_count := SQL%ROWCOUNT;

    -- 除非索引确实需要重建，否则建议移除这行
    EXECUTE IMMEDIATE 'ALTER INDEX ' || col_1_idx || ' REBUILD';

    COMMIT;
    
    v_merge_tab.EXTEND;
    v_merge_tab(v_merge_tab.LAST) := v_row_count;
   
END IF;    
    RETURN v_merge_tab;

EXCEPTION
    WHEN OTHERS THEN
        v_merge_tab.EXTEND;
        v_merge_tab(v_merge_tab.LAST) := 0;
        RETURN v_merge_tab;
END;

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