企业官网建设流程全解析

这篇是PostgreSQL存储过程系列的第六篇，专门把PL/pgSQL里的结构控制和循环讲透。存储过程写多了你会发现，真正决定代码质量的并不是SQL写得多花哨，而是分支、循环、异常这些控制流用得顺不顺手。很多业务逻辑——比如按条件更新、循环处理订单、批量校验数据——本质上就是“判断+遍历+异常兜底”的组合，PL/pgSQL里对应的就是IF、CASE、LOOP、WHILE、FOR、FOREACH、BEGIN...EXCEPTION这套结构控制语法。

本文用大量可直接抄作业的代码片段，逐一拆解每种控制结构的语法形态、执行机制和适用场景，最后带一个完整的实战案例把整串知识串起来。适合正在写函数、触发器，或者打算把复杂业务逻辑下沉到数据库层的开发者阅读。

1. 条件分支：IF语句的完整形态

1.1 从最简单的IF-THEN开始

PL/pgSQL里最基本的IF语句就是IF-THEN结构，语法上跟大多数编程语言没有本质区别：

CREATE OR REPLACE FUNCTION check_order_status(order_amount NUMERIC) RETURNS TEXT LANGUAGE plpgsql AS $$ DECLARE result_text TEXT; BEGIN IF order_amount > 1000 THEN result_text := '大额订单'; END IF; -- 注意：如果条件不满足，result_text是NULL RETURN result_text; END; $$;

这个例子里有个很容易踩的坑——if条件不满足时，result_text并不会被赋予任何值，它保持NULL。调用函数后输出的是空值，而不是你预期的“普通订单”。所以实际项目里，IF-THEN后面几乎总是要跟ELSE，除非你确实只需要在条件成立时做一些副作用操作（比如写日志、修改某个变量）。

IF-THEN的执行机制很直接：先计算布尔表达式，结果为TRUE才执行THEN后面的语句块，为FALSE或NULL则跳过。关键点在于，PL/pgSQL里的布尔表达式遵循SQL的三值逻辑，NULL参与逻辑运算的结果往往和直觉相反。

1.2 ELSIF多分支判断细节

多分支判断用IF-THEN-ELSIF-ELSE，注意PostgreSQL的写法是ELSIF，不是其他语言里常见的ELSE IF，少写一个字母就会报语法错误。这是个高频手误，我见过不少人在这里卡住。

CREATE OR REPLACE FUNCTION get_tier(score INTEGER) RETURNS TEXT LANGUAGE plpgsql AS $$ BEGIN IF score >= 90 THEN RETURN 'A'; ELSIF score >= 80 THEN RETURN 'B'; ELSIF score >= 60 THEN RETURN 'C'; ELSE RETURN 'D'; END IF; END; $$;

执行逻辑是自上而下逐条判断，一旦某个条件为TRUE，就执行对应分支并跳出整个IF结构，后面的ELSIF不会再被检查。这一点很重要，因为你可以把判断条件按优先级从高到低排列，避免写复杂的AND条件。比如上面的例子，先判断score >= 90，再判断score >= 80，不需要在第二个条件里写成score >= 80 AND score < 90，因为能走到第二个分支，说明第一个条件已经不成立了，隐含了score < 90这个事实。

这也是IF-ELSIF和多个独立IF语句的核心区别——ELSIF天然具备“互斥且短路”的特性，性能更好，逻辑也更清晰。多个独立IF则每个都会被计算，如果条件设计不当，一个变量可能被后续分支重新覆盖，调试时容易被绕晕。

1.3 嵌套与布尔表达式的真假陷阱

嵌套IF是IF条件复杂时的常规解法，但嵌套过深会严重影响可读性。我给自己定的规矩是：超过三层嵌套，就必须考虑拆分成独立的子函数，或者用CASE表达式重构。一个典型的三层嵌套长这样：

IF user_type = 'VIP' THEN IF order_amount > 5000 THEN IF is_first_order THEN discount := 0.8; ELSE discount := 0.85; END IF; ELSE discount := 0.9; END IF; ELSE discount := 1.0; END IF;

这个逻辑虽然能跑，但每个分支缩进一层，层次一多眼睛就花。更推荐的做法是先把条件合并成布尔变量，再用单个IF判断：

