Oracle 12c 新 SQL 特性详解

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

---

1. 概述

Oracle 12c 引入大量新 SQL 特性[1]：

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

---

2. FETCH 分页

2.1 基本

SELECT * FROM employees ORDER BY id
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

2.2 百分比

SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

2.3 WITH TIES

SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 10 ROWS WITH TIES;

详细见：Oracle SQL 查询优化技巧。

---

3. IDENTITY

3.1 基本

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

-- BY DEFAULT
CREATE TABLE employees (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

-- ON NULL
CREATE TABLE employees (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

3.2 选项

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY 
    (START WITH 1 INCREMENT BY 1 MAXVALUE 999999 CACHE 20)
    PRIMARY KEY
);

详细见：Oracle 序列与自增列详解。

---

4. 临时表

4.1 Private Temporary（18c+）

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp (
  id NUMBER,
  name VARCHAR2(100)
) ON COMMIT DROP DEFINITION;
-- 或 ON COMMIT PRESERVE DEFINITION

-- 会话/事务级
-- 自动清理

4.2 GTT 不变

CREATE GLOBAL TEMPORARY TABLE gtt_emp (...) 
ON COMMIT DELETE ROWS;

---

5. Invisible Columns

CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(100),
  salary NUMBER INVISIBLE
);

-- 默认不可见
DESC employees;  -- 不显示 salary

-- 显式查询
SELECT id, name, salary FROM employees;

-- 修改
ALTER TABLE employees MODIFY salary VISIBLE;
ALTER TABLE employees MODIFY salary INVISIBLE;

---

6. Default 列

6.1 DEFAULT

CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(100),
  status VARCHAR2(20) DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

6.2 序列

CREATE TABLE employees (
  id NUMBER DEFAULT seq_emp.NEXTVAL PRIMARY KEY,
  name VARCHAR2(100)
);

6.3 NULL

CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(100) DEFAULT ON NULL 'Unknown'
);
-- INSERT NULL 时使用默认值

---

7. MATCH_RECOGNIZE

SELECT *
FROM stock_prices
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY price_date
  MEASURES 
    FINAL FIRST(up.price_date) AS start_date,
    FINAL LAST(down.price_date) AS end_date
  ONE ROW PER MATCH
  PATTERN (up+ down+)
  DEFINE 
    up AS up.price > PREV(up.price),
    down AS down.price < PREV(down.price)
);

详细见：Oracle SQL 模式匹配。

---

8. LISTAGG 增强

8.1 DISTINCT

SELECT dept_id, LISTAGG(DISTINCT name, ',') WITHIN GROUP (ORDER BY name)
FROM employees
GROUP BY dept_id;

8.2 ON OVERFLOW

SELECT LISTAGG(name, ',' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY name)
FROM employees;

---

9. JSON 支持

9.1 查询

SELECT JSON_VALUE(doc, '$.name'),
       JSON_QUERY(doc, '$.skills')
FROM documents
WHERE JSON_EXISTS(doc, '$.salary > 5000');

9.2 JSON_TABLE

SELECT t.name, t.skill
FROM documents d,
  JSON_TABLE(doc, '$'
    COLUMNS (
      name VARCHAR2(100) PATH '$.name',
      NESTED PATH '$.skills[*]'
        COLUMNS (skill VARCHAR2(50) PATH '$')
    )
  ) t;

详细见：Oracle JSON 处理详解。

---

10. PL/SQL 增强

10.1 ACCESSIBLE BY

CREATE OR REPLACE PACKAGE emp_pkg
  ACCESSIBLE BY (PROCEDURE hr_proc)
AS
  ...
END;
/

10.2 UTL_CALL_STACK

DECLARE
  v_depth NUMBER;
