Oracle WITH AS临时结果集是否支持多查询同时引用

WITH AS（公用表表达式，简称CTE）的作用域仅限定在它直接附属的单条SQL语句内。你当前的写法把CTE定义放在第一条SELECT INTO之前，第二条独立的SELECT语句已经脱离了该CTE的作用范围，无法识别src对象，因此执行失败。
Oracle本身完全支持同一份CTE在单条SQL语句内被多次引用，只是不支持跨独立SQL语句访问已定义的CTE。
另外你当前src子查询的写法存在逻辑bug：ROWNUM会在ORDER BY执行前分配，会导致生成的xIndex和CreateTime倒序的预期不一致，建议改用窗口函数ROW_NUMBER()生成排序后的序号。 #

方案1：单次查询聚合赋值（推荐，性能最优） #

不需要拆成两个独立SELECT，通过条件聚合一次性取出xIndex=1和xIndex=2对应的字段值，仅需计算一次src结果集，性能最高：

WITH src AS (
    -- 用ROW_NUMBER修正排序后序号的生成逻辑
    SELECT ROW_NUMBER() OVER(ORDER BY "CreateTime" DESC) as xIndex,
           "LoginResult", 
           ROUND(SYSDATE -"LastLoginTime") as "lastLogonDays"
    FROM "tb_UserStatus" 
    WHERE "UserId" = userId
)
SELECT 
    MAX(CASE WHEN xIndex = 1 THEN "lastLogonDays" END),
    MAX(CASE WHEN xIndex = 1 THEN "LoginResult" END),
    MAX(CASE WHEN xIndex = 2 THEN "lastLogonDays" END),
    MAX(CASE WHEN xIndex = 2 THEN "LoginResult" END)
INTO lastLogonDays, hasLogon, reverse2ndDaysDiff, reverse2ndLoginResult
FROM src
WHERE xIndex IN (1,2);

这里使用MAX聚合是因为每个xIndex仅对应1条记录，聚合后不会丢失值，也不会触发多行返回的赋值错误。

方案2：PL/SQL集合缓存结果集（适合多场景复用） #

如果后续需要查询更多xIndex对应的数值，反复写CASE语句过于繁琐，可以先一次性将src的结果存入PL/SQL集合，后续直接从内存中的集合取值，不需要重复查询原表：

DECLARE
    -- 定义和src结果匹配的集合结构
    TYPE t_src_rec IS RECORD(
        xIndex NUMBER,
        "LoginResult" "tb_UserStatus"."LoginResult"%TYPE,
        "lastLogonDays" NUMBER
    );
    TYPE t_src_tab IS TABLE OF t_src_rec INDEX BY PLS_INTEGER;
    src_tab t_src_tab;
BEGIN
    -- 一次性将查询结果存入内存集合
    SELECT ROW_NUMBER() OVER(ORDER BY "CreateTime" DESC) as xIndex,
           "LoginResult", 
           ROUND(SYSDATE -"LastLoginTime") as "lastLogonDays"
    BULK COLLECT INTO src_tab
    FROM "tb_UserStatus" 
    WHERE "UserId" = userId;

    -- 按需遍历集合给变量赋值
    FOR i IN 1..src_tab.COUNT LOOP
        CASE src_tab(i).xIndex
            WHEN 1 THEN
                lastLogonDays := src_tab(i)."lastLogonDays";
                hasLogon := src_tab(i)."LoginResult";
            WHEN 2 THEN
                reverse2ndDaysDiff := src_tab(i)."lastLogonDays";
                reverse2ndLoginResult := src_tab(i)."LoginResult";
        END CASE;
    END LOOP;
END;
/

单条SQL内多次引用CTE的正确示例 #

只要所有对CTE的引用都处于同一条SQL的作用域范围内，可以直接多次引用，不需要重复定义：

WITH src AS (
    SELECT ROW_NUMBER() OVER(ORDER BY "CreateTime" DESC) as xIndex,
           "LoginResult", 
           ROUND(SYSDATE -"LastLoginTime") as "lastLogonDays"
    FROM "tb_UserStatus" 
    WHERE "UserId" = userId
)
SELECT s1."lastLogonDays" idx1_days,
       s1."LoginResult" idx1_login,
       s2."lastLogonDays" idx2_days,
       s2."LoginResult" idx2_login
FROM src s1
CROSS JOIN src s2
WHERE s1.xIndex = 1 AND s2.xIndex = 2;

上述语句中s1、s2均引用同一个src CTE，可以正常执行。 #

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