Oracle中如何用SQL生成任意规模的模拟数据？

你的原SQL报错原因是：内层子查询返回了10列，但外层将其作为单个字段选取，Oracle不允许这种将多列子查询当作单列使用的写法。

一、固定规模（以10行10列为例） #

可以通过生成行号与列号的笛卡尔积，再用PIVOT转置得到目标结构，以下是可行的单条SQL：

SELECT *
FROM (
    -- 生成行、列标识及模拟值（这里用行号作为模拟值，可替换为随机数等）
    SELECT
        r.row_id,
        'col' || c.col_idx AS col_name,
        r.row_id AS dummy_value
    FROM (
        -- 生成10行
        SELECT level AS row_id FROM dual CONNECT BY level <= 10
    ) r
    -- 笛卡尔积生成10列
    CROSS JOIN (
        SELECT level AS col_idx FROM dual CONNECT BY level <= 10
    ) c
)
-- 转置为列
PIVOT (
    MAX(dummy_value)
    FOR col_name IN (
        'col1' AS col1, 'col2' AS col2, 'col3' AS col3, 'col4' AS col4, 'col5' AS col5,
        'col6' AS col6, 'col7' AS col7, 'col8' AS col8, 'col9' AS col9, 'col10' AS col10
    )
);

如果需要不同的模拟值，比如随机整数，可将r.row_id替换为TRUNC(dbms_random.value(1, 1000))。

二、动态任意规模（支持N行M列） #

如果需要无需硬编码列名的动态规模生成，可借助Oracle的PIVOT XML实现单条SQL查询：

WITH config AS (
    -- 这里修改行数和列数参数
    SELECT 100 AS row_count, 100 AS col_count FROM dual
),
row_source AS (
    SELECT level AS row_id FROM dual, config CONNECT BY level <= row_count
),
col_source AS (
    SELECT level AS col_id FROM dual, config CONNECT BY level <= col_count
),
raw_data AS (
    SELECT
        row_id,
        'col' || col_id AS col_name,
        -- 自定义模拟值，这里用0-100的随机数
        TRUNC(dbms_random.value(0, 101)) AS dummy_value
    FROM row_source
    CROSS JOIN col_source
)
SELECT *
FROM raw_data
PIVOT XML (
    MAX(dummy_value)
    FOR col_name IN (SELECT 'col' || level FROM dual, config CONNECT BY level <= col_count)
);

该查询会返回包含XML类型列的结果，其中XML内包含所有列的模拟数据。如果需要标准的关系型列结构，最优方式是使用动态SQL（需借助PL/SQL块，但仍可作为单条执行语句）：

DECLARE
    v_row_cnt NUMBER := 100;
    v_col_cnt NUMBER := 100;
    v_sql VARCHAR2(4000);
BEGIN
    v_sql := 'SELECT * FROM (
        SELECT r.row_id, ''col''||c.col_idx AS col_name, TRUNC(dbms_random.value(0,101)) AS val
        FROM (SELECT level row_id FROM dual CONNECT BY level <= '||v_row_cnt||') r
        CROSS JOIN (SELECT level col_idx FROM dual CONNECT BY level <= '||v_col_cnt||') c
    ) PIVOT (MAX(val) FOR col_name IN (';
    
    FOR i IN 1..v_col_cnt LOOP
        v_sql := v_sql || '''col'||i||''' AS col'||i||CASE WHEN i < v_col_cnt THEN ',' ELSE '' END;
    END LOOP;
    
    v_sql := v_sql || '))';
    EXECUTE IMMEDIATE v_sql;
END;
/

总结 #

• 固定规模的模拟数据完全可以通过单条SQL实现，推荐使用笛卡尔积+PIVOT的方式，性能和可读性都较好。
• 动态规模场景下，单条SQL可通过PIVOT XML实现；若需要标准关系型结果，动态SQL是最优选择，仅需修改参数即可生成任意行列的模拟数据。

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