学习Oracle数据库那些实用又容易忽略的代码小技巧分享

今天咱们不聊那些Oracle数据库的大道理，比如什么体系架构、备份恢复策略，那些交给DBA去头疼，咱们就说说在实际写代码、查数据时，那些能让你事半功倍，但又特别容易被忽略的小技巧，这些东西可能不会写在教科书的重点章节里，但却是老手们日常都在用的“利器”。

用 WITH 子句（公共表表达式）让复杂查询变清爽

你是不是经常遇到那种需要嵌套好几层的SELECT语句？写起来头晕，过几天自己都看不懂，这时候，一定要试试 WITH 子句，你可以把它理解成先定义一个临时的视图,然后在主查询里反复使用它。

你想先找出每个部门工资最高的员工，然后再从这些“部门最高薪员工”里找出整个公司工资最低的那位，用传统写法可能要嵌套两次，但用 WITH 就清楚多了：

（来源：Oracle官方SQL文档 - SELECT语句）

WITH dept_max_sal AS (
    SELECT deptno, MAX(sal) as max_sal
    FROM emp
    GROUP BY deptno
)
SELECT e.ename, e.sal, e.deptno
FROM emp e
JOIN dept_max_sal d ON e.deptno = d.deptno AND e.sal = d.max_sal
WHERE e.sal = (SELECT MIN(max_sal) FROM dept_max_sal);

这样写，逻辑一目了然，第一步干什么，第二步干什么，清清楚楚，而且这个临时表 dept_max_sal 在主查询里可以像普通表一样被多次引用，数据库也只会执行一次,效率很高。

分析函数：排名、累加，一行代码搞定

这是Oracle特别强大的功能，但初学者很容易被吓到，其实它的核心思想就是“在不合并行的前提下，看到与组相关的信息”，最常用的就是 ROW_NUMBER(), RANK(), SUM() OVER()。

• 轻松给数据排名：比如你想给员工按部门分组,按工资排名。

  SELECT ename, deptno, sal,
         ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) as dept_rank
  FROM emp;

  一句 OVER(PARTITION BY deptno ORDER BY sal DESC) 就搞定了，意思是“按部门分区，在每个部门内部按工资降序排列并编号”，比用自连接（Self Join）子查询要简单和高效得多。

• 计算累计求和：你想看公司每个月工资的累计发放总额。

  SELECT TO_CHAR(hiredate, 'YYYY-MM') as hire_month, sal,
         SUM(sal) OVER(ORDER BY TO_CHAR(hiredate, 'YYYY-MM')) as running_total
  FROM emp;

  SUM(sal) OVER(ORDER BY ...) 就能实现逐月累加的效果,非常直观。

（来源：Oracle官方SQL文档 - 分析函数）

LISTAGG 函数：把多行数据“捏”成一行

你有没有遇到过这种需求：把一个分组下的所有值，用逗号拼接成一个字符串？列出每个部门所有员工的名字。

以前可能要用很麻烦的 SYS_CONNECT_BY_PATH 函数或者写程序循环，现在简单了，用 LISTAGG。

SELECT deptno, LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) as employees
FROM emp
GROUP BY deptno;

执行结果可能就是：

DEPTNO  EMPLOYEES
10      CLARK, KING, MILLER
20      ADAMS, FORD, JONES, SCOTT, SMITH
30      ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

这个函数在生成报表时特别有用,比如生成逗号分隔的ID列表等。

（来源：Oracle官方SQL文档 - LISTAGG函数）

误操作后的“后悔药”：Flashback Query（闪回查询）

你肯定有过手滑的时候：不小心执行了一个DELETE或者UPDATE，把重要数据改错了，还提交了（commit）！瞬间冷汗就下来了，别急，如果你的DBA开启了相关功能，Oracle给你准备了“后悔药”,就是闪回查询。

你可以像坐时光机一样,查询几分钟前表的数据是什么样子：

-- 查询5分钟前的员工表数据
SELECT * FROM emp AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '5' MINUTE;
-- 如果你知道大概的SCN号（系统改变号），也可以基于SCN查询，更精确
-- SELECT * FROM emp AS OF SCN 12345678;

查到了旧数据之后，你就可以快速写一个INSERT ... SELECT语句把数据恢复回来，这招在开发测试环境里简直是救命稻草，这个功能能回溯的时间有限,取决于undo表空间的设置。

（来源：Oracle官方概念指南 - 闪回技术）

用 CASE WHEN 实现动态统计

我们经常要做一些统计报表，比如按不同工资范围统计人数，笨办法是写好几个SQL，分别用 WHERE sal BETWEEN ...，聪明办法是用 CASE WHEN 在一条SQL里搞定。

SELECT
    COUNT(*) as total,
    COUNT(CASE WHEN sal < 2000 THEN 1 END) as "低薪(<2000)",
    COUNT(CASE WHEN sal BETWEEN 2000 AND 3000 THEN 1 END) as "中薪(2000-3000)",
    COUNT(CASE WHEN sal > 3000 THEN 1 END) as "高薪(>3000)"
FROM emp;

这里的技巧是，CASE WHEN 会为符合条件的行返回1，不符合的返回NULL，而 COUNT 函数只计算非NULL值，这样就能在一个结果行里看到所有维度的统计,效率极高。

序列（Sequence）的 nextval 和 currval 的妙用

序列大家都知道，用来生成唯一主键ID，但很多人会忽略 CURRVAL（当前值）的用法，在一个会话中，你刚用 序列名.NEXTVAL 拿下一个新值后，可以立刻用 序列名.CURRVAL 来引用这个刚生成的值。

这在一次插入主从表（父子表）时特别有用：

-- 假设有订单表（orders）和订单明细表（order_details）
INSERT INTO orders (order_id, order_date) VALUES (order_seq.NEXTVAL, SYSDATE);
-- 紧接着插入明细，不需要再去查询刚才插入的order_id是什么
INSERT INTO order_details (detail_id, order_id, product_id)
VALUES (detail_seq.NEXTVAL, order_seq.CURRVAL, 1001);

注意：CURRVAL 必须在同一次会话中，在 NEXTVAL 之后 执行才有效。

（来源：Oracle官方SQL参考 - CREATE SEQUENCE）

这些小技巧就像是你工具箱里的小扳手、小螺丝刀，平时可能不起眼，但在解决特定问题时，能让你显得非常“专业”,希望这些实实在在的代码对你有帮助。