Oracle如何为多表批量实现复制行并修改change_id列的需求

批量实现脚本（推荐，性能更优，无需手动指定列） #

DECLARE
    TYPE tablenamearray IS VARRAY(30) OF VARCHAR2(30);
    tablenames tablenamearray;
    v_col_list VARCHAR2(32767);
    v_sql VARCHAR2(32767);
BEGIN
    -- 在这里配置待处理的表名，需要和Oracle数据字典存储的大小写一致（默认全大写）
    tablenames := tablenamearray('TABLE_ONE', 'TABLE_TWO', 'TABLE_THREE');
    
    FOR i IN tablenames.FIRST .. tablenames.LAST LOOP
        -- 自动读取当前表的所有列，仅将change_id替换为固定值-1，其他列保持原值
        SELECT LISTAGG(
            CASE WHEN UPPER(column_name) = 'CHANGE_ID' 
                 THEN '-1 AS CHANGE_ID' 
                 ELSE column_name 
            END, ','
        ) WITHIN GROUP (ORDER BY column_id)
        INTO v_col_list
        FROM all_tab_columns
        WHERE UPPER(table_name) = UPPER(tablenames(i))
        -- 如果存在多schema同名表的情况，可放开下面的注释指定schema
        -- AND owner = '你的业务SCHEMA名称'
        ;
        
        -- 拼接动态插入SQL
        v_sql := 'INSERT INTO ' || tablenames(i) || 
                 ' SELECT ' || v_col_list || 
                 ' FROM ' || tablenames(i) || 
                 ' WHERE change_id = 0';
        
        -- 执行SQL
        EXECUTE IMMEDIATE v_sql;
        -- 输出处理进度，可删除
        DBMS_OUTPUT.PUT_LINE('表' || tablenames(i) || '处理完成，新增行数：' || SQL%ROWCOUNT);
    END LOOP;
    -- 统一提交，可根据需要调整为循环内逐表提交
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('处理出错，已回滚，错误信息：' || SQLERRM);
        RAISE;
END;
/

方案说明 #

• 完全不需要手动指定每张表的列，自动从Oracle数据字典拉取列结构，适配任意包含change_id字段的表
• 采用批量INSERT SELECT逻辑，性能远高于逐行循环插入，适合数据量较大的场景
• 适配你提到的主键规则：主键包含change_id，修改为-1后不会触发唯一约束冲突

逐行循环实现（仅当需要行级自定义逻辑时使用） #

如果你需要在复制每行时做额外的自定义处理，可以用DBMS_SQL实现动态行操作：

DECLARE
    TYPE tablenamearray IS VARRAY(30) OF VARCHAR2(30);
    tablenames tablenamearray := tablenamearray('TABLE_ONE', 'TABLE_TWO', 'TABLE_THREE');
    v_cursor NUMBER;
    v_desc DBMS_SQL.DESC_TAB;
    v_col_cnt NUMBER;
    v_row PL/SQL.TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(128);
    v_sql VARCHAR2(32767);
    v_change_id_idx PLS_INTEGER;
    v_res NUMBER;
BEGIN
    FOR i IN tablenames.FIRST .. tablenames.LAST LOOP
        -- 打开动态游标查询原数据
        v_cursor := DBMS_SQL.OPEN_CURSOR;
        v_sql := 'SELECT * FROM ' || tablenames(i) || ' WHERE change_id = 0';
        DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
        DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_cnt, v_desc);
        
        -- 定义列绑定
        FOR j IN 1 .. v_col_cnt LOOP
            DBMS_SQL.DEFINE_COLUMN(v_cursor, j, v_row(v_desc(j).col_name), 4000);
            IF UPPER(v_desc(j).col_name) = 'CHANGE_ID' THEN
                v_change_id_idx := j;
            END IF;
        END LOOP;
        
        v_res := DBMS_SQL.EXECUTE(v_cursor);
        -- 逐行处理
        WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0 LOOP
            -- 读取列值
            FOR j IN 1 .. v_col_cnt LOOP
                DBMS_SQL.COLUMN_VALUE(v_cursor, j, v_row(v_desc(j).col_name));
            END LOOP;
            -- 修改change_id为-1
            v_row('CHANGE_ID') := '-1';
            
            -- 拼接插入语句
            v_sql := 'INSERT INTO ' || tablenames(i) || '(' || 
                     LISTAGG(v_desc(j).col_name, ',') WITHIN GROUP (ORDER BY j) ||
                     ') VALUES (' || 
                     LISTAGG('''' || REPLACE(v_row(v_desc(j).col_name), '''', '''''') || '''', ',') WITHIN GROUP (ORDER BY j) || ')';
            EXECUTE IMMEDIATE v_sql;
        END LOOP;
        DBMS_SQL.CLOSE_CURSOR(v_cursor);
        DBMS_OUTPUT.PUT_LINE('表' || tablenames(i) || '处理完成');
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        IF DBMS_SQL.IS_OPEN(v_cursor) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor);
        END IF;
        RAISE;
END;
/

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