DECLARE is_vip_large_order BOOLEAN; BEGIN is_vip_large_order := user_type = 'VIP' AND order_amount > 5000; IF is_vip_large_order THEN discount := 0.85; ELSIF user_type = 'VIP' THEN discount := 0.9; ELSE discount := 1.0; END IF; END;

布尔表达式的最大陷阱是NULL传播。在SQL里，NULL = NULL的结果不是TRUE，而是NULL。如果你写IF user_type = 'VIP'，而user_type恰巧是NULL，这个条件的真假值和FALSE一样，会走进ELSE分支。更隐蔽的情况是IF NOT (user_type = 'VIP')，当user_type为NULL时，NOT NULL仍然是NULL，所以条件不成立，也不会进入THEN分支。这个行为和逻辑直觉完全相反，排查时很容易怀疑人生。

安全建议：判断可空字段时，显式用IS NULL或IS NOT DISTINCT FROM：

IF user_type IS NOT DISTINCT FROM 'VIP' THEN -- 当user_type本身就是'VIP'或NULL时都走这里 END IF;

2. CASE表达式：比IF更清晰的分支武器

2.1 简单CASE与搜索CASE的区别

CASE表达式在PL/pgSQL里有两种形态：简单CASE和搜索CASE。简单CASE是针对单个表达式做等值匹配：

CASE level_code WHEN 'A' THEN '优秀' WHEN 'B' THEN '良好' WHEN 'C' THEN '及格' ELSE '不及格' END

注意简单CASE的匹配语义是=，也就是等价于level_code = 'A'。如果level_code是NULL，它不会匹配任何WHEN分支，直接落入ELSE。简单CASE有个限制：无法表达范围判断、大小判断这类非等值条件。

搜索CASE则完全灵活，每个WHEN后面跟一个完整的布尔表达式：

CASE WHEN score >= 90 THEN '优秀' WHEN score >= 80 THEN '良好' WHEN score >= 60 THEN '及格' ELSE '不及格' END

搜索CASE其实就是IF-ELSIF-ELSE的表达式版，它最大的价值是：可以赋值给变量、可以嵌套在SQL语句里、可以作为RETURN的直接返回值。因为它是表达式而非语句，所以能用在普通SQL里，而IF是PL/pgSQL的过程语句，只能在函数内部使用。

2.2 在赋值和查询中活用CASE

CASE表达式最常见的用法是赋值。我比较推荐把简单的多分支判断写成CASE而不是IF，代码会短很多：

CREATE OR REPLACE FUNCTION format_phone(phone TEXT) RETURNS TEXT LANGUAGE plpgsql AS $$ DECLARE region TEXT; BEGIN region := CASE WHEN phone LIKE '13%' OR phone LIKE '18%' THEN '移动网络' WHEN phone LIKE '15%' THEN '联通网络' ELSE '其他' END; RETURN region; END; $$;

CASE还可以直接在动态拼接的SQL里使用，比如你要根据某个条件统计不同时间范围的订单数：

SELECT CASE WHEN order_date >= CURRENT_DATE - INTERVAL '7 days' THEN '近7日' WHEN order_date >= CURRENT_DATE - INTERVAL '30 days' THEN '近30日' ELSE '更早' END AS period, COUNT(*) FROM orders GROUP BY 1;

执行顺序上，CASE同样是从上到下匹配，第一个命中的分支生效，后续分支不再计算。这也意味着你应该把最具体、优先级最高的条件放在最前面。

2.3 CASE与IF的选型思考

选IF还是CASE，我的经验判断标准是：

• 判断逻辑是“等值匹配”或“范围分段”，且结果是一个简单的值，用CASE最优。
• 判断之后需要执行多行复杂的语句、嵌套子过程、做变量修改，用IF。
• 需要提前RETURN跳出函数，用IF，因为CASE只能产出值，不能执行RETURN。

举个例子，如果每个分支不只是返回一个值，还要更新几张表、记录日志、调用其他函数，那必须用IF：

IF status = 'PAID' THEN PERFORM record_payment_log(order_id); UPDATE orders SET paid_at = now() WHERE id = order_id; ELSIF status = 'CANCELLED' THEN PERFORM record_cancel_log(order_id); END IF;

