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,因此在自动提交环境中语句结束后会清理,便于重复练习。
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
$$;预期输出
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 才不会变成难以调试的脚本。