Oracle 23ai 新特性 SQL 详解

适用版本：Oracle Database 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

Oracle 23ai 引入大量新 SQL 特性[1]：

详细见：Oracle 23c 新特性 SQL。

---

2. BOOLEAN 类型

2.1 基本

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  is_active BOOLEAN DEFAULT TRUE,
  is_admin BOOLEAN DEFAULT FALSE
);

INSERT INTO employees (id, is_active) VALUES (1, TRUE);
INSERT INTO employees (id, is_active) VALUES (2, FALSE);

-- 查询
SELECT * FROM employees WHERE is_active;
SELECT * FROM employees WHERE is_active = TRUE;
SELECT * FROM employees WHERE NOT is_admin;

2.2 PL/SQL

CREATE OR REPLACE PROCEDURE check_active(p_id NUMBER) IS
  v_active BOOLEAN;
BEGIN
  SELECT is_active INTO v_active FROM employees WHERE id = p_id;
  
  IF v_active THEN
    DBMS_OUTPUT.PUT_LINE('Active');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Inactive');
  END IF;
END;
/

---

3. VECTOR 类型

3.1 基本

CREATE TABLE docs (
  id NUMBER PRIMARY KEY,
  content CLOB,
  embedding VECTOR(768, FLOAT32)
);

-- 插入
INSERT INTO docs VALUES (1, 'text', '[0.1, 0.2, 0.3, ...]');

3.2 相似查询

-- 向量相似度
SELECT id, content, 
  VECTOR_DISTANCE(embedding, :query_vec, COSINE) AS distance
FROM docs
ORDER BY distance
FETCH FIRST 10 ROWS ONLY;

-- 向量索引
CREATE VECTOR INDEX vec_idx ON docs(embedding)
  ORGANIZATION NEIGHBOR PARTITIONS
  DISTANCE COSINE
  WITH TARGET ACCURACY 95;

3.3 转换

-- 文本到向量
SELECT VECTOR_EMBEDDING(content USING ALL_MINILM_L12_V2) 
FROM docs;

---

4. JSON Relational Duality

4.1 创建

-- 基表
CREATE TABLE employees (id NUMBER PRIMARY KEY, name VARCHAR2(100), dept_id NUMBER);
CREATE TABLE departments (id NUMBER PRIMARY KEY, name VARCHAR2(100));

-- Duality View
CREATE JSON DUALITY VIEW employees_dv AS
  SELECT e.id, e.name,
    JSON_OBJECT(d.id, d.name) AS department
  FROM employees e
  LEFT JOIN departments d ON e.dept_id = d.id
  GROUP BY e.id;

-- 查询
SELECT data FROM employees_dv;

-- 更新（自动同步到基表）
UPDATE employees_dv SET data = JSON_TRANSFORM(data, SET '$.name' = 'Alice') 
WHERE JSON_VALUE(data, '$.id') = 1;

详细见：Oracle JSON 处理详解。

---

5. SQL 简化

5.1 IF EXISTS

-- 表
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees (...);

-- 视图
DROP VIEW IF EXISTS v_emp;

-- 索引
DROP INDEX IF EXISTS idx_emp;

5.2 注释

SELECT * FROM employees; -- 行注释
-- 完整支持

---

6. GROUP BY 简化

6.1 GROUP BY 列别名

SELECT EXTRACT(YEAR FROM hire_date) AS yr, COUNT(*) 
FROM employees
GROUP BY yr;  -- 直接用别名

6.2 GROUP BY 位置

SELECT EXTRACT(YEAR FROM hire_date), COUNT(*) 
FROM employees
GROUP BY 1;  -- 位置

---

7. SELECT 别名

7.1 直接

-- 23ai
SELECT salary * 1.1 AS new_salary
FROM employees
WHERE new_salary > 5000;  -- 直接用

---

8. UPDATE 增强

8.1 RETURNING

UPDATE employees SET salary = salary * 1.1
WHERE dept_id = 10
RETURNING id, name, salary;  -- 直接返回结果集

---

9. JOIN 增强

9.1 USING *

SELECT *
FROM employees e JOIN departments d USING (dept_id);
-- 自动去重 dept_id