CASE在这种场景下毫无用武之地，因为它没有“执行副作用”的能力。理解表达式和语句的差异，是掌握PL/pgSQL的关键门槛。

3. 循环控制：LOOP、WHILE、FOR与FOREACH

3.1 基础LOOP的退出机制

最简单的循环是无限LOOP，配合EXIT退出。很多初学者不理解为什么要先写一个“无限”循环，实际上它提供了最大的灵活性——你可以把退出条件写在循环体任何一个位置，而不是像WHILE那样只能在循环开始前判断。

CREATE OR REPLACE FUNCTION test_loop_demo() RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE i INTEGER := 0; BEGIN LOOP i := i + 1; EXIT WHEN i >= 5; RAISE NOTICE '当前i=%', i; END LOOP; END; $$;

这段代码执行后，RAISE NOTICE会打印i=1到i=4。当i递增到5，EXIT WHEN条件满足，循环退出。注意检查顺序是每轮循环体执行完毕后进行的，所以i=5这一轮不会执行NOTICE打印——但如果你把EXIT WHEN放在递增之前，行为就完全不同了，这里非常容易出边界值错误。

EXIT后面可以带循环标签实现“跳出指定层循环”。多重嵌套循环里，这个功能能救命：

DECLARE i INTEGER; j INTEGER; BEGIN outer_loop: LOOP FOR i IN 1..10 LOOP FOR j IN 1..10 LOOP EXIT outer_loop WHEN i * j > 50; RAISE NOTICE 'i=%, j=%', i, j; END LOOP; END LOOP; END LOOP outer_loop; END;

这里的EXIT outer_loop会直接跳出最外层的循环，而不是只跳出j那一层。没有标签的话，EXIT只能退出当前最内层的循环，你还需要额外加一个布尔标志位来控制外层退出，代码会丑陋不少。标签写在LOOP关键字之前的写法是PostgreSQL的标准能力，建议牢记。

3.2 WHILE循环的应用场景

WHILE循环适合那种“退出条件在循环开始时就能判断”的场景，典型的比如等待某个资源就绪、逐级处理层级数据：

CREATE OR REPLACE FUNCTION get_parent_chain(leaf_node_id INTEGER) RETURNS TABLE(node_id INTEGER, level INTEGER) LANGUAGE plpgsql AS $$ DECLARE current_id INTEGER := leaf_node_id; current_level INTEGER := 0; BEGIN WHILE current_id IS NOT NULL LOOP RETURN QUERY SELECT t.node_id, current_level FROM tree_table t WHERE t.id = current_id; SELECT t.parent_id INTO current_id FROM tree_table t WHERE t.id = current_id; current_level := current_level + 1; END LOOP; END; $$;

这个例子模拟了在树形结构里逐层往上找父节点的过程。每轮循环从当前节点出发查父节点，再带着父节点进入下一轮。WHILE的条件是current_id为非空，一旦顶层节点的parent_id为NULL，循环自然终止。

WHILE的隐患是死循环风险。如果查询结果意外返回了自身ID，或者数据表里存在环路，循环将永远无法结束。生产环境里建议加一个循环次数上限作为保险：

DECLARE guard_count INTEGER := 0; BEGIN WHILE current_id IS NOT NULL AND guard_count < 100 LOOP -- 业务逻辑 guard_count := guard_count + 1; END LOOP; END;

这个习惯的成本极低，但在数据质量不稳的旧系统里，能帮你避免一个跑几个小时的任务。

3.3 FOR循环的三种模式

FOR循环是PL/pgSQL里最常用的循环形态，它有三种模式，每种都有独立的适用场景。

第一种：整数范围循环

FOR i IN 1..100 LOOP RAISE NOTICE 'i=%', i; END LOOP;

这里的1..100是包含边界的，也就是i会从1走到100，共100次。如果写REVERSE 100..1，则从100递减到1。另外这里有个小细节：循环变量i不需要在DECLARE里声明，系统自动创建。但如果你在DECLARE里声明了同名变量，循环变量会“遮蔽”外层变量，循环结束后外层变量保持原值，这个行为容易造成困惑。

第二种：查询结果集循环

FOR rec IN SELECT id, order_no, amount FROM orders WHERE created_at >= CURRENT_DATE ORDER BY id LOOP RAISE NOTICE '订单%金额%', rec.order_no, rec.amount; END LOOP;