BEGIN
  v_depth := UTL_CALL_STACK.DYNAMIC_DEPTH;
  FOR i IN 1..v_depth LOOP
    DBMS_OUTPUT.PUT_LINE(
      UTL_CALL_STACK.CONCATENATED_OWNER_NAME(i) || '.' ||
      UTL_CALL_STACK.CONCATENATED_NAME(i)
    );
  END LOOP;
END;
/

10.3 DBMS_HPROF

EXEC DBMS_HPROF.START_PROFILING('HPROF_DIR', 'test.txt');
-- 执行
EXEC DBMS_HPROF.STOP_PROFILING;

---

11. 数据类型

11.1 VARCHAR2 32767

ALTER SYSTEM SET max_string_size = EXTENDED SCOPE = SPFILE;
-- 重启
@?/rdbms/admin/utl32k.sql

-- 32K 字符串
CREATE TABLE t (long_text VARCHAR2(32767));

11.2 PL/SQL

-- PL/SQL 32K
DECLARE
  v_text VARCHAR2(32767);
BEGIN
  ...
END;

---

12. 临时表 Undo

CREATE TEMPORARY TABLE temp_sales (...) 
  ON COMMIT DELETE ROWS;
-- 12c+ 临时表 Undo 优化

---

13. 在线操作

13.1 MOVE ONLINE

ALTER TABLE employees MOVE ONLINE;
ALTER TABLE employees MOVE PARTITION p2024 ONLINE;

13.2 SPLIT ONLINE

ALTER TABLE sales SPLIT PARTITION pmax AT (...) ONLINE;

13.3 UPDATE INDEXES

ALTER TABLE sales MOVE PARTITION p2024 ONLINE UPDATE INDEXES;

---

14. Partial Index

CREATE TABLE sales (
  id NUMBER,
  sale_date DATE,
  status VARCHAR2(10),
  INDEXING OFF
)
PARTITION BY RANGE (sale_date) (
  PARTITION p2024 ... INDEXING OFF,
  PARTITION p2025 ... INDEXING ON
);

CREATE INDEX idx_sales_status ON sales(status) LOCAL INDEXING PARTIAL;

---

15. 多列外键 NULL

-- 12c+ 任一列 NULL 视为 NULL
ALTER TABLE child ADD CONSTRAINT fk_comp
  FOREIGN KEY (a, b) REFERENCES parent(a, b) NULL;  -- 默认
ALTER TABLE child ADD CONSTRAINT fk_comp
  FOREIGN KEY (a, b) REFERENCES parent(a, b) NOT NULL;

---

16. 字段默认序列

-- 序列作为默认值
CREATE TABLE employees (
  id NUMBER DEFAULT seq_emp.NEXTVAL PRIMARY KEY
);

---

17. Top N

-- FETCH
SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

---

18. 应用场景

18.1 分页

-- Web 分页
SELECT * FROM products 
ORDER BY id
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY;

18.2 自增 ID

-- 现代主键
CREATE TABLE t (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ...
);

18.3 模式匹配

-- 金融分析
SELECT * FROM stock 
MATCH_RECOGNIZE (...);

18.4 JSON

-- 半结构化
CREATE TABLE docs (doc JSON);

---

19. 性能

19.1 FETCH

- 比 ROWNUM 快
- 键集更快

19.2 IDENTITY

- 序列底层
- CACHE

19.3 临时表

- 私有临时表
- 内存
- 自动清理

---

20. 常见坑与排错

20.1 FETCH 性能

- 大 OFFSET 慢
- 键集替代

20.2 IDENTITY

- ALWAYS 不可指定
- BY DEFAULT 可指定

20.3 Invisible

- DESC 不显示
- 显式查询

---

21. 最佳实践

1. FETCH：分页
2. IDENTITY：主键
3. Private Temp：临时
4. Invisible：敏感
5. MATCH_RECOGNIZE：模式
6. JSON：半结构
7. 32K：长字符串
8. 在线操作：业务
9. Partial Index：选择性
10. 测试：兼容

---

22. 参考资料

[1] Oracle Database New Features Guide 12c https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/