PostgreSQL / 系统课时

控制流与异常处理

控制流与异常处理

PL/pgSQL 的过程逻辑通常同时涉及变量、分支、循环、动态 SQL 和异常恢复。真正难点不是记住语法,而是控制变量作用域、正确区分标识符与数据参数,并理解异常块会回滚其中已经发生的数据库修改。

学习目标

  • 能使用变量、IF 和循环组织过程逻辑
  • 能用 EXECUTE ... USING 安全执行动态 SQL
  • 能捕获指定异常并理解异常块的回滚边界

变量和控制流先限定作用域

PL/pgSQL 变量在块的 DECLARE 区声明,随后可以在 IF、CASE、LOOP、WHILE、FOR 或 FOREACH 中使用。优先让变量只活在真正需要它的块里,这样能减少同名变量和表字段之间的歧义。

FOREACH 适合直接遍历数组;如果是遍历查询结果,通常使用 FOR record_variable IN SELECT ... LOOP。控制流负责决定“执行哪一段”,而不是替代 SQL 本身能够完成的集合运算。一次 UPDATE 能解决的问题,不要改写成逐行循环。

用一个主示例串起分支、循环和动态 SQL

下面的匿名代码块遍历两个租户名,为每个租户创建临时表。north 写入合法值并成功,south 故意写入负数触发检查约束,再由异常块捕获。临时表使用 ON COMMIT DROP,因此在自动提交环境中语句结束后会清理,便于重复练习。

sql
SET client_min_messages = notice;

DO $$
DECLARE
    tenant text;
    target_table text;
    candidate integer;
    affected integer;
BEGIN
    FOREACH tenant IN ARRAY ARRAY['north', 'south'] LOOP
        target_table := 'ctrl_' || tenant || '_demo';

        EXECUTE format(
            'CREATE TEMP TABLE %I (
                id integer PRIMARY KEY,
                value integer CHECK (value >= 0)
             ) ON COMMIT DROP',
            target_table
        );

        IF tenant = 'north' THEN
            candidate := 10;
        ELSE
            candidate := -1;
        END IF;

        BEGIN
            EXECUTE format(
                'INSERT INTO %I (id, value) VALUES ($1, $2)',
                target_table
            )
            USING 1, candidate;

            GET DIAGNOSTICS affected = ROW_COUNT;
            RAISE NOTICE '%: inserted % row(s)', tenant, affected;
        EXCEPTION
            WHEN check_violation THEN
                RAISE NOTICE '%: rejected value %', tenant, candidate;
        END;
    END LOOP;
END
$$;

预期输出

text
SET
NOTICE:  north: inserted 1 row(s)
NOTICE:  south: rejected value -1
DO

不同客户端对 NOTICE 的展示前缀和空格可能略有差异,但两条业务消息的顺序应保持一致。

动态 SQL 要区分标识符和数据值

动态 SQL 最常见的错误,是试图用一个参数占位符同时解决表名和数据值。USING 只能绑定数据值,不能把 $1 当作表名、列名或关键字。因此示例中表名通过 format('%I', target_table) 安全地作为标识符插入,而 id 和 candidate 通过 USING 绑定。

对字符串值也不要手工拼单引号。数据值交给 USING,不仅能避免注入和转义错误,也让类型转换更清晰。只有真正动态的 SQL 结构,例如表名、排序列或可选子句,才需要经过白名单判断后再拼接。

EXECUTE 的执行计划会在每次执行时重新生成,这与许多静态 PL/pgSQL SQL 语句可以复用计划不同。只有查询结构确实需要变化时才使用动态 SQL;如果只是参数值变化,优先保留静态 SQL。

异常块会形成回滚边界

EXCEPTION 不是普通的错误日志分支。进入带异常处理的子块后,如果其中某条 SQL 失败,该子块内此前已经发生的数据库状态修改会回滚,然后控制流转到匹配的 WHEN 分支;PL/pgSQL 局部变量的值仍保留。

在主示例里,创建临时表发生在内层异常块之外,所以 south 的插入失败时不会撤销建表;失败的 INSERT 自身会被回滚,随后执行 RAISE NOTICE。这种边界非常适合“尝试一个操作,失败后做补偿或记录”,但不适合把整段正常控制流都包进异常处理。

让异常处理服务于可恢复性

异常处理的判断标准不是“程序不报错”,而是“失败后数据库仍处于可解释状态”。需要记录错误详情时,可以在异常分支读取 SQLSTATE 与 SQLERRM;需要调用方感知失败时,应使用 RAISE 重新抛出,而不是只打印消息。

设计过程函数时,可以把不可恢复错误留给事务整体回滚,把可恢复的局部失败放进小范围子块,再明确记录补偿结果。变量负责保存状态,控制流负责选择路径,动态 SQL 负责处理真正变化的结构,而异常块只负责错误边界;把这四类职责分开,PL/pgSQL 才不会变成难以调试的脚本。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

控制流与异常处理

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通