Oracle AI Database 26ai (26.2) 中的三个 SQL 新特性

00. 写在前面

做了十几年 Oracle DBA，笔者有个深刻的体会：SQL 写法的进化速度，往往比数据库内核的进化速度慢得多。很多开发同学到现在还在用 10g 时代的写法，不是不想升级，而是没人告诉他们"原来还能这么写"。

Oracle 26ai 在 SQL 语法层面做了一次大的变更。本文挑三个笔者认为最实用的特性展开：JOIN TO ONE、嵌套 WITH 子句、SQL/JSON 路径表达式的条件逻辑。每个特性都有完整的建表、插数、对比演示，你可以直接复制到 26ai 环境里跑。

演示环境为 Oracle 26.2。

SQL> SELECT banner FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle AI Database 26ai Enterprise Edition Release 23.26.2.0.0 - Production

01. JOIN TO ONE：让笛卡尔积无处遁形

1.1 这个特性解决什么问题

做过报表的同学一定踩过这个坑：两个表做 1:N 关联，“to one” 那侧的表被静默重复了，查询结果莫名其妙多出好多行，排查半天才发现是关联方向写反了。

Oracle 官方博客说得直白：“The database does not catch the mistake… It is easy to overlook the fact it returns duplicate rows.”, "JOIN TO ONE is a modern join syntax… "

26ai 引入的 JOIN TO ONE 语法，核心思路就是把"行级膨胀"从静默错误变成显式报错。它要求你明确指定"主体表"（Row Widened Table），如果关联导致主体表出现重复行，直接抛 ORA-18640，强制你修正逻辑。

1.2 核心语法规则

规则	说明
主体表放 FROM	你想列出或统计哪张表的行，就把它放 FROM 子句
默认外连接	JOIN TO ONE 默认用 outer join，不是传统的 inner join
外键自动推断	如果表之间有外键，可以省略 ON 条件
方向必须正确	关联方向必须跟外键方向一致，否则报错
可显式指定 join 类型	支持 inner join 或 [left] outer join 修饰

1.3 实测对比

先建两张测试表：

-- 国家表（to one 侧，每个国家唯一）
CREATE TABLE countries (
    country_id   CHAR(2) PRIMARY KEY,
    country_name VARCHAR2(100) NOT NULL
);

-- 地址表（to many 侧，一个国家可以有多个地址）
CREATE TABLE locations (
    location_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    street_address VARCHAR2(200) NOT NULL,
    city           VARCHAR2(100),
    country_id     CHAR(2),
    CONSTRAINT fk_loc_countries FOREIGN KEY (country_id) 
        REFERENCES countries(country_id)
);

-- 插入数据
INSERT INTO countries VALUES ('CN', '中国');
INSERT INTO countries VALUES ('US', '美国');
INSERT INTO countries VALUES ('UK', '英国');
INSERT INTO countries VALUES ('JP', '日本');

INSERT INTO locations (street_address, city, country_id) 
VALUES ('上海市浦东新区世纪大道 88 号', '上海', 'CN');
INSERT INTO locations (street_address, city, country_id) 
VALUES ('1600 Amphitheatre Pkwy', 'Mountain View', 'US');
INSERT INTO locations (street_address, city, country_id) 
VALUES ('10 Downing Street', 'London', 'UK');

COMMIT;

传统 LEFT JOIN 写法：

SELECT c.country_name, l.street_address
FROM countries c
LEFT JOIN locations l ON c.country_id = l.country_id;

COUNTRY_NAME         STREET_ADDRESS
-------------------- ----------------------------------------
中国                 上海市浦东新区世纪大道 88 号
美国                 1600 Amphitheatre Pkwy
英国                 10 Downing Street
日本

使用 JOIN TO ONE 语法:

SELECT c.country_name, l.street_address
FROM countries c
JOIN TO ONE (locations l ON c.country_id = l.country_id);

COUNTRY_NAME         STREET_ADDRESS
-------------------- ----------------------------------------
中国                 上海市浦东新区世纪大道 88 号
美国                 1600 Amphitheatre Pkwy
英国                 10 Downing Street
日本

1.4 笔者的使用建议

JOIN TO ONE 不是要你替换所有 JOIN，它最适合的场景是：

1. 报表查询：主体表明确，关联表只是补充字段
2. REST API 数据组装：JSON Relational Duality 场景下，防止嵌套对象膨胀
3. 数据校验：快速发现模型设计中的关联方向错误