这种模式下，rec是一个自动声明的记录类型变量，可以用rec.字段名访问每一行的列值。这种循环非常适合逐行处理数据——比如批量发送通知、逐条校验数据、同步变更历史等。PL/pgSQL会在循环开始时自动打开一个隐式游标，边取边处理，不会一次性加载全部数据到内存，所以即使结果集很大也不会造成内存压力。

第三种：动态SQL结果集循环

FOR rec IN EXECUTE format('SELECT * FROM %I WHERE amount > %L', table_name, min_amount) LOOP PERFORM process_one_row(rec); END LOOP;

表名或过滤条件来自变量时，必须用EXECUTE动态执行。注意动态SQL里用format函数配合%I、%L占位符做标识符和字面量的转义，能有效防止SQL注入，也避免标识符大小写问题。这个细节值得形成肌肉记忆——涉及拼接SQL的地方，永远不要直接用||拼字符串。

3.4 FOREACH遍历数组

FOREACH是给数组类型准备的循环，普通SQL里没有对应物。假设你有一个整型数组参数，想逐个判断元素：

CREATE OR REPLACE FUNCTION process_id_list(id_list INTEGER[]) RETURNS INTEGER LANGUAGE plpgsql AS $$ DECLARE element_id INTEGER; processed_count INTEGER := 0; BEGIN FOREACH element_id IN ARRAY id_list LOOP UPDATE accounts SET processed = TRUE WHERE id = element_id; IF FOUND THEN processed_count := processed_count + 1; END IF; END LOOP; RETURN processed_count; END; $$;

FOREACH还有遍历多维数组的切片能力：FOREACH element SLICE 1 IN ARRAY matrix可以按行遍历二维数组，但日常用得少，多数场景一维遍历就足够了。需要留意的是，FOREACH的循环变量不需要声明类型，它自动从数组元素类型推断，但在DECLARE里显式声明同名变量也没什么问题。

3.5 EXIT与CONTINUE的精确控制

EXIT和CONTINUE是循环里最重要的两个控制词。EXIT负责跳出循环，CONTINUE负责跳过本轮剩余代码直接进入下一轮。两者都支持WHEN条件子句和循环标签。

FOR rec IN SELECT * FROM payments WHERE status = 'PENDING' LOOP CONTINUE WHEN rec.amount < 10; -- 金额小于10的跳过，这里处理正常金额 PERFORM settle_payment(rec.id); END LOOP;

CONTINUE WHEN的写法非常简洁，它等价于：

IF rec.amount < 10 THEN CONTINUE; END IF;

我在生产代码里更倾向于用CONTINUE WHEN，无关对错，纯粹因为代码行数少。但如果CONTINUE前还需要做点别的事，比如记录日志，那就得老老实实写IF块。

4. 异常处理：BEGIN...EXCEPTION结构

4.1 异常块的基本结构

PL/pgSQL里的异常捕获不是用try-catch语法，而是借助BEGIN...EXCEPTION块。外层BEGIN...END定义的是函数主体，内层却可以再嵌一个BEGIN...EXCEPTION来圈定保护范围：

CREATE OR REPLACE FUNCTION safe_divide(numerator NUMERIC, denominator NUMERIC) RETURNS NUMERIC LANGUAGE plpgsql AS $$ BEGIN RETURN numerator / denominator; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE '除数为0，返回NULL'; RETURN NULL; END; $$;

执行时，如果BEGIN块里的语句抛出了division_by_zero错误，控制流会立刻跳到EXCEPTION子句，找到匹配的WHEN分支处理。这个机制和很多语言的异常处理很像，区别在于：一旦进入EXCEPTION分支，BEGIN块里已经执行的未提交变更会被回滚——注意是整个BEGIN块的回滚，而不是部分。

嵌套异常块可以实现“部分保护”。比如一个循环里每条数据处理都独立加异常块，单条记录失败不会导致整个事务回滚：

FOR rec IN SELECT * FROM temp_import_data LOOP BEGIN INSERT INTO accounts(id, name, amount) VALUES (rec.id, rec.name, rec.amount); EXCEPTION WHEN unique_violation THEN RAISE WARNING '记录%重复，跳过', rec.id; END; END LOOP;

