如何用变量实现多表选定记录复制、子表同步及失败日志记录？

1. 先创建日志表（用于记录失败信息） #

首先需要一个日志表存储复制失败的记录详情，执行以下DDL创建：

CREATE TABLE COPY_ERROR_LOG (
    LOG_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    OPERATION_TYPE VARCHAR2(50) NOT NULL, -- 父表/子表复制
    SOURCE_TABLE VARCHAR2(100) NOT NULL,
    TARGET_TABLE VARCHAR2(100) NOT NULL,
    RECORD_ID VARCHAR2(200), -- 关联的父表主键/子表记录标识
    ERROR_MESSAGE VARCHAR2(1000) NOT NULL,
    ERROR_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP
);

2. 核心实现逻辑（含父表复制+子表复制+错误日志） #

以下PL/SQL块实现了原需求的全部功能：通过MINUS筛选待复制记录、逐条处理父表插入并记录失败日志、父表插入成功后自动复制关联子表记录。

DECLARE
    -- 模拟配置表参数（实际可从配置表查询获取）
    v_SourceParentTab VARCHAR2(100) := 'MergeTestVals';
    v_TargetParentTab VARCHAR2(100) := 'MergeTestIns';
    v_ParentKeyField VARCHAR2(50) := 'Field1'; -- 父表主键，用于关联子表
    v_ParentConfFields VARCHAR2(100) := 'Field1,Field2,Field3,Field4';
    
    -- 子表配置（可扩展为从配置表批量读取多个子表）
    v_SourceChildTab VARCHAR2(100) := 'ChildSource';
    v_TargetChildTab VARCHAR2(100) := 'ChildTarget';
    v_ChildForeignKey VARCHAR2(50) := 'ParentField1'; -- 子表关联父表的外键
    v_ChildConfFields VARCHAR2(100) := 'ChildID,ParentField1,ChildField2,ChildField3';
    
    -- 动态SQL变量
    v_MinusQuery VARCHAR2(2000);
    v_ParentInsertSql VARCHAR2(2000);
    v_ChildInsertSql VARCHAR2(2000);
    v_ParentRec SYS_REFCURSOR;
    v_ParentId VARCHAR2(200); -- 当前父表记录的主键值
    v_Field2 VARCHAR2(100);
    v_Field3 VARCHAR2(100);
    v_Field4 VARCHAR2(100);
    v_FieldList VARCHAR2(1000);
    v_ValueList VARCHAR2(1000);
