生成Oracle SQL脚本：将小表数据转为UNION ALL形式单字符串查询

以下脚本接收table_name作为输入参数，会自动读取指定小表的所有列与数据，将每一行数据转换为SELECT ... FROM DUAL的形式，最终拼接成带UNION ALL的完整查询语句并输出为单个字符串。

DECLARE
    v_table_name VARCHAR2(100) := '&table_name'; -- 输入目标表名
    v_col_list   CLOB;
    v_dynamic_sql CLOB;
    v_final_result CLOB;
BEGIN
    -- 第一步：获取表的所有列名，构建列表达式模板
    SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)
    INTO v_col_list
    FROM USER_TAB_COLUMNS
    WHERE TABLE_NAME = UPPER(v_table_name);

    -- 第二步：构建动态SQL，将每一行数据转为SELECT子句并拼接UNION ALL
    v_dynamic_sql := 'SELECT LISTAGG(
        ''SELECT '' || 
        LISTAGG(
            CASE 
                WHEN ' || COLUMN_NAME || ' IS NULL THEN ''NULL''
                WHEN DATA_TYPE IN (''VARCHAR2'', ''CHAR'', ''CLOB'') THEN '''''''''' || REPLACE(' || COLUMN_NAME || ', '''''''''', '''''''''''''') || ''''''''''
                WHEN DATA_TYPE IN (''DATE'', ''TIMESTAMP'') THEN ''TO_DATE('''''''' || TO_CHAR(' || COLUMN_NAME || ', ''YYYY-MM-DD HH24:MI:SS'') || '''''', ''YYYY-MM-DD HH24:MI:SS'')''
                ELSE TO_CHAR(' || COLUMN_NAME || ') 
            END, '', ''
        ) WITHIN GROUP (ORDER BY COLUMN_ID) || '' FROM DUAL''
        , '' UNION ALL '' WITHIN GROUP (ORDER BY ROWNUM)
    FROM ' || v_table_name;

    -- 执行动态SQL并获取最终拼接结果
    EXECUTE IMMEDIATE v_dynamic_sql INTO v_final_result;

    -- 输出结果
    DBMS_OUTPUT.PUT_LINE(v_final_result);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('执行错误：' || SQLERRM);
END;
/

使用说明 #

• 执行脚本时，输入目标小表名（如CONTINENT/COUNTRY）
• 脚本自动处理字符串、日期、数字、空值等不同数据类型的格式转换
• 输出结果可直接作为完整SQL语句执行

示例输出 #

Select 1 as id, '亚洲' as continent_name from dual --first row
union all
Select 2 as id, '欧洲' as continent_name from dual --second row
...
union all
Select 7 as id, '南极洲' as continent_name from dual --last row

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