这段代码里，每条数据单独一个事务子块，遇到主键冲突就警告一条并继续。如果没有这个内层EXCEPTION，任何一条数据出错都会导致整个函数异常退出，前面所有成功插入的数据也跟着回滚。这个模式在数据导入、批量清洗任务里极其常用。

4.2 如何捕获特定异常并记录

PostgreSQL的异常条件可以用SQLSTATE码表示，也可以用预定义的名字，比如unique_violation、foreign_key_violation、division_by_zero、not_null_violation等。用名字写出来的代码可读性最好，但偶尔会需要捕获系统里没有预定义名字的SQLSTATE，此时可以直接用字符串码：

EXCEPTION WHEN SQLSTATE '23505' THEN RAISE NOTICE '唯一约束冲突';

23505是unique_violation对应的SQLSTATE码，如果你只记得数字码，按这个写也没问题。不过既然PostgreSQL已经给了语义化名字，日常使用还是优先用名字，代码更容易读。

想要获取错误的详细信息，可以用GET STACKED DIAGNOSTICS语句，它有点像其他语言里的异常对象属性访问：

DECLARE err_msg TEXT; err_detail TEXT; err_hint TEXT; BEGIN BEGIN PERFORM risky_operation(); EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL, err_hint = PG_EXCEPTION_HINT; RAISE NOTICE '错误信息：%', err_msg; RAISE NOTICE '详情：%', err_detail; RAISE NOTICE '提示：%', err_hint; END; END;

WHEN OTHERS会捕获所有未列出的异常，是兜底分支。生产环境建议在WHEN OTHERS分支里记录完整错误上下文，否则排查问题时会发现函数静默失败，无从下手。

4.3 异常处理中的回滚行为

理解回滚语义是正确使用异常块的关键。进入EXCEPTION分支后，当前BEGIN块内的所有未提交数据变更都会回滚到BEGIN之前的状态，但外层已经提交的操作不受影响。如果异常块外面还有已经完成的工作，这些工作不会跟着回滚，除非整个函数都在一个更大的事务里。

举个实际案例。某函数先更新订单状态为已处理，然后调用子过程更新库存，子过程里出现库存不足异常：

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER) RETURNS TEXT LANGUAGE plpgsql AS $$ BEGIN UPDATE orders SET status = 'PROCESSING' WHERE id = p_order_id; BEGIN PERFORM deduct_stock(p_order_id); EXCEPTION WHEN check_violation THEN -- 库存不足时，订单状态仍然被更新了，这里需要手动回滚 UPDATE orders SET status = 'FAILED' WHERE id = p_order_id; RETURN '库存不足'; END; UPDATE orders SET status = 'DONE' WHERE id = p_order_id; RETURN '成功'; END; $$;

这个例子里，内层EXCEPTION只会回滚deduct_stock引起的变更，订单状态更新已经发生在内层BEGIN之前，不在回滚范围内。如果业务上要求“订单状态也不能变”，那就得把订单更新放在和库存扣减同一个BEGIN块里，或者在内层发现异常后显式把状态更新回原值。这个设计决策没有标准答案，取决于业务的原子性边界。

5. RETURN系列与PERFORM的更多细节

5.1 RETURN在三种函数类型中的差异

RETURN语句的行为取决于函数的返回类型声明。最基础的是RETURNS VOID，此时RETURN不带任何参数，纯粹表示函数结束：

CREATE OR REPLACE FUNCTION log_operation(op_name TEXT) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN INSERT INTO operation_logs(name, created_at) VALUES (op_name, now()); RETURN; END; $$;

RETURNS标量类型（比如INTEGER、TEXT、NUMERIC）时，RETURN必须带一个值：

CREATE OR REPLACE FUNCTION get_order_count() RETURNS INTEGER LANGUAGE plpgsql AS $$ DECLARE cnt INTEGER; BEGIN SELECT COUNT(*) INTO cnt FROM orders; RETURN cnt; END; $$;

RETURNS TABLE或RETURNS SETOF时，返回值不是一次性返回，而是像流一样逐行产生。此时RETURN只是表示“函数结束”，不携带数据，数据是靠RETURN NEXT或RETURN QUERY累积的。

特别提醒：在RETURNS TABLE的函数里，如果直接写RETURN 1;这种语法，会报错。你只能在RETURNS标量类型的函数里给RETURN带值。

