Oracle SQL中WITH子句与UNION ALL结合使用报错问题咨询

核心错误原因 #

你遇到的"Inappropriate into"错误，根源是不能用UNION ALL拼接多个带INTO子句的SELECT语句。UNION ALL会将多个查询结果合并为单一结果集，但每个SELECT都试图把结果写入不同变量，这不符合Oracle的语法规则，直接触发错误。

修正方案 #

1. 拆分独立查询，分别赋值

把每个求和查询单独执行，彼此独立赋值，不要用UNION ALL连接：

WITH SS AS (
    SELECT COUNT(ROWNUM) AS S001
    FROM x.MSSE A
    WHERE SUCCESS_STATUS='F'
    UNION ALL
    SELECT COUNT(ROWNUM) AS S001
    FROM X.MSSE A
    WHERE SUCCESS_STATUS='N'
),
S333 AS (
    SELECT /*+ INDEX (A MSSEADTD_SI02) */
           COUNT(ROWNUM) AS S333_
    FROM X.MSSE A
    WHERE SUCCESS_STATUS='M'
    UNION ALL
    SELECT /*+ INDEX (A MSSEORMD_SI03) */
           COUNT(ROWNUM) AS S333_
    FROM HHL7.MSSEORMD A
    WHERE SUCCESS_STATUS='A'
),
LIS AS (
    SELECT /*+ INDEX (A MSSEORUD_SI02) */
           COUNT(ROWNUM) AS LIS_
    FROM A.MSS A
    WHERE SUCCESS_STATUS='D'
)
-- 单独执行每个赋值操作
SELECT SUM(S001) INTO Value_A FROM SS;
SELECT SUM(S333_) INTO VALUE_C FROM S333;
SELECT SUM(LIS_) INTO VALUE_D FROM LIS;

-- 后续逻辑保留
IF Value_A > 30000 THEN  
    -- DO THIS 
END IF;

IF VALUE_C > 30000 THEN
    -- DO THAT 
END IF;

2. 修复原有语法漏洞

原代码还有多处语法错误，必须修正：

• 移除SS定义中多余的外层括号
• 补全所有WHERE子句末尾的闭合括号
• 补全LIS子查询的闭合括号
• 修正无效注释符号（原代码中的/) */改为标准注释格式或直接移除）

3. 可选优化：一次性查询赋值

如果想减少上下文切换，提升效率，可以通过单条SELECT语句一次性将多个结果存入变量：

WITH SS AS (
    SELECT COUNT(ROWNUM) AS S001
    FROM x.MSSE A
    WHERE SUCCESS_STATUS='F'
    UNION ALL
    SELECT COUNT(ROWNUM) AS S001
    FROM X.MSSE A
    WHERE SUCCESS_STATUS='N'
),
S333 AS (
    SELECT /*+ INDEX (A MSSEADTD_SI02) */
           COUNT(ROWNUM) AS S333_
    FROM X.MSSE A
    WHERE SUCCESS_STATUS='M'
    UNION ALL
    SELECT /*+ INDEX (A MSSEORMD_SI03) */
           COUNT(ROWNUM) AS S333_
    FROM HHL7.MSSEORMD A
    WHERE SUCCESS_STATUS='A'
),
LIS AS (
    SELECT /*+ INDEX (A MSSEORUD_SI02) */
           COUNT(ROWNUM) AS LIS_
    FROM A.MSS A
    WHERE SUCCESS_STATUS='D'
)
SELECT
    (SELECT SUM(S001) FROM SS),
    (SELECT SUM(S333_) FROM S333),
    (SELECT SUM(LIS_) FROM LIS)
INTO Value_A, VALUE_C, VALUE_D
FROM DUAL;

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