日常 OLTP 点查没必要硬上，但复杂报表里放一个 JOIN TO ONE，能帮你提前拦截很多隐性 bug。

02. 嵌套 WITH 子句与关联：复杂报表的救星

2.1 从"扁平 CTE"到"嵌套 CTE"

传统的 WITH 子句（Common Table Expression）是扁平的，所有 CTE 并列定义，互相引用时容易逻辑混乱。26ai 打破了限制，允许在一个 WITH 里面再嵌套另一个 WITH，而且内层 CTE 可以引用外层 CTE 的列。

Oracle 官方文档把这个能力归类为"New Features"，明确说明"Nesting WITH clauses within other WITH clauses, removing the earlier restriction."

2.2 实测：部门薪资分析报表

先建测试数据：

-- 1. 创建部门主表
CREATE TABLE departments (
    department_id   NUMBER PRIMARY KEY,
    department_name VARCHAR2(100) NOT NULL
);

-- 2. 创建员工从表
CREATE TABLE employees (
    employee_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    employee_name VARCHAR2(100) NOT NULL,
    department_id NUMBER NOT NULL,
    salary        NUMBER(10,2) NOT NULL,
    hire_date     DATE NOT NULL, -- 匹配 EXTRACT(YEAR FROM hire_date)
    CONSTRAINT fk_emp_dept_id FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 3. 插入部门数据
INSERT INTO departments (department_id, department_name) VALUES (10, '研发部');
INSERT INTO departments (department_id, department_name) VALUES (20, '市场部');
INSERT INTO departments (department_id, department_name) VALUES (30, '财务部');

-- 4. 插入员工数据
-- === 研发部 (10) ===
-- 2024年：招聘 1 人
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('少安', 10, 15000, TO_DATE('2024-03-15', 'YYYY-MM-DD'));
-- 2025年：招聘 2 人 (呈 GROWTH 趋势)
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('李四', 10, 13000, TO_DATE('2025-05-20', 'YYYY-MM-DD'));
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('王五', 10,  8000, TO_DATE('2025-08-11', 'YYYY-MM-DD'));
-- 2026年：招聘 1 人 (呈 DECLINE 趋势)
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('赵六', 10,  9000, TO_DATE('2026-02-10', 'YYYY-MM-DD'));

-- === 市场部 (20) ===
-- 2024年：招聘 1 人
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('钱七', 20,  6000, TO_DATE('2024-01-10', 'YYYY-MM-DD'));
-- 2025年：招聘 1 人 (与去年持平，呈 STABLE 趋势)
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('孙八', 20, 25000, TO_DATE('2025-07-19', 'YYYY-MM-DD'));
-- 2026年：招聘 2 人 (呈 GROWTH 趋势)
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('李九', 20, 15000, TO_DATE('2026-01-05', 'YYYY-MM-DD'));
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('周十', 20, 16000, TO_DATE('2026-04-12', 'YYYY-MM-DD'));

-- === 财务部 (30) ===
-- 2024年：招聘 2 人
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('孙八', 30, 25000, TO_DATE('2024-02-14', 'YYYY-MM-DD'));
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('李九', 30, 15000, TO_DATE('2024-11-05', 'YYYY-MM-DD'));
-- 2025年：招聘 1 人 (呈 DECLINE 趋势)
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('周十', 30, 16000, TO_DATE('2025-06-22', 'YYYY-MM-DD'));
-- 2026年：招聘 1 人 (与 2025 年持平，呈 STABLE 趋势)
INSERT INTO employees (employee_name, department_id, salary, hire_date) VALUES ('吴十一', 30, 18000, TO_DATE('2026-01-18', 'YYYY-MM-DD'));


COMMIT;

场景一：找出薪资高于部门平均的员工

传统写法需要子查询或重复计算：

SELECT e.department_id, e.employee_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
);

26ai 嵌套 WITH 写法，逻辑分层更清晰：

WITH 
    dept_stats AS (
        -- 内层 WITH：计算每个部门的平均薪资
        WITH avg_sal AS (
            SELECT department_id, AVG(salary) AS avg_sal
            FROM employees
            GROUP BY department_id
        )
        -- 外层 WITH 引用内层结果，再关联部门名
        SELECT d.department_id, d.department_name, a.avg_sal
        FROM departments d
        JOIN avg_sal a ON d.department_id = a.department_id
    )