5.2 RETURN NEXT与RETURN QUERY

RETURN NEXT每次执行时往结果集里追加一行。它的语义是“挂着不退出”，函数可以继续执行后续逻辑，下次再追加下一行。这非常适合把循环里逐行计算的结果收集起来返回：

CREATE OR REPLACE FUNCTION generate_series_custom(start_val INTEGER, end_val INTEGER) RETURNS TABLE(n INTEGER, n_squared INTEGER) LANGUAGE plpgsql AS $$ DECLARE v INTEGER; BEGIN FOR v IN start_val..end_val LOOP n := v; n_squared := v * v; RETURN NEXT; END LOOP; RETURN; END; $$;

RETURN QUERY则是把一次查询的全部结果集追加进函数返回值，底层相当于把查询结果流式传递给调用方：

CREATE OR REPLACE FUNCTION get_recent_orders(limit_count INTEGER) RETURNS TABLE(id INTEGER, order_no TEXT, amount NUMERIC) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT o.id, o.order_no, o.amount FROM orders o ORDER BY o.created_at DESC LIMIT limit_count; -- 如果还需要追加别的数据，可以继续RETURN QUERY或RETURN NEXT END; $$;

RETURN QUERY EXECUTE可以执行动态SQL并返回结果行，这个结合动态条件拼装时很有用：

RETURN QUERY EXECUTE format( 'SELECT id, order_no FROM orders WHERE status = %L ORDER BY id LIMIT %L', p_status, p_limit );

5.3 PERFORM语句的用途

在PL/pgSQL函数里，如果只想执行一个SELECT查询但不接收结果，不能直接写SELECT expression;，因为PL/pgSQL不允许裸的SELECT语句出现在函数体中，必须配套INTO接收结果，或者使用PERFORM。

PERFORM count(*) FROM orders WHERE status = 'PAID';

PERFORM会执行后面的查询并丢弃结果，但函数仍然能感知到是否有行返回，这正好利用FOUND状态。一个常见的用法是先判断数据是否存在再决定下一步逻辑：

PERFORM 1 FROM accounts WHERE user_name = p_username LIMIT 1; IF FOUND THEN RAISE EXCEPTION '用户名%已被占用', p_username; END IF;

PERFORM 1是一个很取巧的写法——只判断是否存在对应行，不关心具体值。它等价于EXISTS子查询，但写起来更简洁。FOUND是一个全局状态变量，在上一条SQL语句执行后被自动更新。注意在IF结构内部判断FOUND没有问题，但如果中间夹了其他SQL语句，FOUND会被覆盖，批量循环场景里尤其要注意这一点。

6. 实战复盘：用结构控制写一个完整的订单分账存储过程

6.1 业务场景与初步设计

为了让上面的知识点落地，这里设计一个完整的实战案例。某电商平台要做订单分账：每个订单有金额、归属店铺、支付状态；月底需要把所有已支付订单按店铺聚合，计算平台抽成后生成分账记录，同时更新店铺统计表。

表结构简化为三张表：

CREATE TABLE orders ( id INTEGER PRIMARY KEY, shop_id INTEGER, amount NUMERIC(10,2), status TEXT CHECK (status IN ('PAID', 'PENDING', 'CANCELLED')) ); CREATE TABLE shops ( id INTEGER PRIMARY KEY, name TEXT, settled_amount NUMERIC(10,2) DEFAULT 0 ); CREATE TABLE settlement_logs ( id BIGSERIAL PRIMARY KEY, shop_id INTEGER, order_id INTEGER, settled_ratio NUMERIC(4,2), settled_value NUMERIC(10,2), created_at TIMESTAMP DEFAULT now() );

业务规则是：平台抽成比例按店铺当月总销售额区间浮动——销售额小于10000抽成5%，超过10000但不低于50000抽成8%，超过50000抽成10%。分摊比例需要逐订单计算并记录日志。

6.2 存储过程逐步构建

第一步：遍历所有已支付订单。支付状态是PAID。需要按店铺分组统计销售额，但比例计算需要整店月度销售额作为前置条件，所以先聚合，再逐单分账。

