ai新特性实战：掌握WITH子句嵌套技巧

Oracle AI Database 23.26.2 这次更新，终于把 WITH 子句的嵌套限制给解除了。简单说，现在你可以在 WITH 里面再套 WITH，而且只要语法上允许放 WITH 的地方，基本都能这么玩。

回想一下以前有多憋屈：CTE（公共表表达式）只能在查询的最顶层定义；子查询内部想用 WITH？没门；WITH 内部再嵌套 WITH？更是想都别想。这次更新直接把这几道枷锁全拆了。

特性介绍

新特性带来的变化很直观：

• 可以在 WITH 子句的 SELECT 主体里定义内层的 WITH 子句
• FROM 子句的子查询里也能使用 WITH
• INSERT … SELECT 中支持嵌套 WITH
• 内层 WITH 可以引用外层 WITH 的数据，也就是所谓的关联引用
• 支持三层甚至更多层的嵌套

不过，也得注意它暂时还做不到的事：

• 递归嵌套（即在 WITH 内部直接引用自身）还是不行
• 嵌套 WITH 子句内部不能定义 PL/SQL 函数
• 同样也不能使用表值构造器

另外，inline 和 materialize 这两个提示依然可以用，但在嵌套场景下可能会被忽略，好在不影响非嵌套查询块的行为。

适用场景

这个特性的价值，在实际场景里会非常明显：

• 复杂报表：可以把多步骤的数据转换逻辑拆成层次化的 CTE，可读性瞬间提高好几个档次。
• 数据管道：在 INSERT … SELECT 里用嵌套 WITH 实现复杂加工，整个流程一目了然。
• 分析查询：对中间结果先排名再分类，再也不需要写一堆重复的子查询了。
• 代码复用：把公共计算逻辑封装在内层 WITH 里，外层可以多处引用，维护起来也省心。
• 迁移兼容：对于从 PostgreSQL、SQL Server 这类已经支持嵌套 CTE 的数据库迁移过来的项目，平滑度会高很多。

实战演示

还是用老熟人 NFTEST 用户来演示，该用户已授权 DB_DEVELOPER_ROLE 角色，所有操作都在 NFTEST 下执行。

创建测试表并添加测试数据

CREATE TABLE nwc_sales (
    sale_id NUMBER PRIMARY KEY,
    product VARCHAR2(30),
    region VARCHAR2(30),
    amount NUMBER,
    sale_date DATE
);

INSERT INTO nwc_sales VALUES (1, 'Widget', 'East', 100, DATE'2026-01-15');
INSERT INTO nwc_sales VALUES (2, 'Widget', 'West', 150, DATE'2026-01-20');
INSERT INTO nwc_sales VALUES (3, 'Gadget', 'East', 200, DATE'2026-02-10');
INSERT INTO nwc_sales VALUES (4, 'Gadget', 'West', 120, DATE'2026-02-15');
INSERT INTO nwc_sales VALUES (5, 'Widget', 'East', 80, DATE'2026-03-01');
INSERT INTO nwc_sales VALUES (6, 'Gadget', 'West', 300, DATE'2026-03-05');
COMMIT;

image.png

测试1：基本测试

先看一个最直接的示例：在一个 WITH 子句里嵌套另一个 WITH。

WITH outer_cte AS (
    WITH inner_cte AS (
        SELECT product, region, SUM(amount) AS region_total
        FROM nwc_sales
        GROUP BY product, region
    )
    SELECT product, SUM(region_total) AS total,
           MAX(region_total) AS top_region_total
    FROM inner_cte
    GROUP BY product
)
SELECT product, total, top_region_total
FROM outer_cte
ORDER BY total DESC;

image.png

测试2：多层嵌套

试试三层的嵌套，逻辑上分三步：先算每个产品在各区域的销售，再在区域内排名，最后把排名结果分类。

WITH level1 AS (
    SELECT product, region, SUM(amount) AS region_total
    FROM nwc_sales GROUP BY product, region
),
level2 AS (
    WITH region_rank AS (
        SELECT product, region, region_total,
               RANK() OVER (PARTITION BY product ORDER BY region_total DESC) AS rnk
        FROM level1
    )
    SELECT product, region, region_total, rnk
    FROM region_rank
)
SELECT product, region, region_total,
       CASE
           WHEN rnk = 1 THEN 'TOP'
           ELSE 'OTHER'
       END AS tier
FROM level2
ORDER BY product, rnk;

image.png

测试3：在带有相关性的 FROM 子句子查询中嵌套使用 WITH

这次是把嵌套 WITH 放在 FROM 子句的子查询里，还顺手关联了外层 CTE 的数据。

WITH outer_cte AS (
    SELECT product, SUM(amount) AS total
    FROM nwc_sales GROUP BY product
)
SELECT sub.product, sub.total, sub.sale_count
FROM (
    WITH inner_cte AS (
        SELECT product, COUNT(*) AS sale_count
        FROM nwc_sales GROUP BY product
    )
    SELECT o.product, o.total, i.sale_count
    FROM outer_cte o
    JOIN inner_cte i ON o.product = i.product
) sub
ORDER BY sub.total DESC;

image.png

测试4：在 INSERT INTO 中嵌套 WITH 子句

最后演示一个更实际的应用：直接把嵌套 WITH 查询的结果插入到汇总表中。

CREATE TABLE nwc_summary (
    product VARCHAR2(30),
    total NUMBER,
    a vg_sale NUMBER,
    num_sales NUMBER
);

INSERT INTO nwc_summary
WITH product_stats AS (
    WITH sale_details AS (
        SELECT product,
               SUM(amount) AS total,
               A VG(amount) AS a vg_amt,
               COUNT(*) AS num_sales
        FROM nwc_sales GROUP BY product
    )
    SELECT * FROM sale_details
)
SELECT product, total, a vg_amt, num_sales
FROM product_stats;

COMMIT;

-- 验证数据
SELECT product, total, a vg_sale, num_sales
FROM nwc_summary;

image.png

总结

本期对 Oracle AI Database 26ai 这个 23.26.2 版本新引入的 WITH 子句嵌套特性，从特性到实际场景做了比较完整的介绍和演示。这个能力对于一些复杂查询的编写来说，确实让思路更清晰了。老规矩，看完这篇，大概知道改了些啥就行。