4.1 SQL语言与PL-SQL

本节摘要：语言能力决定性能上限：分析函数能用一行 SQL 消掉自连接，递归查询能拆掉应用层循环，PL/SQL 的批量绑定能把百万行处理的上下文切换砍到近零。本节沿三个真实改写案例，把 Oracle 方言里最值钱的几个语言特性讲透。

一、SQL 不只是查询语言

把 SQL 当"查数工具"的团队，性能问题往往出在写法而不是硬件。Oracle 的 SQL 方言经过四十多年演进，很多"需要程序循环才能干的事"其实一行 SQL 就能表达——数据库内部做这件事是批量、向量的；放到应用层用循环做，每行都要走一次网络与解析。本节的立场很直接：能用声明式表达的逻辑，不要挪进过程式代码。反过来，PL/SQL 的价值也不在"把逻辑搬进数据库"，而在批量处理与事务内聚——用它写业务逻辑没问题，但要写得像集合操作者，别写得像逐行搬运工。

二、分析函数：自连接的终结者

需求：取每个部门工资最高的前三名。老写法是相关子查询：对每一行，回到全表数一遍"比我高的有几个"——一万行的表要扫一万遍，复杂度直接爆炸。窗口函数把"分组排名"变成一次扫描：


-- 现代写法：一次扫描完成分组排名与过滤
SELECT deptno, empno, sal, rk
  FROM (
    SELECT deptno, empno, sal,
           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rk
      FROM emp)
 WHERE rk <= 3;

-- 常用窗口三件套：排名、累计、偏移
SELECT deptno, sal,
       RANK()       OVER (PARTITION BY deptno ORDER BY sal DESC) AS 排名,
       SUM(sal)     OVER (PARTITION BY deptno ORDER BY hiredate
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累计工资,
       LAG(sal)     OVER (PARTITION BY deptno ORDER BY hiredate) AS 上一位工资
  FROM emp;

LAG/LEAD 取同组前后行的值，一行 SQL 替代应用里的"取下一条对比"循环；ROWS BETWEEN 定义累计窗口，同比环比不用自连接。这类改写的收益通常在三到二十倍，且代码更短——语言能力换性能，是性价比最高的调优。

三、递归查询：层级数据的原生表达

组织树、物料清单、地区层级——层级展开过去靠应用递归或临时表，Oracle 的 CONNECT BY 与标准递归 CTE 都能一行解决：


-- 递归 CTE：从 CEO 展开整棵组织树并给出层级
WITH org (empno, ename, mgr, lvl) AS (
  SELECT empno, ename, mgr, 1 FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, o.lvl + 1
    FROM emp e JOIN org o ON e.mgr = o.empno)
SEARCH DEPTH FIRST BY empno SET order_seq
SELECT LPAD(' ', (lvl-1)*2) || ename AS 树形显示, lvl FROM org ORDER BY order_seq;

SEARCH DEPTH FIRST 控制展开顺序，CYCLE 子句防环形数据死循环——层级查询的两个工程细节都在方言里。旧式 CONNECT BY 语法（START WITH ... CONNECT BY PRIOR）依然大量存在于存量系统，读懂它是接手老系统的必修课，新代码建议统一走递归 CTE。

四、PL/SQL：写得像集合，跑得像闪电

PL/SQL 常被当成"数据库里的 Java"，这是误解的最大来源。它的杀手锏是批量绑定（BULK COLLECT 与 FORALL）：普通 FOR 循环每行在 PL/SQL 引擎与 SQL 引擎之间往返一次（上下文切换），批量操作把一万行的往返压成几次。


-- 反例：逐行处理，十万行就是十万次上下文切换
FOR r IN (SELECT id, amt FROM stage_tab) LOOP
  UPDATE accounts SET balance = balance + r.amt WHERE id = r.id;
END LOOP;

-- 正解：集合操作一步到位（能一句 SQL 就别写循环）
UPDATE accounts a
   SET balance = balance + (SELECT amt FROM stage_tab s WHERE s.id = a.id)
 WHERE EXISTS (SELECT 1 FROM stage_tab s WHERE s.id = a.id);

-- 必须过程化时：批量绑定，切换次数从十万降到千级
DECLARE
  TYPE t_ids IS TABLE OF accounts.id%TYPE;
  TYPE t_amts IS TABLE OF stage_tab.amt%TYPE;
  v_ids t_ids; v_amts t_amts;
BEGIN
  SELECT id, amt BULK COLLECT INTO v_ids, v_amts FROM stage_tab;
  FORALL i IN 1 .. v_ids.COUNT
    UPDATE accounts SET balance = balance + v_amts(i) WHERE id = v_ids(i);
  COMMIT;
END;
/

决策树很简单：能用一句 SQL 表达就写 SQL；需要分批控制（限流、断点续跑）才用 FORALL 分片；纯逐行逻辑（每行要调用外部接口）才是普通循环的地盘。错误处理配 EXCEPTION 块与 SAVE EXCEPTIONS 批量容错，自治事务 PRAGMA AUTONOMOUS_TRANSACTION 让审计日志在主事务回滚后仍能留下记录——这两个特性在第 7 章审计设计里还会出场。

五、案例：一次十倍提速的三段改写

背景。 月末佣金计算作业跑了 52 分钟，业务要求压进 10 分钟。程序主体是一个 3000 行的 PL/SQL 包，逐行处理 420 万条流水的九十万个客户。

操作。 按本节的决策树分三段改写。第一段，90 万客户的"按组排名取前三笔"逻辑从相关子查询改窗口函数——这一段占时 28 分钟，改后 4 分钟。第二段，流水汇总从逐行累加改一句 GROUP BY，占时 15 分钟改后 40 秒。第三段必须保留过程逻辑的规则校验（每客户调用评级函数），从逐行 FOR 循环改 BULK COLLECT 加 FORALL 分片（每片 1 万行），占时 9 分钟改后 2 分钟。

结果。 总耗时 52 分钟降到 7 分钟，包的行数还减了 400 行。解读。 三段改写对应语言能力的三张王牌：窗口函数消自连接、集合操作消循环、批量绑定消切换。没有动任何参数、没有加任何索引——上限在写法里。变式。 若包里有大量跨行业务规则无法集合化，可考虑把"能集合化的 80%"抽成临时表加工，只让 20% 的规则走过程逻辑——先降维再处理，往往比追求 100% 集合化更务实。

💡 关键直觉：性能优化的第一刀永远是改写，不是加资源。一句"这段逻辑能不能用一句 SQL 表达"的追问，比任何索引建议都省钱——因为写法改对了，索引才有意义。

本节要点回顾

声明式优先：数据库内部是集合引擎，应用层循环逐行搬数是性能的下限所在。
窗口三件套：ROW_NUMBER 与 RANK 管排名、LAG 与 LEAD 管偏移、ROWS BETWEEN 管累计窗口。
递归两式：新代码用递归 CTE 加 SEARCH 与 CYCLE 子句；读懂 CONNECT BY 是接手老系统的必修课。
PL/SQL 纪律：BULK COLLECT 加 FORALL 消上下文切换；逐行循环只留给真正需要逐行的逻辑。

语言上限打开了，接下来看一条 SQL 从提交到返回都经历了哪些站——十二秒的账要在流水线上逐站去查。

作者与出处

原作者：灏天文库

来源：灏天文库

整理：灏天文库整理

由灏天文库平台收录，内容或由平台用户上传，仅供学习交流