CREATE OR REPLACE FUNCTION monthly_settlement(target_month TEXT) RETURNS INTEGER LANGUAGE plpgsql AS $$ DECLARE r RECORD; shop_total NUMERIC(10,2); ratio NUMERIC(4,2); processed_count INTEGER := 0; BEGIN -- 遍历每个店铺 FOR r IN SELECT s.id AS shop_id, s.name AS shop_name, COALESCE(SUM(o.amount), 0) AS total_amount FROM shops s LEFT JOIN orders o ON o.shop_id = s.id AND o.status = 'PAID' AND to_char(o.created_at, 'YYYY-MM') = target_month GROUP BY s.id, s.name ORDER BY s.id LOOP -- 根据总销售额确定抽成比例 IF r.total_amount < 10000 THEN ratio := 0.05; ELSIF r.total_amount < 50000 THEN ratio := 0.08; ELSE ratio := 0.10; END IF; -- 更新店铺统计字段 UPDATE shops SET settled_amount = settled_amount + round(r.total_amount * ratio, 2) WHERE id = r.shop_id; processed_count := processed_count + 1; END LOOP; RETURN processed_count; END; $$;

这个版本已经用到了IF-ELSIF多分支结构、FOR查询结果集循环、聚合查询。但它还没做到“逐订单记录日志”，下面完善。

第二步：在循环内再嵌一层FOR循环，遍历该店铺的每笔已支付订单，为每笔订单生成分账日志：

CREATE OR REPLACE FUNCTION monthly_settlement_v2(target_month TEXT) RETURNS INTEGER LANGUAGE plpgsql AS $$ DECLARE r RECORD; order_rec RECORD; shop_total NUMERIC(10,2); ratio NUMERIC(4,2); processed_count INTEGER := 0; BEGIN FOR r IN SELECT s.id AS shop_id, s.name AS shop_name, COALESCE(SUM(o.amount), 0) AS total_amount FROM shops s LEFT JOIN orders o ON o.shop_id = s.id AND o.status = 'PAID' AND to_char(o.created_at, 'YYYY-MM') = target_month GROUP BY s.id, s.name ORDER BY s.id LOOP IF r.total_amount < 10000 THEN ratio := 0.05; ELSIF r.total_amount < 50000 THEN ratio := 0.08; ELSE ratio := 0.10; END IF; -- 内层循环：逐订单写分账日志 FOR order_rec IN SELECT o.id, o.amount FROM orders o WHERE o.shop_id = r.shop_id AND o.status = 'PAID' AND to_char(o.created_at, 'YYYY-MM') = target_month LOOP INSERT INTO settlement_logs( shop_id, order_id, settled_ratio, settled_value, created_at ) VALUES ( r.shop_id, order_rec.id, ratio, round(order_rec.amount * ratio, 2), now() ); END LOOP; UPDATE shops SET settled_amount = settled_amount + round(r.total_amount * ratio, 2) WHERE id = r.shop_id; processed_count := processed_count + 1; END LOOP; RETURN processed_count; END; $$;

这里嵌套了两层FOR循环，外层按店铺聚合，内层逐订单分账。内层循环里的INSERT语句每执行一条就是一个独立的SQL事务动作，如果中间某条记录插入失败，整个函数会直接异常退出。对数据导入类操作，建议给内层加异常块做部分容错。

6.3 加入异常保护和参数校验

生产环境直接跑批处理，不可能假设数据都是完美的。完善的方向有三个：参数合法性校验、单条失败的容错、重复日期的幂等处理。

