Oracle 高级 SQL 查询技巧

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

1. 概述

Oracle 高级 SQL 查询技巧[1]：

内容：

子查询
EXISTS/IN
WITH 子句
MODEL 子句
闪回查询

2. 子查询

2.1 标量子查询

SELECT 
  e.name,
  (SELECT d.dept_name FROM departments d WHERE d.id = e.dept_id) AS dept_name
FROM employees e;

2.2 相关子查询

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id
);

2.3 多列子查询

SELECT * FROM employees
WHERE (dept_id, salary) IN (
  SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id
);

详细见：Oracle 子查询与 EXISTS。

3. EXISTS vs IN

3.1 EXISTS

-- 适合大表
SELECT * FROM big_table b
WHERE EXISTS (SELECT 1 FROM small_table s WHERE s.id = b.id);

3.2 IN

-- 适合小表
SELECT * FROM big_table b
WHERE b.id IN (SELECT id FROM small_table);

3.3 NOT EXISTS vs NOT IN

-- NOT EXISTS 推荐
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);

-- NOT IN 注意 NULL
SELECT * FROM a WHERE a.id NOT IN (SELECT id FROM b WHERE id IS NOT NULL);

4. WITH 子句

4.1 简单

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e, dept_avg d
WHERE e.dept_id = d.dept_id AND e.salary > d.avg_sal;

4.2 多个

WITH 
dept_total AS (
  SELECT dept_id, SUM(salary) AS total
  FROM employees GROUP BY dept_id
),
company_avg AS (
  SELECT AVG(total) AS avg_total FROM dept_total
)
SELECT d.dept_id, d.total
FROM dept_total d, company_avg c
WHERE d.total > c.avg_total;

详细见：Oracle CTE 与递归查询。

5. 递归查询

5.1 CONNECT BY

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

详细见：Oracle 层次查询。

5.2 WITH 递归

WITH emp_hierarchy (employee_id, manager_id, lvl) AS (
  SELECT employee_id, manager_id, 1
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, h.lvl + 1
  FROM employees e, emp_hierarchy h
  WHERE e.manager_id = h.employee_id
)
SELECT * FROM emp_hierarchy;

6. MODEL 子句

6.1 概述

11g+
电子表格式查询
跨行计算

6.2 示例

SELECT dept_id, year, sales
FROM sales_history
MODEL
  PARTITION BY (dept_id)
  DIMENSION BY (year)
  MEASURES (sales)
  RULES (
    sales[2026] = sales[2025] * 1.1
  );

7. PIVOT / UNPIVOT

7.1 PIVOT

SELECT * FROM (
  SELECT dept_id, job_id, salary FROM employees
)
PIVOT (
  SUM(salary) FOR job_id IN ('CLERK' AS clerk, 'MANAGER' AS mgr, 'ANALYST' AS analyst)
);

7.2 UNPIVOT

SELECT * FROM sales_pivot
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
);

详细见：Oracle 行列转换 PIVOT/UNPIVOT。

8. Flashback Query

8.1 AS OF

SELECT * FROM employees 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
WHERE id = 100;

8.2 VERSIONS BETWEEN

SELECT 
  versions_xid, 
  versions_starttime, 
  versions_endtime,
  salary
FROM employees
VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY) AND SYSTIMESTAMP
WHERE id = 100;

详细见：Oracle 闪回技术。

9. FETCH 分页（12c+）

9.1 基本

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

9.2 百分比

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

9.3 WITH TIES

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

10. MERGE

MERGE INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name
  DELETE WHERE s.status = 'INACTIVE'
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name);

详细见：Oracle MERGE 语句。

11. 分析函数

11.1 ROW_NUMBER

SELECT 
  name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

11.2 RANK / DENSE_RANK

SELECT 
  name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

11.3 LAG / LEAD

SELECT 
  sale_date, amount,
  LAG(amount) OVER (ORDER BY sale_date) AS prev_amount,
  LEAD(amount) OVER (ORDER BY sale_date) AS next_amount
FROM sales;

详细见：Oracle 分析函数。

12. 正则表达式

-- REGEXP_LIKE
SELECT * FROM employees WHERE REGEXP_LIKE(name, '^A.*');

-- REGEXP_REPLACE
SELECT REGEXP_REPLACE(phone, '([0-9]{3})-([0-9]{4})', '\1.\2') FROM employees;

-- REGEXP_SUBSTR
SELECT REGEXP_SUBSTR('a,b,c', '[^,]+', 1, 2) FROM dual;

-- REGEXP_INSTR
SELECT REGEXP_INSTR('abc123', '[0-9]') FROM dual;

详细见：Oracle 正则表达式。

13. 集合操作

-- UNION：去重
SELECT id FROM a UNION SELECT id FROM b;

-- UNION ALL：不去重
SELECT id FROM a UNION ALL SELECT id FROM b;

-- INTERSECT：交集
SELECT id FROM a INTERSECT SELECT id FROM b;

-- MINUS：差集
SELECT id FROM a MINUS SELECT id FROM b;

详细见：Oracle SQL 集合操作。

14. JSON 查询

14.1 12c+

-- JSON_VALUE
SELECT JSON_VALUE(data, '$.name') FROM json_table;

-- JSON_QUERY
SELECT JSON_QUERY(data, '$.items') FROM json_table;

-- JSON_TABLE
SELECT jt.name, jt.salary
FROM json_table,
JSON_TABLE(data, '$' COLUMNS (
  name VARCHAR2(100) PATH '$.name',
  salary NUMBER PATH '$.salary'
)) jt;

详细见：Oracle JSON 处理。

15. 性能优化

15.1 索引使用

-- 避免函数
SELECT * FROM employees WHERE id = 100;  -- 索引有效
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';  -- 需函数索引

15.2 JOIN 顺序

-- 小表驱动大表
SELECT /*+ LEADING(s b) USE_NL(b) */ *
FROM small_table s, big_table b
WHERE s.id = b.id;

15.3 绑定变量

-- 减少 hard parse
EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id = :1' USING v_id;

详细见：Oracle SQL 调优最佳实践。

16. 常见坑与排错

16.1 NULL 处理

-- NULL 不参与比较
SELECT * FROM t WHERE col != 'X';  -- 不返回 NULL
SELECT * FROM t WHERE col IS DISTINCT FROM 'X';  -- 包含 NULL

16.2 隐式转换

-- 字符与数字
SELECT * FROM t WHERE id = '100';  -- 隐式转换，索引可能失效
SELECT * FROM t WHERE id = 100;

16.3 OR 优化

-- OR 可能影响
SELECT * FROM t WHERE id = 1 OR id = 2;
-- 改
SELECT * FROM t WHERE id IN (1, 2);

17. 最佳实践

绑定变量：减少解析
索引友好：避免函数
JOIN 顺序：小驱动大
WITH 子句：可读性
分析函数：性能
EXISTS/IN 选择：数据量
避免 SELECT *：精确
分页 FETCH：12c+
MERGE 替代：高效
测试执行计划：验证

18. 参考资料

[1] Oracle Database SQL Language Reference 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/