Oracle12c辅助表

在 Oracle 12c 和 19c 中, 不支持 SELECT 直接生成表值. 在 23c 中是支持的. 也就是说下面 SQL 会失败:

SELECT 123;

在早期的数据库中, 需要借助于辅助表 DUAL 来查询.

该表不实际存储数据, 是一个虚拟表
该表只有一行一列, 值为 X, 列名是: DUMMY

借助于该表与 CTE 可以构建任意的辅助表.

例如需要单个表时:

SELECT 123 FROM DUAL;

例如需要一个 0 到 99 的辅助表, 可以写成:

WITH tbl AS (
     SELECT 0 AS n FROM DUAL UNION ALL
     SELECT 1 AS n  FROM DUAL UNION ALL
     SELECT 2 AS n  FROM DUAL UNION ALL
     SELECT 3 AS n  FROM DUAL UNION ALL
     SELECT 4 AS n  FROM DUAL UNION ALL
     SELECT 5 AS n  FROM DUAL UNION ALL
     SELECT 6 AS n  FROM DUAL UNION ALL
     SELECT 7 AS n  FROM DUAL UNION ALL
     SELECT 8 AS n  FROM DUAL UNION ALL
     SELECT 9 AS n  FROM DUAL
)
SELECT 
     t1.n * 10 + t2.n num 
FROM      
     tbl t1 
     CROSS JOIN 
     tbl t2 
ORDER BY num;