CREATE OR REPLACE FUNCTION monthly_settlement_v3(p_month TEXT) RETURNS INTEGER LANGUAGE plpgsql AS $$ DECLARE r RECORD; order_rec RECORD; shop_total NUMERIC(10,2); ratio NUMERIC(4,2); processed_count INTEGER := 0; skip_count INTEGER := 0; BEGIN -- 参数格式校验 IF p_month !~ '^\d{4}-\d{2}$' THEN RAISE EXCEPTION '月份格式必须为YYYY-MM，当前值：%', p_month; END IF; -- 幂等控制：同月重复执行时先清掉旧日志 DELETE FROM settlement_logs WHERE to_char(created_at, 'YYYY-MM') = p_month; FOR r IN SELECT s.id AS shop_id, s.name AS shop_name, COALESCE(SUM(o.amount), 0) AS total_amount FROM shops s LEFT JOIN orders o ON o.shop_id = s.id AND o.status = 'PAID' AND to_char(o.created_at, 'YYYY-MM') = p_month GROUP BY s.id, s.name ORDER BY s.id LOOP IF r.total_amount < 10000 THEN ratio := 0.05; ELSIF r.total_amount < 50000 THEN ratio := 0.08; ELSE ratio := 0.10; END IF; FOR order_rec IN SELECT o.id, o.amount FROM orders o WHERE o.shop_id = r.shop_id AND o.status = 'PAID' AND to_char(o.created_at, 'YYYY-MM') = p_month LOOP BEGIN INSERT INTO settlement_logs( shop_id, order_id, settled_ratio, settled_value, created_at ) VALUES ( r.shop_id, order_rec.id, ratio, round(order_rec.amount * ratio, 2), now() ); EXCEPTION WHEN OTHERS THEN skip_count := skip_count + 1; RAISE WARNING '订单%分账写入失败，跳过，错误信息：%', order_rec.id, SQLERRM; END; END LOOP; UPDATE shops SET settled_amount = settled_amount + round(r.total_amount * ratio, 2) WHERE id = r.shop_id; processed_count := processed_count + 1; END LOOP; RAISE NOTICE '店铺处理数：%，跳过异常单数：%', processed_count, skip_count; RETURN processed_count; END; $$;

这个版本已经具备生产可用性。参数校验用正则表达式匹配月份格式；幂等控制用DELETE清掉同月份的旧日志；内层异常捕获保证单条数据失败不影响整批；RAISE NOTICE输出统计信息方便任务排错。执行结果验证：

SELECT monthly_settlement_v3('2025-01');

如果orders和shops有测试数据，能看到NOTICE输出处理数量和跳过数量，settlement_logs表里按月和店铺记录了每条订单的分账明细。

7. 常见问题与排错实录

7.1 必踩的语法和逻辑坑

我把高频问题做成速查表，方便对照排查：

7.2 循环内变量遮蔽问题

FOR循环自动声明循环变量，如果你在DECLARE里也声明了同名变量，循环体里的变量名指向循环自动创建的那个，外层同名变量在循环内不可见。等循环结束，外层变量仍然是进入循环前的值。这个坑很容易造成困惑，建议循环变量用统一的前缀，比如_i、rec_xxx，避免和外层业务变量重名。

7.3 异常捕获后的排查建议

RAISE NOTICE只是往服务端日志和客户端输出信息。批量跑批场景里，如果函数中途吞掉了大量异常，最好把异常详情记录到一张日志表，而不只是打NOTICE。下面这种简单结构就能实现：

CREATE TABLE IF NOT EXISTS process_error_logs ( id BIGSERIAL PRIMARY KEY, process_name TEXT, error_key TEXT, error_message TEXT, occurred_at TIMESTAMP DEFAULT now() );

在EXCEPTION分支里插入一条记录，再RAISE WARNING提示，既不会打断批处理，也保留了完整的事故现场。

7.4 关于性能和锁的实战提醒

结构控制在功能性上没问题，但要考虑性能。每行数据都执行INSERT、UPDATE这类事务语句时，系统开销会随行数线性增长，且每写一行都需要短期行锁。涉及大批量数据时，应该考虑先用一条INSERT...SELECT批量生成分账日志，再按店铺做一次聚合UPDATE，而不是嵌套循环逐行操作。这一点在存储过程设计之初就要想清楚，而不是写完再回头优化。

循环里如果还涉及跨表的复杂逻辑，尤其要注意锁顺序，避免死锁。保持多个函数之间访问表的顺序一致，是减少死锁的有效手段。

说到底，PL/pgSQL里的结构控制并不复杂，把IF、CASE、LOOP、WHILE、FOR、FOREACH、EXCEPTION这几样练熟，就能应付绝大多数存储过程需求。真正决定代码质量的是对每个特性的边界行为有清醒的认识——NULL怎么传播、EXIT什么时候检查条件、异常块的回滚范围到哪里。把这些边界摸清了，写出来的函数不管是可读性还是稳定性，都会上一个台阶。下一件事，就是用前面提到的批量改写思路，把你手里逐行处理的循环换成集合操作，性能和代码都能更上一层楼。