Oracle表自动导出DML脚本需求：跨库资产数据同步自动化

核心思路 #

• 借助Oracle内置数据字典（USER_TABLES、USER_TAB_COLUMNS、USER_CONSTRAINTS）自动获取表结构、列名和主外键依赖关系，无需手动维护70+列的表清单
• 按主外键顺序生成INSERT语句，避免跨表插入时的约束冲突
• 支持批量指定asset_no，一键生成所有目标表的同步语句

分步实现 #

1. 设置同步参数 #

先定义要同步的资产号和目标表范围：

DEFINE ASSET_NOS = '''555'',''666'''; -- 多个资产号用逗号分隔，单引号需转义
DEFINE TARGET_TABLES = 'ASSET_MAIN,ASSET_DETAIL,ASSET_ATTR,ASSET_LOG'; -- 需同步的表名

2. 自动获取主外键排序的表顺序（可选） #

如果不想手动排表顺序，用这段代码自动生成从主表到从表的插入顺序：

WITH fk_deps AS (
    SELECT 
        tc.table_name AS child_table,
        rt.table_name AS parent_table
    FROM USER_CONSTRAINTS tc
    JOIN USER_CONSTRAINTS rc ON tc.r_constraint_name = rc.constraint_name
    JOIN USER_TABLES rt ON rc.table_name = rt.table_name
    WHERE tc.constraint_type = 'R'
      AND tc.table_name IN (&TARGET_TABLES)
),
sorted_tables AS (
    SELECT 
        table_name,
        LEVEL AS dep_level
    FROM (SELECT DISTINCT table_name FROM USER_TABLES WHERE table_name IN (&TARGET_TABLES))
    START WITH table_name NOT IN (SELECT child_table FROM fk_deps)
    CONNECT BY PRIOR table_name = parent_table
    ORDER BY dep_level ASC
)
SELECT LISTAGG(table_name, ',') WITHIN GROUP (ORDER BY dep_level) INTO :sorted_tables FROM sorted_tables;

3. 生成INSERT语句的核心脚本 #

执行这段PL/SQL，自动输出所有目标表的同步INSERT语句：

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
    v_table VARCHAR2(128);
    v_col_names VARCHAR2(4000);
    v_select_cols VARCHAR2(4000);
    v_insert_stmt VARCHAR2(4000);
    
    -- 用手动指定顺序或自动排序后的表列表
    CURSOR c_target_tables IS
        SELECT table_name FROM USER_TABLES 
        WHERE table_name IN (&TARGET_TABLES)
        -- 如需自动排序，替换为下面一行：
        -- WHERE table_name IN (:sorted_tables) ORDER BY dep_level
        ORDER BY CASE table_name 
                   WHEN 'ASSET_MAIN' THEN 1
                   WHEN 'ASSET_DETAIL' THEN 2
                   WHEN 'ASSET_ATTR' THEN 3
                   WHEN 'ASSET_LOG' THEN 4
                 END;
BEGIN
    FOR rec IN c_target_tables LOOP
        v_table := rec.table_name;
        
        -- 获取表的所有列名（按存储顺序）
        SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)
        INTO v_col_names
        FROM USER_TAB_COLUMNS
        WHERE table_name = v_table;
        
        -- 生成SELECT部分，处理不同数据类型的转义/格式转换
        SELECT LISTAGG(
            CASE data_type
                WHEN 'VARCHAR2' THEN '''' || column_name || ''''
                WHEN 'CHAR' THEN '''' || column_name || ''''
                WHEN 'DATE' THEN 'TO_CHAR(' || column_name || ', ''YYYY-MM-DD HH24:MI:SS'')'
                WHEN 'TIMESTAMP' THEN 'TO_CHAR(' || column_name || ', ''YYYY-MM-DD HH24:MI:SS.FF3'')'
                ELSE column_name -- 数字、RAW等类型直接输出
            END, ', '
        ) WITHIN GROUP (ORDER BY column_id)
        INTO v_select_cols
        FROM USER_TAB_COLUMNS
        WHERE table_name = v_table;
        
        -- 拼接完整INSERT语句
        v_insert_stmt := 'INSERT INTO ' || v_table || ' (' || v_col_names || ') ' ||
                        'SELECT ' || v_select_cols || ' ' ||
                        'FROM ' || v_table || ' ' ||
                        'WHERE asset_no IN (&ASSET_NOS);';
        
        -- 输出脚本，可直接复制到数据库2执行
        DBMS_OUTPUT.PUT_LINE('-- 同步表: ' || v_table);
        DBMS_OUTPUT.PUT_LINE(v_insert_stmt);
        DBMS_OUTPUT.PUT_LINE('/');
        DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
END;
/

使用注意事项 #

• 如果表包含CLOB/BLOB类型，需要单独处理（比如用DBMS_LOB.SUBSTR或转成字符串）
• 执行脚本后，复制输出的所有语句到数据库2执行，确保按顺序插入
• 可将脚本保存为固定文件，每日仅修改ASSET_NOS参数即可快速生成同步语句

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