SELECT e.employee_name, e.salary, ds.department_name, ds.avg_sal,
       e.salary - ds.avg_sal AS above_avg
FROM employees e
JOIN dept_stats ds ON e.department_id = ds.department_id
WHERE e.salary > ds.avg_sal;

输出：

EMP_NAME           SALARY DEPT_NAME       AVG_SALARY ABOVE_AVG
--------------- --------- --------------- ---------- ---------
少安             15000.00 研发部            11250.00   3750.00
李四             13000.00 研发部            11250.00   1750.00
孙八             25000.00 市场部            15500.00   9500.00
周十             16000.00 市场部            15500.00    500.00
孙八             25000.00 财务部            18500.00   6500.00

场景二：分析各部门年度招聘趋势，多层 WITH + 关联

WITH 
    yearly_summary AS (
        SELECT department_id, 
               EXTRACT(YEAR FROM hire_date) AS hire_year,
               COUNT(*) AS hire_count,
               AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id, EXTRACT(YEAR FROM hire_date)
    ),
    dept_trend AS (
        -- 嵌套WITH：引用外部WITH定义的表
        WITH yearly_growth AS (
            SELECT department_id, hire_year, hire_count,
                   LAG(hire_count) OVER (PARTITION BY department_id ORDER BY hire_year) AS prev_count
            FROM yearly_summary
        )
        SELECT department_id, hire_year, hire_count,
               CASE 
                   WHEN prev_count IS NULL THEN 'NEW'
                   WHEN hire_count > prev_count THEN 'GROWTH'
                   WHEN hire_count < prev_count THEN 'DECLINE'
                   ELSE 'STABLE'
               END AS trend
        FROM yearly_growth
    )
SELECT d.department_name, dt.hire_year, dt.hire_count, dt.trend
FROM departments d
JOIN dept_trend dt ON d.department_id = dt.department_id
ORDER BY d.department_name, dt.hire_year;

执行后，外层 CTE 引用内层聚合，配合窗口函数计算，将自动按部门名称排序，并呈现三个部门各自的完整发展轨迹：

DEPT_NAME        YEAR COUNT TREND
--------------- ----- ----- ----------
市场部           2024     1 NEW
市场部           2025     1 STABLE
市场部           2026     2 GROWTH
研发部           2024     1 NEW
研发部           2025     2 GROWTH
研发部           2026     1 DECLINE
财务部           2024     2 NEW
财务部           2025     1 DECLINE
财务部           2026     1 STABLE

9 rows selected.

2.3 笔者的使用建议

嵌套 WITH 不是炫技，它解决的是"复杂报表 SQL 可读性"这个老大难问题。笔者建议：

1. 超过 3 个 CTE 的查询，考虑用嵌套结构分层，每层只做一件事
2. 内层 CTE 命名要直白，比如 avg_sal、yearly_growth，别用 t1、t2
3. 不要无限嵌套，超过两层嵌套建议拆成视图或存储过程

03. SQL/JSON 路径表达式支持 DECODE 与 CASE

3.1 JSON 查询的条件逻辑进化

26ai 之前，想在 JSON 查询里做条件判断，得先把 JSON 值抽出来，在 SQL 层用 CASE 或 DECODE 处理。26ai 把这个能力下沉到了 JSON 路径表达式内部，你可以在路径里直接写条件逻辑。

更实际的变化是：26ai 对原生 JSON 类型的点路径语法（Dot Notation）做了大幅增强，现在 表.列.键名 这种写法可以直接用，再配合 SQL 层的 DECODE/CASE，代码简洁度提升一个档次。

3.2 实测：员工 JSON 文档的分级处理

CREATE TABLE employees_json (
    emp_id NUMBER PRIMARY KEY,
    info JSON
);

INSERT INTO employees_json VALUES (1, 
    '{"name":"Alice", "salary":8500, "department":"IT"}');
INSERT INTO employees_json VALUES (2, 
    '{"name":"Bob", "salary":12000, "department":"HR"}');
INSERT INTO employees_json VALUES (3, 
    '{"name":"ShawnYan", "salary":25000, "department":"Admin"}');

COMMIT;

传统写法：JSON_VALUE 抽出来再判断

