Oracle动态生成INSERT INTO语句的PL/SQL脚本改造需求

以下是通用PL/SQL脚本，可自动适配任意表的列结构，生成可直接执行的INSERT语句，满足无外部客户端、无数据库链接的场景需求。

通用实现脚本 #

DECLARE
    v_table_name     VARCHAR2(100) := 'ADMIN.ACCT_HRS'; -- 目标表名，可替换为其他表
    v_filter_clause  VARCHAR2(1000) := 'EXISTS (SELECT 1 FROM ADMIN.INVENTORY inv WHERE inv.WID = acct.WID AND inv.PKNUM = :pknum)'; -- 自定义筛选条件
    v_pknum          NUMBER := &input_pknum; -- 接收用户输入的PKNUM参数
    v_column_list    VARCHAR2(4000);
    v_select_sql     VARCHAR2(4000);
    v_cursor_id      INTEGER;
    v_col_count      INTEGER;
    v_desc_tab       DBMS_SQL.DESC_TAB;
    v_value          VARCHAR2(4000);
    v_insert_prefix  VARCHAR2(4000);
    v_insert_values  VARCHAR2(4000);
BEGIN
    -- 1. 从数据字典获取表列信息，生成INSERT语句的列前缀部分
    SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)
    INTO v_column_list
    FROM all_tab_columns
    WHERE owner || '.' || table_name = v_table_name
    ORDER BY column_id;

    v_insert_prefix := 'INSERT INTO ' || v_table_name || ' (' || v_column_list || ') VALUES (';

    -- 2. 构建带筛选条件的查询SQL
    v_select_sql := 'SELECT * FROM ' || v_table_name || ' acct WHERE ' || v_filter_clause;

    -- 3. 使用DBMS_SQL处理动态查询，适配任意列数与类型
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor_id, v_select_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':pknum', v_pknum);
    DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_col_count, v_desc_tab);

    -- 定义列绑定，适配所有列
    FOR i IN 1..v_col_count LOOP
        DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_value, 4000);
    END LOOP;

    -- 执行查询
    DBMS_SQL.EXECUTE(v_cursor_id);

    -- 遍历结果集，逐行生成INSERT语句
    WHILE DBMS_SQL.FETCH_ROWS(v_cursor_id) > 0 LOOP
        v_insert_values := '';
        FOR i IN 1..v_col_count LOOP
            DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_value);

            -- 根据数据类型格式化值，处理空值与特殊字符转义
            CASE v_desc_tab(i).col_type
                WHEN 1 -- VARCHAR2/CHAR类型
                     THEN v_insert_values := v_insert_values || '''' || REPLACE(NVL(v_value, 'NULL'), '''', '''''') || ''',';
                WHEN 9 -- DATE类型
                     THEN v_insert_values := v_insert_values || CASE WHEN v_value IS NULL THEN 'NULL' ELSE 'TO_DATE(''' || TO_CHAR(v_value, 'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS'')' END || ',';
                WHEN 2 -- NUMBER类型
                     THEN v_insert_values := v_insert_values || NVL(v_value, 'NULL') || ',';
                ELSE -- 其他类型默认处理，可按需扩展
                     v_insert_values := v_insert_values || NVL(v_value, 'NULL') || ',';
            END CASE;
        END LOOP;

        -- 移除末尾逗号，拼接完整INSERT语句并输出
        v_insert_values := RTRIM(v_insert_values, ',') || ')';
        DBMS_OUTPUT.PUT_LINE(v_insert_prefix || v_insert_values);
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(v_cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
        END IF;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

关键说明 #

• 动态列适配：通过all_tab_columns数据字典视图自动读取目标表的列名、数据类型与顺序，无需硬编码列信息。
• 数据类型兼容：针对字符串自动转义内部单引号，日期转换为标准TO_DATE格式，数字直接输出，空值统一处理为NULL。
• 自定义筛选：v_filter_clause变量可根据不同表的关联逻辑修改，无需重构核心代码。
• 原生交互：通过&input_pknum接收用户输入参数，符合Oracle原生命令行交互方式。

适配其他5张表的方法 #

1. 修改v_table_name为目标表的完整名称（如ADMIN.OTHER_TABLE）。
2. 调整v_filter_clause为对应表的筛选逻辑（无需关联时可设为1=1）。
3. 若目标表有特殊数据类型（如CLOB、TIMESTAMP），可扩展CASE v_desc_tab(i).col_type分支补充处理逻辑。

使用方式 #

执行脚本后，DBMS_OUTPUT会输出所有符合条件的INSERT语句，直接复制这些语句到远程服务器执行即可重建表数据。

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