Oracle 数据库新特性与常用 SQL 示例：从查询分析到工程实践

Oracle 数据库的能力很“全栈”：从 OLTP 到数仓分析、从权限审计到高可用与分区分片，都有成熟方案。本文以 Oracle 19c/21c/23c（含 23ai） 为主线，先梳理你最可能用得上的“新变化/新能力”，再给一份常用 SQL 示例清单，便于迁移与日常速查。

---

TL;DR（先给结论）

• 写报表分析：优先用 分析函数（Analytic Functions），例如 ROW_NUMBER()、LAG/LEAD、SUM(...) OVER(...)。
• 写层级数据：优先用 CONNECT BY（经典语法）或递归 CTE（WITH ...）。
• 写分页：用 OFFSET ... FETCH NEXT ...（12c+），避免 ROWNUM 的老写法。
• 处理 JSON：优先用 JSON_TABLE 把 JSON 摊平成关系表再查（更好维护）。
• 做“安全的索引优化”：Oracle 里常用 不可见索引（Invisible Index） 做灰度验证。

---

1) Oracle “新特性”怎么看：版本与取舍

Oracle 的很多能力（分析函数、分区、物化视图、并行等）在更早版本就很成熟。近些年的变化更多集中在：

• 云化/多租户（Multitenant）：容器数据库 CDB / 可插拔数据库 PDB 的体系更普遍。
• 自动化与自治：更强的自动统计、索引建议/自动化能力（不同版本与产品形态差异较大）。
• JSON 与多模型数据：JSON 处理能力增强，逐步走向“关系 + 文档”的统一使用体验。
• 开发体验：更现代的 SQL 语法糖与可维护性增强（例如更强的 JSON、宏/抽象等方向）。

如果你在选型/升级，一条实用建议是：

• 长期稳定优先：以 19c 作为“稳态主力”的参考点。
• 想用更现代的 JSON/开发特性：关注 21c/23c（以及云/自治形态的特性差异）。

---

2) 常见开发能力升级（你最容易用上）

2.1 分页：OFFSET/FETCH（12c+，比 ROWNUM 直观）

SELECT *
FROM orders
WHERE user_id = :user_id
ORDER BY created_at DESC
OFFSET :offset ROWS FETCH NEXT :page_size ROWS ONLY;

---

2.2 MERGE：经典 UPSERT（存在则更新，不存在则插入）

MERGE INTO user_profile t
USING (SELECT :user_id AS user_id, :nickname AS nickname FROM dual) s
ON (t.user_id = s.user_id)
WHEN MATCHED THEN
  UPDATE SET t.nickname = s.nickname, t.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (user_id, nickname, created_at, updated_at)
  VALUES (s.user_id, s.nickname, SYSTIMESTAMP, SYSTIMESTAMP);

---

2.3 不可见索引（Invisible Index）：安全做索引灰度

适合“想验证删索引/改索引会不会影响查询计划”：

ALTER INDEX idx_orders_user_id INVISIBLE;
ALTER INDEX idx_orders_user_id VISIBLE;

---

3) 查询分析能力（Oracle 的强项）：分析函数

3.1 每组 Top N：每个用户最近 3 笔支付

WITH ranked AS (
  SELECT
    p.*,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY paid_at DESC) AS rn
  FROM payments p
)
SELECT *
FROM ranked
WHERE rn <= 3
ORDER BY user_id, rn;

3.2 环比/同比：用 LAG/LEAD 做对比

WITH m AS (
  SELECT
    TRUNC(paid_at, 'MM') AS month_start,
    SUM(amount) AS total
  FROM orders
  WHERE status = 'PAID'
  GROUP BY TRUNC(paid_at, 'MM')
)
SELECT
  month_start,
  total,
  LAG(total, 1) OVER (ORDER BY month_start) AS prev_month_total,
  total - LAG(total, 1) OVER (ORDER BY month_start) AS mom_delta
