胖头鱼的技术专栏-440 26ai新特性实战：WITH子句嵌套（20260707）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database
PostgreSQL ACE
10年+数据库行业经验
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP，ITPUB认证专家
圈内拥有“总监”称号，非著 名社恐（社交恐怖分子）
全网同名：胖头鱼的鱼缸
ITPUB：yhw1809
除授权转载并标明出处外，均为“非法”抄袭

特性介绍

Oracle AI Database 23.26.2 解除了此前对 WITH 子句嵌套的限制，现在支持在其他 WITH 子句内部嵌套 WITH 子句，并允许在 WITH 子句有效的任何位置使用。

此前的限制：

• WITH 子句（CTE，公共表表达式）只能在查询的最外层定义
• 子查询内部不能使用 WITH 子句
• WITH 子句内部不能再嵌套 WITH 子句

新特性允许：

• 在 WITH 子句的 SELECT 主体中定义内层 WITH 子句
• 在 FROM 子句的子查询中使用 WITH 子句
• 在 INSERT … SELECT 中使用嵌套 WITH 子句
• 内层 WITH 子句引用外层 WITH 子句的数据（关联引用）
• 多层嵌套（三层及以上）

仍然不支持的：

• 递归嵌套（WITH 内部直接引用自身）
• 嵌套 WITH 子句内部定义 PL/SQL 函数
• 嵌套 WITH 子句内部使用表值构造器（Table Value Constructors）

关于内联提示：现有提示（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;

测试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_totalFROM outer_cteORDER BY total DESC;

测试2：多层嵌套

3层：

• 第一级：按产品和区域分类的销售
• 第二级：对产品内的区域进行排名（包含嵌套的WITH语句）

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 level2ORDER BY product, rnk;

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

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;

测试4：在insert into中嵌套WITH子句

CREATE TABLE nwc_summary (
  product   VARCHAR2(30),
  total     NUMBER,
  avg_sale  NUMBER,
  num_sales NUMBER);
  
INSERT INTO nwc_summary
WITH product_stats AS (
  WITH sale_details AS (
      SELECT product, SUM(amount) AS total, AVG(amount) AS avg_amt, COUNT(*) AS num_sales
      FROM nwc_sales GROUP BY product
  )  SELECT * FROM sale_details
)
SELECT product, total, avg_amt, num_sales FROM product_stats;
COMMIT;
-- 验证数据
SELECT product, total, avg_sale, num_salesFROM nwc_summary;

总结

本期对Oracle AI Database 26ai，23.26.2版本新引入的WITH子句嵌套进行了完整介绍与实战演示。

老规矩，知道写了些啥。