BEGIN
    -- 1. 生成父表MINUS查询语句（筛选待复制的差异记录）
    v_MinusQuery := 'SELECT ' || v_ParentConfFields || ' FROM ' || v_SourceParentTab ||
                    ' MINUS ' ||
                    'SELECT ' || v_ParentConfFields || ' FROM ' || v_TargetParentTab;

    -- 2. 生成父表插入SQL模板
    v_FieldList := '';
    v_ValueList := '';
    FOR i IN (SELECT TRIM(REGEXP_SUBSTR(v_ParentConfFields, '[^,]+', 1, LEVEL)) AS field
              FROM DUAL
              CONNECT BY LEVEL <= LENGTH(v_ParentConfFields) - LENGTH(REPLACE(v_ParentConfFields, ',')) + 1)
    LOOP
        v_FieldList := v_FieldList || i.field || ',';
        v_ValueList := v_ValueList || ':' || i.field || ',';
    END LOOP;
    v_FieldList := RTRIM(v_FieldList, ',');
    v_ValueList := RTRIM(v_ValueList, ',');

    v_ParentInsertSql := 'INSERT INTO ' || v_TargetParentTab || '(' || v_FieldList || ') VALUES (' || v_ValueList || ')';

    -- 3. 遍历待复制的父表记录，逐条处理
    OPEN v_ParentRec FOR v_MinusQuery;
    LOOP
        FETCH v_ParentRec INTO v_ParentId, v_Field2, v_Field3, v_Field4; -- 字段顺序需与v_ParentConfFields完全一致
        EXIT WHEN v_ParentRec%NOTFOUND;

        BEGIN
            -- 插入父表记录
            EXECUTE IMMEDIATE v_ParentInsertSql USING v_ParentId, v_Field2, v_Field3, v_Field4;
            
            -- 4. 父表插入成功后，复制关联子表记录
            v_FieldList := '';
            v_ValueList := '';
            FOR i IN (SELECT TRIM(REGEXP_SUBSTR(v_ChildConfFields, '[^,]+', 1, LEVEL)) AS field
                      FROM DUAL
                      CONNECT BY LEVEL <= LENGTH(v_ChildConfFields) - LENGTH(REPLACE(v_ChildConfFields, ',')) + 1)
            LOOP
                v_FieldList := v_FieldList || i.field || ',';
            END LOOP;
            v_FieldList := RTRIM(v_FieldList, ',');

            v_ChildInsertSql := 'INSERT INTO ' || v_TargetChildTab || '(' || v_FieldList || ') ' ||
                                'SELECT ' || v_ChildConfFields || ' FROM ' || v_SourceChildTab ||
                                ' WHERE ' || v_ChildForeignKey || ' = :parent_id';
            
            -- 执行子表插入并捕获异常
            BEGIN
                EXECUTE IMMEDIATE v_ChildInsertSql USING v_ParentId;
                DBMS_OUTPUT.PUT_LINE('父表ID ' || v_ParentId || ' 及关联子表记录复制成功');
            EXCEPTION
                WHEN OTHERS THEN
                    INSERT INTO COPY_ERROR_LOG (OPERATION_TYPE, SOURCE_TABLE, TARGET_TABLE, RECORD_ID, ERROR_MESSAGE)
                    VALUES ('子表复制', v_SourceChildTab, v_TargetChildTab, v_ParentId, SQLERRM);
                    DBMS_OUTPUT.PUT_LINE('父表ID ' || v_ParentId || ' 子表复制失败: ' || SQLERRM);
            END;

        EXCEPTION
            WHEN OTHERS THEN
                -- 记录父表复制错误日志
                INSERT INTO COPY_ERROR_LOG (OPERATION_TYPE, SOURCE_TABLE, TARGET_TABLE, RECORD_ID, ERROR_MESSAGE)
                VALUES ('父表复制', v_SourceParentTab, v_TargetParentTab, v_ParentId, SQLERRM);
                DBMS_OUTPUT.PUT_LINE('父表ID ' || v_ParentId || ' 复制失败: ' || SQLERRM);
        END;
    END LOOP;
    CLOSE v_ParentRec;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        INSERT INTO COPY_ERROR_LOG (OPERATION_TYPE, SOURCE_TABLE, TARGET_TABLE, ERROR_MESSAGE)
        VALUES ('整体操作', v_SourceParentTab, v_TargetParentTab, '批量处理失败: ' || SQLERRM);
        RAISE;
END;
/

3. 关键细节说明 #

• 配置表扩展：实际应用中，可将父表、子表、字段、关联键等信息存入配置表（如TABLE_COPY_CONFIG），通过查询配置表动态获取参数，实现多表切换的灵活性。
• 游标字段匹配：使用SYS_REFCURSOR时，FETCH的变量顺序必须与v_ParentConfFields的字段顺序完全一致；如果字段较多，建议改用DBMS_SQL包处理，避免硬编码变量。
• 子表复制优化：示例采用批量INSERT（基于父表主键查询源子表），比逐条遍历子表效率更高；若需逐条记录子表复制结果，可改为游标遍历子表记录。
• 事务控制：整体在循环结束后提交事务，异常时回滚未提交操作；也可根据需求改为每条父表记录提交，但会影响性能。

4. 替代方案：保留Merge+日志记录 #

如果想保留Merge的高效性，同时记录父表复制错误，可结合Oracle的LOG ERRORS子句（10g+支持）：

-- 自动创建父表错误日志表
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name => 'MergeTestIns', err_log_table_name => 'MergeTestIns_ERR');

-- 修改Merge语句添加日志记录
v_SqlStmt := 'Merge Into '||v_TargetTable||' it '||
             'Using '||v_SourceTable||' st '||
             'On ('||v_OnFields||')'||
             'When NOT Matched Then Insert ('||v_InsertFields||') '||
                                   'Values ('||v_ValueFields||')'||
             'LOG ERRORS INTO MergeTestIns_ERR REJECT LIMIT UNLIMITED';

该方案仅能记录父表的Merge错误，无法直接关联子表复制，适合仅需父表批量复制的场景。

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