FROM m
ORDER BY month_start;

3.3 累计值（running total）

SELECT
  dt,
  amount,
  SUM(amount) OVER (ORDER BY dt) AS running_total
FROM daily_sales
ORDER BY dt;

---

4) 层级数据：CONNECT BY 与递归 CTE

4.1 CONNECT BY（经典写法，查组织树/分类树）

SELECT
  id,
  parent_id,
  name,
  LEVEL AS depth
FROM categories
START WITH id = :root_id
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY name;

4.2 递归 CTE（WITH …，结构更像标准 SQL）

WITH category_tree (id, parent_id, name, depth) AS (
  SELECT id, parent_id, name, 0
  FROM categories
  WHERE id = :root_id

  UNION ALL

  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN category_tree t ON c.parent_id = t.id
)
SELECT *
FROM category_tree
ORDER BY depth, id;

---

5) JSON：把半结构化数据“关系化”查询

Oracle 的 JSON 生态比较成熟，推荐的思路是：能结构化就结构化；需要保留原始 JSON 时，用 JSON_TABLE 把查询写成“像查表一样”。

假设 orders.items_json 是 JSON 数组：

SELECT
  o.id AS order_id,
  jt.sku,
  jt.qty,
  jt.price,
  jt.qty * jt.price AS line_total
FROM orders o
CROSS JOIN JSON_TABLE(
  o.items_json,
  '$[*]' COLUMNS (
    sku   VARCHAR2(32) PATH '$.sku',
    qty   NUMBER       PATH '$.qty',
    price NUMBER       PATH '$.price'
  )
) jt
ORDER BY o.id;

---

6) 性能排查与计划查看：EXPLAIN PLAN / DBMS_XPLAN

Oracle 常用的计划查看流程：

EXPLAIN PLAN FOR
SELECT *
FROM orders
WHERE user_id = :user_id
ORDER BY paid_at DESC
FETCH FIRST 20 ROWS ONLY;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

一些实用建议：

• 优先让 SQL “可索引”：WHERE 条件、连接条件、排序字段尽量有合适的索引支撑。
• 少在谓词里包函数（例如 WHERE LOWER(email) = ...），必要时用函数索引或生成列替代（实现方式与版本/许可相关，按你的环境选）。

---

7) 权限与可维护性：常用账号/权限操作

创建用户并授权（示例）：

CREATE USER app_user IDENTIFIED BY "StrongPassword";
GRANT CREATE SESSION TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON your_schema.orders TO app_user;

查看权限：

SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

---

8) 常用 SQL 速查（更贴近日常）

8.1 时间与日期

SELECT SYSDATE, SYSTIMESTAMP FROM dual;
SELECT TRUNC(SYSDATE, 'DD') AS day_start FROM dual;
SELECT ADD_MONTHS(SYSDATE, -1) AS one_month_ago FROM dual;

8.2 去重与计数

SELECT COUNT(*) FROM t;
SELECT COUNT(DISTINCT user_id) FROM orders;

8.3 字符串处理

SELECT LOWER(email), UPPER(email) FROM users;
SELECT SUBSTR(name, 1, 10) FROM users;

8.4 正则

SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS digits_only
FROM contacts;

---

9) 升级/迁移小贴士（从 MySQL/PG 迁到 Oracle 常见差异）

• 占位符与绑定变量：Oracle 强烈建议使用绑定变量（:id），既安全又利于复用执行计划。
• 自增：可以用 sequence + trigger（传统）或 identity（更现代的选择，按你的版本/建表规范）。
• 分页：建议统一用 OFFSET/FETCH（更直观）。
• NULL 处理：Oracle 常用 NVL(expr, default)；也支持 COALESCE（更标准）。

---

参考与延伸阅读

• Oracle Database SQL Language Reference（官方文档）
• Oracle JSON Developer’s Guide（官方文档）
• DBMS_XPLAN（官方文档）