Oracle SQL中向CTE插入新行遇语法错误，求解决方法

你遇到的问题核心是：CTE是临时查询结果集，不是可写入的持久化表，Oracle不支持直接用INSERT INTO操作CTE。要实现"向CTE结果里添加新行"的需求，正确的做法是把原CTE的数据和新增行通过UNION ALL合并。

修正后的代码 #

WITH region AS (
    SELECT City, County
    FROM clarity_pos cp
    LEFT JOIN zc_county zc ON zc.county_c = cp.county_c
    WHERE cp.county_c IS NOT NULL
    -- 用UNION ALL合并新增行
    UNION ALL
    SELECT 'AMSTON', 'TOLLAND' FROM DUAL
    UNION ALL
    SELECT 'BURLINGTON', 'HARTFORD' FROM DUAL
    UNION ALL
    SELECT 'COVENTRY', 'TOLLAND' FROM DUAL
    UNION ALL
    SELECT 'DARIAN', 'FAIRFIELD' FROM DUAL
    UNION ALL
    SELECT 'EAST LYME', 'NEW LONDON' FROM DUAL
)
SELECT City, County
FROM region;

如果你的Oracle版本是12c及以上，也可以用VALUES子句简化新增行部分：

WITH region AS (
    SELECT City, County
    FROM clarity_pos cp
    LEFT JOIN zc_county zc ON zc.county_c = cp.county_c
    WHERE cp.county_c IS NOT NULL
    UNION ALL
    SELECT * FROM (
        VALUES
            ('AMSTON', 'TOLLAND'),
            ('BURLINGTON', 'HARTFORD'),
            ('COVENTRY', 'TOLLAND'),
            ('DARIAN', 'FAIRFIELD'),
            ('EAST LYME', 'NEW LONDON')
    ) AS temp(City, County)
)
SELECT City, County
FROM region;

关键注意事项 #

• 确保新增行的列数、数据类型和原CTE的查询结果完全匹配，否则会报错。
• 用UNION ALL不会对合并后的结果去重，如果需要去重，替换成UNION即可（但会牺牲一点性能）。

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