SELECT emp_id,
       JSON_VALUE(info, '$.name') AS emp_name,
       JSON_VALUE(info, '$.salary') AS emp_salary,
       CASE 
           WHEN TO_NUMBER(JSON_VALUE(info, '$.salary')) < 10000 THEN 'JUNIOR'
           WHEN TO_NUMBER(JSON_VALUE(info, '$.salary')) < 20000 THEN 'SENIOR'
           ELSE 'EXECUTIVE'
       END AS emp_level
FROM employees_json;

输出：

     ID NAME            SALARY       LEVEL
------- --------------- ------------ ------------
      1 Alice           8500         JUNIOR
      2 Bob             12000        SENIOR
      3 ShawnYan        25000        EXECUTIVE

26ai 新写法：点路径直接取，在JSON路径表达式中使用CASE

SELECT e.emp_id,
       e.info.name.string() AS emp_name,  -- 点路径，无需 JSON_VALUE，.string() 做类型转换
       e.info.salary AS emp_salary,       -- 直接访问 JSON 属性
       CASE 
           WHEN e.info.salary < 10000 THEN 'JUNIOR'
           WHEN e.info.salary < 20000 THEN 'SENIOR'
           ELSE 'EXECUTIVE'
       END AS emp_level
FROM employees_json e;

输出：

     ID NAME            SALARY       LEVEL
------- --------------- ------------ ------------
      1 Alice           8500         JUNIOR
      2 Bob             12000        SENIOR
      3 ShawnYan        25000        EXECUTIVE

与传统写法输出结果一致。

注意 e.info.name.string() 这个写法。26ai 的点路径返回的是 JSON 标量，加 .string() 可以确保类型安全，避免隐式转换的坑。

3.3 DECODE 做部门名映射

在 Oracle 26ai 中，针对 JSON 类型数据，推荐直接使用点路径语法（Dot-Notation）将数据提取到 SQL 层，然后再通过 SQL 原生的 DECODE 函数进行分支转换。这种写法完全避开了在字符串内部嵌套引号的烦恼，可读性极高，且执行效率更优：

COLUMN dept_full_name FORMAT A25 HEADING "DEPARTMENT";

SELECT e.emp_id,
       DECODE(
           e.info.department.string(),
           'IT',   'Information Technology',
           'HR',   'Human Resources',
           'EXEC', 'Executive Office',
           e.info.department.string()   -- 默认值：原样返回
       ) AS dept_full_name
FROM employees_json e;

输出：

     ID DEPARTMENT
------- -------------------------
      1 Information Technology
      2 Human Resources
      3 Admin

04. 总结

测完 26ai 这三个 SQL 新特性，笔者的几点感受：

1. JOIN TO ONE 是防呆设计典范。它不帮你写 SQL，但能在你写错的时候及时喊停。对于复杂报表和 JSON Duality 场景，这个语法能拦截大量隐性笛卡尔积 bug。

2. 嵌套 WITH 让复杂查询有了结构感。以前写一个 200 行的 WITH 查询，CTE 之间互相引用，逻辑像 spaghetti。现在可以分层封装，每层只做一件事，可读性提升明显。

3. JSON 点路径 + SQL 条件逻辑是最佳组合。不要试图在 JSON 路径字符串里写复杂的 CASE，而是把数据抽到 SQL 层，用原生的 DECODE/CASE 处理。26ai 的点路径语法让这个过程变得极其自然。

4. 建议团队制定 SQL 编码规范升级计划。不要指望开发同学自发使用新语法，DBA 和架构师需要主动推广，在 Code Review 里逐步引入 JOIN TO ONE 和嵌套 WITH，半年后团队代码质量会有质变。

---

Have a nice day ~ ☕

🌻 近期内容 ▼

• Oracle Skills开源：AI工程正在进入"技能时代"
• 苦等三年！Oracle AI Database 26ai本地服务器版终于来了
• MySQL 8.0结束生命周期，8.4.9 LTS、9.7.0发版上线：一个时代的交接与新生

👉 这里有得聊

如果对国产基础软件（操作系统、数据库、中间件）、AI、Vibe Coding、OpenClaw 、Hermes Agent 等感兴趣，可以加群一起聊聊。关注微信公众号：(少安事务所)，后台回复[群]，即可看到入口。如果这篇文章为你带来了灵感或启发，请帮忙『点赞、推荐、转发』吧，感谢！ღ( ´･ᴗ･` )~