写了几年 Oracle SQL，你可能觉得自己已经很熟了。SELECT、JOIN、GROUP BY 信手拈来，存储过程、触发器也能写。但 Oracle 有一些"坑"，平时不显山露水，一旦踩上去就是 性能断崖式下跌 或者 数据莫名其妙不对。
这些坑有一个共同特点：SQL 能正常执行，不报错，但结果或性能跟你想的完全不一样。
本文盘点 Oracle 开发中最常见的 SQL 陷阱，每一条都来自真实的生产事故。建议收藏，写 SQL 前对照检查。
陷阱一：NULL 值的"隐身术"
问题
NULL 在 Oracle 中代表"未知"，而不是"空值"。这个设计导致很多直觉判断是错的：
-- 你觉得会返回什么？
SELECT * FROM emp WHERE comm = NULL;
-- 结果：一条都查不到。因为 NULL = NULL 的结果是 NULL（未知），不是 TRUE。
正确写法：
SELECT * FROM emp WHERE comm IS NULL;
更隐蔽的坑：NOT IN + NULL
-- 子查询返回的结果中如果包含哪怕一个 NULL
SELECT * FROM dept
WHERE dept_id NOT IN (SELECT dept_id FROM emp);
-- 如果 emp.dept_id 中有 NULL，整个查询返回 0 行！
原因：NOT IN 等价于 != ALL，而 x != NULL 的结果是 NULL，不是 TRUE。只要子查询中有一个 NULL，所有行的判断结果都是 NULL，最终没有一行满足条件。
正确写法：
-- 方案 1：确保子查询排除 NULL
SELECT * FROM dept
WHERE dept_id NOT IN (SELECT dept_id FROM emp WHERE dept_id IS NOT NULL);
-- 方案 2：改用 NOT EXISTS（推荐）
SELECT * FROM dept d
WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id);
经验法则：
NOT IN子查询中一定要确认关联列不可能为 NULL，否则一律改用NOT EXISTS。
陷阱二：隐式类型转换，索引直接失效
问题
-- emp_id 是 VARCHAR2 类型，上面有索引
SELECT * FROM emp WHERE emp_id = 12345;
你以为走了索引？没有。 Oracle 会做隐式类型转换，等价于：
SELECT * FROM emp WHERE TO_NUMBER(emp_id) = 12345;
emp_id 被函数包裹了，索引 直接失效，变成全表扫描。
怎么发现
用 EXPLAIN PLAN 看一下执行计划。如果看到 TABLE ACCESS FULL，而你认为应该走索引，检查 WHERE 条件中是否有类型不匹配。
正确写法
SELECT * FROM emp WHERE emp_id = '12345'; -- 加引号，类型匹配
类似的坑
| 列类型 | 错误写法 | 正确写法 |
|---|---|---|
| DATE | WHERE create_time = '2024-01-01' | WHERE create_time = DATE '2024-01-01' |
| NUMBER | WHERE amount = '100' | WHERE amount = 100 |
| VARCHAR2 | WHERE name = N'张三'（NVARCHAR 字面量） | WHERE name = '张三' |
陷阱三：对索引列做运算，索引又废了
问题
-- 对索引列做函数运算
SELECT * FROM emp WHERE SUBSTR(emp_name, 1, 1) = '张';
SELECT * FROM orders WHERE TRUNC(create_time) = DATE '2024-01-01';
这两种写法都会导致 索引失效，因为 Oracle 无法在索引树中直接定位经过函数变换后的值。
解决方案
方案 1：改写 SQL，避免对列做运算
-- 用 LIKE 代替 SUBSTR
SELECT * FROM emp WHERE emp_name LIKE '张%';
-- 用范围查询代替 TRUNC
SELECT * FROM orders
WHERE create_time >= DATE '2024-01-01'
AND create_time < DATE '2024-01-02';
方案 2：建函数索引
CREATE INDEX idx_orders_ctime ON orders(TRUNC(create_time));
注意：函数索引只在查询条件中 精确使用相同函数 时才会被使用。
陷阱四：OR 条件让索引"罢工"
问题
SELECT * FROM emp
WHERE dept_id = 10 OR salary > 10000;
如果 dept_id 和 salary 上分别有索引，Oracle 很可能 两个都不用，直接全表扫描。
原因：Oracle 的优化器在处理 OR 条件时，通常需要合并两个索引的扫描结果，成本可能比全表扫描还高。除非两个列的组合选择性极高，否则优化器会选择全表扫描。
解决方案
方案 1：改写为 UNION ALL
SELECT * FROM emp WHERE dept_id = 10
UNION ALL
SELECT * FROM emp WHERE salary > 10000 AND dept_id != 10;
方案 2：建复合索引
CREATE INDEX idx_emp_dept_sal ON emp(dept_id, salary);
方案 3：使用 BITMAP OR（适用于数据仓库场景，OLTP 慎用）
陷阱五：LIKE 前导通配符，索引失效
问题
SELECT * FROM emp WHERE emp_name LIKE '%张%';
% 在前面，索引 完全无法使用。因为 B-Tree 索引是有序的，只能做前缀匹配。
解决方案
- 如果业务允许，改为后缀匹配：
LIKE '张%'（可以用索引）； - 如果需要全文搜索，考虑 Oracle Text 索引；
- 或者引入 Elasticsearch 等专业搜索引擎。
陷阱六：ROWNUM 分页的隐藏陷阱
问题
很多人写分页是这样的：
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM emp ORDER BY salary DESC
) a WHERE ROWNUM <= 20
) WHERE rn > 10;
这本身没错。但下面这个写法就 大错特错 了：
-- 想查第 11-20 条记录
SELECT * FROM emp WHERE ROWNUM > 10 ORDER BY salary DESC;
-- 结果：一条都查不到！
原因：ROWNUM 是在 结果集生成过程中 逐行分配的。第一行分配 ROWNUM = 1，因为 1 > 10 不成立，被过滤掉；第二行本来应该分配 ROWNUM = 2，但因为第一行被过滤了，它变成了新的第一行，分配 ROWNUM = 1，还是不满足 > 10……以此类推，永远没有行能通过 ROWNUM > 10 的过滤。
正确写法
SELECT * FROM (
SELECT t.*, ROWNUM rn FROM (
SELECT * FROM emp ORDER BY salary DESC
) t
) WHERE rn BETWEEN 11 AND 20;
Oracle 12c 之后可以用更简洁的
OFFSET ... FETCH语法，但底层原理类似。
陷阱七：UPDATE 关联更新的"伪更新"
问题
想根据另一张表更新当前表：
-- 错误写法（MySQL 风格，Oracle 不支持这种 UPDATE JOIN 语法）
UPDATE emp e
SET e.salary = (SELECT d.avg_salary FROM dept d WHERE d.dept_id = e.dept_id);
如果子查询没有匹配到任何行，返回 NULL，那么 emp.salary 会被更新为 NULL，而不是保持不变。
正确写法
UPDATE emp e
SET e.salary = (
SELECT d.avg_salary
FROM dept d
WHERE d.dept_id = e.dept_id
)
WHERE EXISTS (
SELECT 1
FROM dept d
WHERE d.dept_id = e.dept_id
);
陷阱八：DELETE 没有 WHERE 条件（手滑事故）
这不是 Oracle 特有的坑，但在 Oracle 中后果特别严重——Oracle 默认不自动提交。
DELETE FROM emp; -- 忘了写 WHERE
-- 此时数据还在回滚段中，可以 ROLLBACK 挽救
COMMIT; -- 如果手滑再敲一个 COMMIT，数据就真没了
防御措施
- 写 DELETE 时先写 SELECT：先
SELECT COUNT(*)确认要删多少行； - 使用事务：DELETE 后先不 COMMIT，确认
SELECT检查无误后再提交； - 开启闪回（Flashback） ：
FLASHBACK TABLE emp TO TIMESTAMP ...可以恢复误删的数据（前提是开启了行移动）。
陷阱九：NVL 和 COALESCE 的隐式转换
问题
SELECT NVL(date_col, ' ') FROM t;
date_col 是 DATE 类型，' ' 是字符串。Oracle 会尝试将 ' ' 转换为 DATE，转换失败直接报错。
另一个坑：NVL 的参数类型必须兼容
SELECT NVL(salary, 0) FROM emp; -- OK，都是 NUMBER
SELECT NVL(emp_name, 0) FROM emp; -- 报错，VARCHAR2 和 NUMBER 不兼容
COALESCE vs NVL
-- NVL：总是先计算第二个参数
SELECT NVL(col1, func_expensive()) FROM t; -- func_expensive() 总是被执行
-- COALESCE：短路求值
SELECT COALESCE(col1, func_expensive()) FROM t; -- col1 非 NULL 时，func_expensive() 不执行
建议：能用 COALESCE 就用 COALESCE，性能和灵活性都更好。
陷阱十：外连接（+）的隐藏限制
问题
Oracle 的外连接语法 (+) 有一些鲜为人知的限制：
-- 错误：不能在 WHERE 子句中对同一表既用 (+) 又不用
SELECT * FROM a, b
WHERE a.id = b.id(+)
AND b.status = 'A'; -- b.status 没有加 (+)，导致外连接退化为内连接
原因：b.status = 'A' 这个条件在没有 (+) 的情况下，会将 b 中不匹配的行过滤掉（因为 NULL 不满足 = 'A'），外连接的效果被抵消了。
正确写法
SELECT * FROM a, b
WHERE a.id = b.id(+)
AND b.status(+) = 'A';
或者改用 ANSI SQL 的 LEFT JOIN 语法（推荐）：
SELECT * FROM a
LEFT JOIN b ON a.id = b.id AND b.status = 'A';
强烈建议：新代码一律使用
LEFT JOIN / RIGHT JOIN语法，(+)语法是 Oracle 遗留特性，可读性差且限制多。
陷阱十一：GROUP BY 的"便捷"坑
问题
-- Oracle 12c 之前，这是非法的
SELECT dept_id, emp_name, COUNT(*)
FROM emp
GROUP BY dept_id;
-- ORA-00979: 不是 GROUP BY 表达式
但如果你用了某些 BI 工具或 ORM 框架，它们可能自动生成包含非聚合列的 GROUP BY 查询，导致报错。
Oracle 12c+ 的 GROUP BY ... GROUPING SETS 和 ROLLUP
-- 这个写法在 Oracle 中合法，但结果可能不是你想要的
SELECT dept_id, emp_name, COUNT(*)
FROM emp
GROUP BY ROLLUP(dept_id, emp_name);
ROLLUP 会产生小计行，其中 dept_id 或 emp_name 可能为 NULL。如果你的应用代码没有处理这些小计行，统计结果会多出意料之外的汇总数据。
陷阱十二：SELECT FOR UPDATE 的锁等待
问题
-- 会话 A
SELECT * FROM emp WHERE emp_id = 100 FOR UPDATE;
-- 会话 B（另一个用户）
SELECT * FROM emp WHERE emp_id = 100 FOR UPDATE; -- 阻塞，等待会话 A 释放锁
如果会话 A 忘记 COMMIT 或 ROLLBACK，会话 B 会 无限期阻塞。
解决方案
-- 加 NOWAIT 或 WAIT 子句
SELECT * FROM emp WHERE emp_id = 100 FOR UPDATE NOWAIT; -- 立即报错，不等待
SELECT * FROM emp WHERE emp_id = 100 FOR UPDATE WAIT 5; -- 最多等 5 秒
总结：避坑自查清单
| 检查项 | 说明 |
|---|---|
| NULL 判断 | 用 IS NULL，不用 = NULL |
| NOT IN | 子查询列可能为 NULL 时改用 NOT EXISTS |
| 类型匹配 | WHERE 条件中的字面量类型与列类型一致 |
| 索引列运算 | 不对索引列做函数/运算，或建函数索引 |
| OR 条件 | 关注执行计划，必要时改写为 UNION ALL |
| LIKE | 前导通配符 %xxx 无法走索引 |
| ROWNUM | 分页必须嵌套子查询 |
| UPDATE 关联 | 用 EXISTS 防止误更新为 NULL |
| 外连接 | 新代码用 LEFT JOIN，不用 (+) |
| FOR UPDATE | 加 NOWAIT 或 WAIT，防止无限阻塞 |
Oracle SQL 的坑大多不是语法错误，而是 语义上的"似是而非" ——SQL 能跑，结果不对，或者性能突然崩了。这些坑靠编译器发现不了，只能靠经验和谨慎来规避。
写 SQL 时多问自己一句： "这个写法，Oracle 的优化器会怎么理解？" 养成这个习惯，能避开 80% 的雷。