Oracle中编写仅更新变更列的表同步存储过程需求

实现思路 #

要做到仅更新每行中实际变化的列，核心是动态生成UPDATE语句：先匹配两表的主键行，逐列对比值差异，只将有变化的列加入更新列表；同时同步table1中存在但table2中没有的新增行。

存储过程代码 #

假设两表的主键为id（请替换为实际主键列名），表名为t_table1和t_table2（替换为你的table1、table2）：

CREATE OR REPLACE PROCEDURE sync_table1_to_table2
IS
    v_update_sql VARCHAR2(4000);
    v_col_list   VARCHAR2(3000);
    v_diff       NUMBER;
    -- 游标：获取两表的所有非主键列
    CURSOR c_cols IS
        SELECT column_name
        FROM user_tab_columns
        WHERE table_name IN ('T_TABLE1', 'T_TABLE2')
          AND column_name != 'ID'  -- 替换为你的主键列
        GROUP BY column_name
        ORDER BY column_name;
BEGIN
    -- 第一步：同步新增行（table1有但table2没有的行）
    INSERT INTO t_table2
    SELECT t1.*
    FROM t_table1 t1
    LEFT JOIN t_table2 t2 ON t1.id = t2.id
    WHERE t2.id IS NULL;

    -- 第二步：处理更新行，仅更新差异列
    FOR rec IN (
        SELECT t1.id
        FROM t_table1 t1
        JOIN t_table2 t2 ON t1.id = t2.id
        WHERE t1 IS NOT DISTINCT FROM t2  -- Oracle 12c+支持，直接筛选有差异的行
        -- 低版本Oracle替换为：WHERE NVL(t1.col1, '') != NVL(t2.col1, '') OR NVL(t1.col2, '') != NVL(t2.col2, '') ...（需遍历所有列）
    ) LOOP
        v_col_list := '';
        -- 逐列对比，收集差异列
        FOR col IN c_cols LOOP
            EXECUTE IMMEDIATE '
                SELECT CASE 
                           WHEN :a != :b 
                                OR (:a IS NULL AND :b IS NOT NULL) 
                                OR (:a IS NOT NULL AND :b IS NULL) 
                           THEN 1 
                           ELSE 0 
                       END
                FROM dual'
            INTO v_diff
            USING 
                (SELECT t1."||col.column_name||" FROM t_table1 t1 WHERE t1.id = rec.id),
                (SELECT t2."||col.column_name||" FROM t_table2 t2 WHERE t2.id = rec.id);
            
            IF v_diff = 1 THEN
                v_col_list := v_col_list || col.column_name || ' = t1.' || col.column_name || ', ';
            END IF;
        END LOOP;

        -- 生成并执行UPDATE语句（仅当有差异列时）
        IF v_col_list IS NOT NULL THEN
            v_col_list := RTRIM(v_col_list, ', ');
            v_update_sql := '
                UPDATE t_table2 t2
                SET ' || v_col_list || '
                WHERE t2.id = :p_id
                AND EXISTS (SELECT 1 FROM t_table1 t1 WHERE t1.id = t2.id)';
            
            EXECUTE IMMEDIATE v_update_sql USING rec.id;
        END IF;
    END LOOP;

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

关键说明 #

1. 主键适配：将代码中的id替换为实际主键列名，若为复合主键，需修改JOIN条件和游标筛选逻辑。
2. NULL值处理：通过CASE语句判断NULL与非NULL的差异，确保空值变更被正确捕获。
3. 低版本兼容：Oracle 12c以下版本不支持IS NOT DISTINCT FROM，需手动用NVL统一空值后对比（比如NVL(t1.col, 'NULL_FLAG') != NVL(t2.col, 'NULL_FLAG')）。
4. 性能优化：数据量大时，建议添加时间戳等条件限制同步范围（比如WHERE t1.update_time > SYSDATE - 1），避免全表扫描。

使用方法 #

直接调用存储过程即可：

EXEC sync_table1_to_table2;

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