PostgreSQL PL/pgSQL存储过程：结构控制与循环实战

PostgreSQL存储过程PL/pgSQL写多了之后你会发现真正决定代码质量的往往不是那些花哨的SQL技巧而是结构控制和循环——IF怎么判断才不踩NULL的坑FOR和WHILE到底选哪个EXIT与CONTINUE怎么在嵌套循环里精准跳转异常块为什么在循环里会拖垮性能。这篇是存储过程系列里的第六篇专门把结构控制和循环这部分掰开揉碎讲一遍。内容适合已经写过几个存储过程、但控制流用得还不够得心应手的开发者也适合准备系统梳理PL/pgSQL控制逻辑的DBA看完可以直接拿这些写法去改造手头的存储过程。1. 条件分支的坑IF、ELSIF、CASE与NULL判断1.1 IF语句的基本结构和常见拼写错误PL/pgSQL里最常用的条件分支就是IF官方的标准写法是IF 条件 THEN -- 条件为TRUE时执行 ELSIF 另一条件 THEN -- 上一个条件不成立且本条件成立时执行 ELSE -- 所有条件都不成立时执行 END IF;第一个容易翻车的地方是拼写。ELSIF只有一个E很多人习惯写ELSEIF老版本的PostgreSQL对这两种写法都做了兼容但官方文档和绝大多数老代码里使用的都是ELSIF。如果团队里有代码规范检查工具建议统一用ELSIF避免代码风格混乱。第二个容易忽略的细节是IF后面的条件必须是布尔表达式。它不像C语言或Python那样接受0或1这类数字更不存在非空即真的隐式转换。如果你写IF p_count THEN -- ... END IF;PostgreSQL会直接报错因为p_count如果是个整数它不会被自动转换成布尔值。正确的做法是显式写成IF p_count 0 THEN。第三个实用技巧IF条件里可以嵌子查询这在实际业务中非常好用。比如判断某张表里是否存在满足条件的记录没必要先把结果取出来再判断直接在IF里写IF EXISTS (SELECT 1 FROM orders WHERE user_id p_user_id AND status pending) THEN RAISE NOTICE 该用户有待处理订单; END IF;这一招既简洁又直观比先SELECT count(*)到一个变量再判断不知道高到哪里去了。1.2 NULL参与判断时最容易走错分支这是我觉得PL/pgSQL里最防不胜防的一个坑新手基本都踩过。SQL世界里的布尔逻辑是三值的TRUE、FALSE还有NULL。NULL和假完全是两回事但很多判断语句会把NULL当false处理。看下面这段代码IF p_score NULL THEN RAISE NOTICE 分数为空; ELSE RAISE NOTICE 分数不为空; END IF;你本来是想判断p_score是不是NULL但p_score NULL这个表达式的结果永远是NULL永远不会变成TRUE。所以这段代码不管传入什么值走到的都一定是ELSE分支你以为的NULL分支压根不会被执行。正确的写法是使用IS NULL或者IS NOT NULLIF p_score IS NULL THEN RAISE NOTICE 分数为空; END IF;更隐蔽的情况是反向判断。比如你想知道这个分数是不是大于60IF p_score 60 THEN RAISE NOTICE 及格; ELSE RAISE NOTICE 不及格; END IF;当p_score是NULL时p_score 60的结果是NULL不是TRUE于是执行的是ELSE分支输出不及格。从业务语义上说NULL既不是及格也不是不及格你把它归到不及格可能恰好没问题但如果你的ELSE分支里写的是分数合法之类的判断那就不知不觉地把非法数据当成正常数据处理了。我的建议是凡是参与分支判断的字段第一件事就要想清楚它有没有可能是NULL。如果有可能先把NULL情况单独拎出来处理再谈大小比较。可以用COALESCE给个默认值也可以像这样显式拆开IF p_score IS NULL THEN RAISE NOTICE 未录入成绩; ELSIF p_score 60 THEN RAISE NOTICE 及格; ELSE RAISE NOTICE 不及格; END IF;1.3 CASE语句和CASE表达式一字之差行为不同很多人知道SQL里有CASE WHEN ... THEN ... END这种表达式但在PL/pgSQL里还有一个长得几乎一样的CASE ... END CASE语句两者行为和用途并不完全一样。先看CASE语句DECLARE v_status text; BEGIN v_status : active; CASE v_status WHEN active THEN RAISE NOTICE 状态正常; WHEN disabled THEN RAISE NOTICE 状态被禁用; ELSE RAISE NOTICE 未知状态; END CASE; END;注意结尾是END CASE而不是END。它和IF语句的地位是一样的都是流程控制语句执行完分支后继续往下走。它也有搜索形式适合比较复杂的条件CASE WHEN p_score 90 THEN RAISE NOTICE 优秀; WHEN p_score 80 THEN RAISE NOTICE 良好; ELSE RAISE NOTICE 继续努力; END CASE;CASE语句有一个非常容易踩的坑如果没有任何WHEN分支命中而且你也没写ELSE它会直接抛出异常CASE_NOT_FOUND。这一点和SQL里的CASE表达式完全不同SQL的CASE表达式在没有匹配且没有ELSE时返回NULL程序不会报错。这其实是好事还是坏事要看场景。有些时候你希望找不到匹配就是异常CASE语句恰好满足了这种需求但如果你只是想做简单的条件分发忘了写ELSE那就是一个隐藏炸弹。所以在PL/pgSQL里写CASE语句时我基本都会补一个ELSE哪怕ELSE里只写一个NULL;占位也好。别小看这个习惯关键时刻能帮你减少一次线上故障。2. 循环家族全景LOOP、WHILE、FOR、FOREACH2.1 LOOP死循环只有EXIT能救你先说最原始的LOOP。它没有自己的终止条件需要你在循环体里手动EXIT否则就真的会永远跑下去DECLARE v_counter int : 0; BEGIN LOOP v_counter : v_counter 1; EXIT WHEN v_counter 10; END LOOP; RAISE NOTICE counter %, v_counter; END;看起来很简单但它其实是四种循环里最容易出事故的。因为循环体里一旦条件判断写错或者某个变量的更新被跳过整个循环就会变成无终止的死循环如果这个存储过程恰好又被并发任务调度数据库连接可能直接被占满。我见过一种比较危险的用法在循环体里用IF来判定退出条件但IF的条件和更新变量的逻辑前后顺序写反了导致EXIT永远等不到想要的值。比如LOOP IF v_counter 10 THEN EXIT; END IF; v_counter : v_counter 1; END LOOP;这段代码其实能正常退出但如果把递增语句写到IF之前或者把比较符号写反成v_counter 10那循环就会一直跑下去。我自己的习惯是能用EXIT WHEN绝不用IF EXIT因为EXIT WHEN把条件和退出动作写在同一行更容易检查也少一对END IF。LOOP适合什么场景呢一般来说当你无法在进入循环前确定迭代次数、需要完全由循环体内逻辑决定何时退出时用它最合适。比如轮询等待某个外部条件满足或者逐行处理一批数据但每步都可能跳过。2.2 WHILE循环先判断后执行WHILE循环把终止条件放到了循环开头DECLARE v_counter int : 1; BEGIN WHILE v_counter 10 LOOP RAISE NOTICE 当前值 %, v_counter; v_counter : v_counter 1; END LOOP; END;它的关键特点是先判断后执行。如果初始条件就不成立循环体一次都不会执行。这跟LOOP有本质区别LOOP无论如何都会先执行一遍循环体WHILE可能整个循环体直接被跳过。写WHILE时最容易犯的错是忘记在循环体里更新条件变量或者更新的位置不对。比如条件要求v_counter从1增到10但你把递增写在一个永远不会执行到的IF分支里循环就卡死在原地。另一个常见问题是边界条件写错 10和 10差一次迭代虽然不致命但会带来边界数据漏处理的问题。应用场景上WHILE非常适合处理完一批还剩一批的批处理模式。比如一个订单表里有大量待处理记录你想每轮处理100条直到全部处理完。用WHILE就很顺WHILE EXISTS (SELECT 1 FROM orders WHERE status pending) LOOP UPDATE orders SET status processing WHERE id IN ( SELECT id FROM orders WHERE status pending LIMIT 100 ); END LOOP;这种写法每轮都重新检查表里还有没有待处理数据天然就不容易漏。2.3 FOR整数循环边界、步长和循环变量FOR循环是我日常写得最多的一种语法非常简洁FOR i IN 1..10 LOOP RAISE NOTICE 第 % 次循环, i; END LOOP;几点需要特别注意。首先是边界1..10是包含两端的也就是从1到10一共执行10次和很多语言里左闭右开的习惯不一样。反着写用REVERSEFOR i IN REVERSE 10..1 LOOP RAISE NOTICE %, i; END LOOP;其次是步长。PostgreSQL 14开始支持BY参数比如FOR i IN 1..10 BY 2 LOOP RAISE NOTICE %, i; -- 输出 1, 3, 5, 7, 9 END LOOP;如果你还在用14之前的版本这个参数用不了只能自己用变量模拟步进写法会麻烦一些。第三是循环变量的身世。FOR循环里这个i不需要在DECLARE区声明它是自动生成的局部变量作用域就是这个循环体。关键限制是循环体内不能给这个变量赋值你即使写了i : 5下一次迭代i还是会被FOR机制重新赋值为下一个整数你的赋值根本不起作用。这个设计其实是有意为之的防止开发者干扰循环的正常推进。还有一个小细节循环结束时循环变量的值是最后一次迭代时的值。如果循环一次都没执行那这个变量在循环体内部其实等于不存在你在循环外引用它可能会报错或者拿到预期之外的值。所以不要指望靠循环变量来传递结果结果应该存在独立的DECLARE变量里。2.4 FOREACH专为数组设计的遍历器FOREACH是PostgreSQL特有的循环语法遍历数组时比FOR舒服得多DECLARE v_arr int[] : ARRAY[1, 2, 3, 4]; v_item int; BEGIN FOREACH v_item IN ARRAY v_arr LOOP RAISE NOTICE %, v_item; END LOOP; END;如果是多维数组默认不带SLICE时FOREACH会把所有最内层的元素挨个遍历出来不会保留数组维度的结构。如果你想按切片来遍历比如把一个二维数组按第一层切成一维数组逐个处理就需要指定SLICE 1DECLARE v_arr int[] : ARRAY[[1, 2], [3, 4]]; v_slice int[]; BEGIN FOREACH v_slice SLICE 1 IN ARRAY v_arr LOOP RAISE NOTICE 切片 %, v_slice; -- 依次输出 {1,2}, {3,4} END LOOP; END;注意这里v_slice要声明成int[]如果声明成intSLICE模式下类型就对不上。FOREACH最常用的场景是以数组作为输入参数在存储过程里展开处理。比如某个业务接口传入一批订单ID你希望逐个处理并跳过无效ID用FOREACH循环会非常自然。3. 循环跳转控制EXIT、CONTINUE与标签的正确用法3.1 用WHEN子句把条件并入EXIT/CONTINUEEXIT和CONTINUE是循环里两个最重要的操盘手。EXIT表示彻底退出循环CONTINUE表示跳过本次迭代剩余代码、直接进入下一轮。它们都可以搭配WHEN子句让条件判断更紧凑LOOP v_counter : v_counter 1; EXIT WHEN v_counter 100; CONTINUE WHEN v_counter % 2 0; RAISE NOTICE 只打印奇数 %, v_counter; END LOOP;注意执行顺序先判断EXIT WHEN再看CONTINUE WHEN。上面这段代码里当v_counter是偶数时CONTINUE成立直接进入下一轮循环下面的RAISE不会执行。WHEN子句里的条件同样要小心NULL的问题。如果条件表达式结果是NULLWHEN不会生效循环不会退出、也不会跳过会继续往下走。大多数情况下这符合直觉但如果你写的条件里涉及可能为NULL的字段最好先显式处理NULL。3.2 命名标签嵌套循环中跳出指定层级只有一层循环的时候EXIT和CONTINUE没什么悬念跳的就是当前这一层。一旦嵌套循环多起来默认行为就不够了——你只想跳出内层循环外层继续跑或者干脆直接跳出整个双层循环。这时候就要给循环命名标签。语法是用一对尖括号把标签名包起来放在循环关键字之前outer_loop FOR i IN 1..3 LOOP inner_loop FOR j IN 1..3 LOOP -- 条件满足时直接跳出外层循环 EXIT outer_loop WHEN i * j 4; RAISE NOTICE i%, j%, i, j; END LOOP; END LOOP;同样CONTINUE outer_loop会跳过外层循环的当前迭代重新开始外层循环的下一轮。这个功能在两层以上嵌套里特别好用没有它的话你得用好多个IF标志位才能实现相同的控制逻辑。标签的另一个好处是让代码自解释。看到EXIT outer_loop读代码的人马上就知道这是要退出哪一层而没有标签的EXIT在多层嵌套里经常让人一头雾水。3.3 EXIT还能直接退出整个BEGIN块这条算是冷门知识但某些场景下非常有用。EXIT并不只能用在循环里它还能配合带标签的BEGIN块使用实现直接跳到块末尾的效果process_block BEGIN -- 一些前置校验 IF p_skip THEN EXIT process_block; END IF; -- 这里是一大段复杂的业务处理 PERFORM do_something(p_param); END process_block; RAISE NOTICE 继续后续流程;这个写法的本质是EXIT带着标签退出指定的块控制流直接跳到块结束后的下一条语句。有点类似其他语言里的goto但PostgreSQL把它限制得很安全只能在包围当前语句的标签块之间跳。它有什么用我有一个比较深刻的体会当你的业务逻辑存在如果满足某个条件就跳过后面这一大段处理的需求时与其把一大段代码都包在IF NOT ... THEN里导致嵌套层级越来越深不如直接用这种带标签的EXIT把块的中间切断。代码不仅横向缩进少了可读性也明显更好。不过要注意CONTINUE不能用在非循环的BEGIN块里它只能出现在循环或者FOR/FOREACH内部。4. 在循环里处理查询结果游标、FOR查询与DML陷阱4.1 FOR-IN-QUERY隐式游标的基本玩法存储过程里处理查询结果集最常见的写法就是FOR循环直接跟一个SELECT语句FOR rec IN SELECT id, order_no, amount FROM orders WHERE created_at 2024-01-01 LOOP RAISE NOTICE 订单 %, 金额 %, rec.order_no, rec.amount; END LOOP;这里rec是一条记录变量不需要在DECLARE区声明字段名就是SELECT列表里的别名。它内部走的是隐式游标机制查询结果被打开成一个游标循环每轮取一行直到取完自动结束。从使用体验上来讲非常友好写起来也快。但这里有一个需要了解的性能背景如果查询返回的结果集特别大FOR-IN-QUERY循环依然是把查询作为一个整体在事务里推进的长事务、内存、锁这些资源压力都会变大。官方文档的态度是返回超大结果集时应该考虑显式游标并且在循环里用FETCH逐批取数据。所以FOR-IN-QUERY适合中小规模结果集比如几千几万行的批量处理如果是百万人级别的大报表遍历建议看下一节的显式游标方案。4.2 显式游标大规模结果集的兜底方案当你在PL/pgSQL里需要精细控制读取行为时用显式游标是更好的选择。一个完整的套路是这样DECLARE cur CURSOR FOR SELECT id, name FROM users WHERE status active; rec record; BEGIN OPEN cur; LOOP FETCH NEXT FROM cur INTO rec; EXIT WHEN NOT FOUND; -- 处理 rec RAISE NOTICE 处理用户 %, rec.name; END LOOP; CLOSE cur; END;这里面的核心是FETCH NEXT FROM cur INTO rec它每轮取一行EXIT WHEN NOT FOUND是取不到行之后退出循环的固定搭配。为什么用EXIT WHEN NOT FOUND而不是判断rec IS NULL因为rec是记录类型取不到行时它可能还保留着上一次的值只有NOT FOUND状态是正确的判据。显式游标的优势在于你可以控制FETCH的方向、一次取多少行比如用FETCH FORWARD 100可以在中途随意关闭游标不必等循环自然结束。在处理几百万行的大表扫描时这种控制感非常宝贵。需要注意PL/pgSQL里NOT FOUND是一个全局的变量状态不只由FETCH置位普通的SELECT INTO如果没取到行也会让它变成TRUE。所以在循环里如果有其他查询动作要小心它对NOT FOUND状态的覆盖建议在开销允许的情况下用独立的布尔变量记录是否需退出。4.3 循环内做DML的锁冲突与快照问题循环里对同一张表或者相关表做INSERT/UPDATE/DELETE是存储过程业务里非常常见的操作但这里坑很深。最典型的是先SELECT一行再UPDATE这一行的模式。在READ COMMITTED隔离级别下如果你SELECT时没有加FOR UPDATE这行可能在你处理期间被其他事务更新掉。等你执行UPDATE时会收到一个tuple concurrently updated的错误整个事务可能就此回滚。比较稳健的写法是在SELECT阶段就锁定目标行FOR rec IN SELECT id, balance FROM accounts WHERE status processed AND balance 0 FOR UPDATE LOOP UPDATE accounts SET status checked WHERE id rec.id; END LOOP;但即便加了FOR UPDATE你也必须意识到如果你在事务里锁了很多行锁的持有时间会一直到事务结束并发事务一旦碰到这些行就会阻塞。所以循环体里有DML时尽量让每个事务的处理量保持在可控范围不要一个超大事务跑几个小时那样在业务高峰期等于事故本身。另外一个常见怪现象是循环里UPDATE了一张表而FOR循环的查询源恰好也是这张表。由于查询结果是在循环开始时就打开了快照你在循环里更新过的行游标可能仍然返回旧版本的数据这会让你觉得明明改了怎么又读出来还是旧的。这不算Bug是PostgreSQL的快照隔离机制在起作用。理解了这一点你就能猜到能用一个UPDATE语句完成的批量修改就不要用循环逐行来搞。先问自己一句能不能写成集合操作这句灵魂拷问能帮你省掉很多不必要的麻烦。5. 异常处理如何与结构控制协同5.1 EXCEPTION块本质上也是一种控制流PL/pgSQL里的异常处理和普通语言的try-catch很像语法长这样BEGIN INSERT INTO orders(id, order_no) VALUES (nextval(orders_id_seq), p_order_no); EXCEPTION WHEN unique_violation THEN RAISE NOTICE 订单号重复已跳过; END;一个带EXCEPTION子句的BEGIN块本质上是开启了子事务。当块内部发生异常时PostgreSQL会把整个块里已经做的修改全部回滚然后进入异常分支继续执行。从控制流角度看它有点像正常路径失败就切换分支的特殊结构控制。但它和IF有本质区别IF是主动判断异常是被动响应。什么时候用异常什么时候用条件判断这是一条值得死记硬背的分界线能提前判断的异常优先用IF无法预判或判断成本极高的情况才用EXCEPTION。比如你要处理一个可能不存在的用户。正常流程是先查用户存不存在不存在就跳过IF EXISTS (SELECT 1 FROM users WHERE id p_user_id) THEN PERFORM process_user(p_user_id); END IF;这种场景就不该用EXCEPTION WHEN no_data_found去捕获因为用户可能不存在这一点你完全可以提前查出来没必要让程序在异常路径里走一遭。5.2 能用IF判断就不要依赖异常捕获但有另一类场景条件判断根本帮不上忙。比如同时有大量并发事务在往同一张表里插入数据而这张表上有唯一约束。你即使先SELECT再INSERT在SELECT结束到INSERT执行之间别的会话也可能已经插入了相同记录。这种判断之后依然可能失败的竞态条件唯一可靠的兜底方式就是捕获unique_violation异常BEGIN INSERT INTO users(id, email) VALUES (v_id, v_email); EXCEPTION WHEN unique_violation THEN RAISE NOTICE 邮箱已存在%, v_email; END;这里就体现了异常处理的真正价值它处理的是那些不该正常发生、但一旦发生就要兜住的场景。判断一个值是否已存在属于可以预判的逻辑主要靠IF而并发冲突导致的唯一键违规属于不可完全预判的边界情况交给异常处理更合适。核心原则是不要用异常处理来掩盖你本该在正常逻辑里做掉的判断否则异常块会大量增加性能开销和调试难度。5.3 异常块的子事务开销一个量级级的性能差我早期写过一段存储过程功能是对一批历史订单逐条尝试匹配新价格匹配失败就跳过。我当时图省事直接在循环体内包了一个带EXCEPTION的BEGIN块。逻辑完全没问题结果线上跑了很久没跑完一查会话发现它在处理大约几十万行的时候慢到无法接受。后来我单独做了个对比测试。同样一段循环不包异常块的情况下处理一万行几乎瞬间完成包了异常块、每一行都开启子事务之后时间变成了原来的几十倍不止而且数据量越大差距越明显。原因是PostgreSQL的EXCEPTION块不是廉价的加一个标记它真正开启了一个子事务。子事务需要额外记录事务状态、保存点信息正常不抛异常时开销已经不小一旦真发生异常还要回滚整个子事务并刷新状态。在一个反复执行几十万次的循环体里这个小开销会被放大到触目惊心的程度。所以我的习惯是需要逐行容错时尽量先把那些注定会失败的数据提前过滤掉让异常块的触发概率无限趋近于零。比如批量插入前先在存储过程里用一条SELECT ... GROUP BY把重复数据剔除再进循环做逐行插入这样循环里的异常块基本不会触发性能自然就回来了。6. 实操过程中的几个进阶技巧与备忘6.1 用RAISE把循环状态可视化存储过程调试起来远比普通应用代码麻烦尤其是循环几千次之后你根本不知道它跑到了哪里。我的习惯是在关键循环里加RAISE NOTICE输出进度信息尤其是变量比较多、逻辑比较绕的时候FOR rec IN SELECT id, name FROM users LOOP RAISE NOTICE 正在处理用户 id%, name%, rec.id, rec.name; -- ... END LOOP;如果数据量很大每一行都RAISE NOTICE会让日志爆炸实际使用中我通常只打印每处理一段的进度比如每次循环100行就输出一条v_count : 0; FOR rec IN SELECT ... LOOP v_count : v_count 1; IF v_count % 100 0 THEN RAISE NOTICE 已处理 % 行, v_count; END IF; END LOOP;RAISE的级别可以选DEBUG、LOG、NOTICE、WARNING线上生产环境我会用RAISE DEBUG避免影响正常的应用日志。6.2 动态SQL与循环组合时的格式化和注入防御存储过程里要循环处理一批动态的表名或列名时需要把动态SQL和循环组合起来。比如你有一组归档表想统一做某种操作FOR t IN SELECT table_name FROM information_schema.tables WHERE table_schema public AND table_name LIKE archive\_% LOOP EXECUTE format(UPDATE %I SET archived_at now(), t.table_name); END LOOP;这里的关键原则是表名、列名这类结构对象不能直接用拼接字符串必须用format函数的%I占位符值呢则应该用USING参数传进去而不是拼到SQL字符串里。我见过不少人图省事写了EXECUTE SELECT * FROM || t.table_name一旦表名来自外部输入就是妥妥的注入风险。format加%I会自动把标识符正确转义并加双引号这个习惯必须养成。另外动态SQL在循环里的成本不低因为它每次都要走一遍解析和规划的过程。如果循环次数很多且SQL结构不变尽量能把动态SQL提到循环外面就别放里面实在没办法的话至少保持循环里的SQL模板一致让PostgreSQL能复用计划。6.3 嵌套结构过深时怎么办不管是IF嵌套还是循环嵌套当代码缩进层级超过三层我就觉得这段代码离维护地狱不远了。PL/pgSQL本身不是一种擅长表达复杂嵌套逻辑的语言硬往里堆会把读代码的人折磨疯。我自己的两个处理手段很朴素。第一拆函数。把内层那坨复杂的循环体抽成一个独立的函数或存储过程在外层循环里直接调用边界清晰调试也方便。第二改用集合操作替代嵌套循环。比如你要对A表的每一行去B表里查找匹配数据这本质上是一个JOIN绝大多数情况下一条带窗口函数的UPDATE或INSERT...SELECT就能解决完全用不着双循环。这个建议听起来不新鲜但我发现真正去实践的人不多。很多PL/pgSQL的代码膨胀就是因为当初没忍住写了一个三重嵌套循环后面所有人都在这个循环里打补丁。与其如此不如在一开始多花十分钟想想能不能换一种不靠循环的写法最后再分享一点个人感触。写存储过程的结构控制难的不是语法本身而是每写一个分支、一个循环都要强迫自己把NULL、边界、并发、性能这四个问题在脑子里过一遍。这个习惯形成之后大部分控制流的坑基本就踩不到你了。如果你也遇到过类似的问题或者有更好的PL/pgSQL控制流写法欢迎在评论区一起交流。