---

10. Annotations

-- 表注释
CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(100)
) ANNOTATIONS (
  display '员工表',
  description '员工基本信息'
);

-- 列注释
CREATE TABLE employees (
  id NUMBER ANNOTATIONS (display 'ID'),
  salary NUMBER ANNOTATIONS (display '薪水', unit '元')
);

---

11. 多值 INSERT

-- 23ai
INSERT INTO t (id, name) VALUES 
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

---

12. DOMAIN

12.1 创建

CREATE DOMAIN salary_domain AS NUMBER(10, 2)
  CONSTRAINT sal_check CHECK (salary_domain > 0 AND salary_domain < 1000000)
  DISPLAY '薪水'
  ANNOTATIONS (unit '元');

12.2 使用

CREATE TABLE employees (
  id NUMBER,
  salary salary_domain
);

---

13. JavaScript in DB

-- 23ai 支持 JavaScript
CREATE OR REPLACE MLE MODULE js_module 
LANGUAGE JAVASCRIPT AS
export function add(a, b) {
  return a + b;
}
/

-- 调用
SELECT mle.eval('js_module', 'add', 1, 2) FROM dual;

---

14. Graph 查询

-- Property Graph
CREATE PROPERTY GRAPH hr_graph
  VERTEX TABLES (
    employees AS employee KEY (id) LABEL employee PROPERTIES (name, salary),
    departments AS dept KEY (id) LABEL dept PROPERTIES (name)
  )
  EDGE TABLES (
    employees AS works_in KEY (id) SOURCE KEY (dept_id) DESTINATION departments KEY (id) LABEL works_in
  );

-- 查询
SELECT * FROM GRAPH_TABLE (hr_graph
  MATCH (e:employee) -[w:works_in]-> (d:dept)
  WHERE e.salary > 5000
  COLUMNS (e.name, d.name)
);

---

15. SQL Firewall

-- 防止 SQL 注入
BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE('scott', 'app_user');
  -- 学习正常 SQL
  
  DBMS_SQL_FIREWALL.ENABLE_ALLOWLIST('scott', 'app_user');
  -- 仅允许学到的 SQL
END;
/

---

16. Data Link

-- 简化 DB Link
CREATE DATA LINK remote_data AS 'remote_db';
SELECT * FROM employees@remote_data;

---

17. Aggregation 增强

17.1 LISTAGG

-- 23ai
SELECT LISTAGG(name, ',' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY name) ...
SELECT LISTAGG(name, ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) WITHIN GROUP (...) ...

---

18. 应用场景

18.1 AI Vector Search

-- 语义搜索
SELECT id, content, VECTOR_DISTANCE(embedding, :query, COSINE) AS score
FROM docs
ORDER BY score
FETCH FIRST 10 ROWS ONLY;

18.2 现代开发

-- JSON + 关系
CREATE JSON DUALITY VIEW ...;

-- BOOLEAN
CREATE TABLE t (is_active BOOLEAN);

-- JavaScript
CREATE MLE MODULE ...;

18.3 简化 SQL

-- IF EXISTS
DROP TABLE IF EXISTS t;

-- 别名
SELECT ... AS x FROM t WHERE x > 100;

---

19. 性能

19.1 VECTOR

- 向量索引
- 相似查询
- AI

19.2 Duality

- 关系 + JSON
- 自动同步
- 性能

19.3 JavaScript

- MLE
- 性能
- 集成

---

20. 常见坑与排错

20.1 兼容

- 23ai 特有
- 版本检查

20.2 VECTOR

- 维度
- 模型
- 索引

20.3 JavaScript

- MLE 配置
- 内存

---

21. 最佳实践

1. BOOLEAN：现代
2. VECTOR：AI
3. Duality：JSON
4. DOMAIN：约束
5. IF EXISTS：简化
6. 别名：清晰
7. Annotations：元数据
8. Graph：关系
9. SQL Firewall：安全
10. 测试：兼容

---

22. 参考资料

[1] Oracle Database 23ai New Features Guide https://docs.oracle.com/en/database/oracle/oracle-database/23/nf/