PL/pgSQL 入门教程（三）：控制结构-阿里云开发者社区

一、先搞懂：怎么从函数返回结果？

写函数的最终目的是返回数据，PL/pgSQL 提供了两套返回机制，先把这个基础打牢。

1.1 单值返回：RETURN

如果你的函数只返回一个值（比如数字、字符串、单行数据），用 RETURN 表达式; 就行，执行到这个语句时函数会直接结束，并把表达式的值返回给调用者。

常见用法示例

-- 1. 返回简单计算结果（标量类型）
CREATE OR REPLACE FUNCTION get_sum(a int, b int) RETURNS int AS $$
BEGIN
    RETURN a + b; -- 直接返回表达式结果
END;
$$ LANGUAGE plpgsql;

-- 调用：SELECT get_sum(3,5); → 输出 8

-- 2. 返回单行数据（复合类型）
CREATE TABLE user_info (id int, name text, age int);
INSERT INTO user_info VALUES (1, '张三', 28);

CREATE OR REPLACE FUNCTION get_user(uid int) RETURNS user_info AS $$
DECLARE
    u user_info%rowtype; -- 声明和表结构一致的变量
BEGIN
    SELECT * INTO u FROM user_info WHERE id = uid;
    RETURN u; -- 返回整行数据
END;
$$ LANGUAGE plpgsql;

-- 调用：SELECT * FROM get_user(1); → 输出 (1,张三,28)

关键注意点

如果函数声明返回 void（无返回值），用 RETURN; 即可，后面不能加表达式；
函数必须执行到 RETURN 语句（除非是返回 void 或带输出参数的函数），否则会报错。

1.2 批量返回：RETURN NEXT / RETURN QUERY

如果想让函数返回多行数据（比如一个表的结果），就需要用这两个命令，它们的核心是“追加数据到结果集”，不会立即结束函数。

核心用法

RETURN NEXT：逐个追加单行数据；
RETURN QUERY：直接追加整个查询的结果；
最后必须加一个无参数的 RETURN; 表示函数结束。

示例 1：RETURN NEXT 遍历返回

-- 返回所有年龄大于20的用户
CREATE OR REPLACE FUNCTION get_adult_users() RETURNS SETOF user_info AS $$
DECLARE
    u user_info%rowtype;
BEGIN
    FOR u IN SELECT * FROM user_info WHERE age > 20 LOOP
        -- 可以在这里加业务逻辑，比如修改数据
        u.age = u.age + 1; -- 模拟：返回时年龄+1
        RETURN NEXT u; -- 把当前行加入结果集
    END LOOP;
    RETURN; -- 标记函数结束
END;
$$ LANGUAGE plpgsql;

示例 2：RETURN QUERY 直接返回查询结果

-- 返回指定日期之后的订单（更简洁）
CREATE TABLE orders (id int, order_date date, amount numeric);
CREATE OR REPLACE FUNCTION get_orders_after(d date) RETURNS SETOF orders AS $$
BEGIN
    -- 直接把查询结果追加到返回集
    RETURN QUERY SELECT * FROM orders WHERE order_date > d;
    
    -- 还能追加其他数据
    RETURN QUERY SELECT 999, CURRENT_DATE, 0.0; -- 追加一条“兜底”数据
    
    RETURN;
END;
$$ LANGUAGE plpgsql;

小提醒

RETURN NEXT 和 RETURN QUERY 可以混用，结果会按执行顺序拼接；
大量数据返回时，结果会先存在内存里，数据特别大时要注意调整 work_mem 参数。

1.3 过程（Procedure）的返回逻辑

过程和函数不同，它没有返回值，不需要 RETURN 语句也能结束；如果想提前退出，只用写 RETURN;（后面不能加表达式）。

如果过程声明了输出参数，参数的最终值会返回给调用者：

-- 过程：把输入的数字乘以3
CREATE PROCEDURE triple_num(INOUT x int) AS $$
BEGIN
    x := x * 3;
END;
$$ LANGUAGE plpgsql;

-- 调用过程
DO $$
DECLARE
    num int := 5;
BEGIN
    CALL triple_num(num);
    RAISE NOTICE '结果：%', num; -- 输出 15
END;
$$;

二、条件判断：让函数“做选择”

就像生活中“如果下雨就带伞，否则出门”，PL/pgSQL 用 IF 和 CASE 实现条件逻辑，是最常用的控制结构。

2.1 IF 系列：最灵活的条件判断

PL/pgSQL 提供三种 IF 形式，覆盖所有条件场景。

形式1：简单 IF（满足条件才执行）

-- 语法
IF 布尔表达式 THEN
    -- 满足条件时执行的语句
END IF;

-- 示例：如果用户年龄小于18，标记为未成年人
CREATE OR REPLACE FUNCTION check_minor(uid int) RETURNS text AS $$
DECLARE
    u_age int;
BEGIN
    SELECT age INTO u_age FROM user_info WHERE id = uid;
    IF u_age < 18 THEN
        RETURN '未成年人';
    END IF;
    RETURN '成年人';
END;
$$ LANGUAGE plpgsql;

形式2：IF-ELSE（二选一）

-- 语法
IF 布尔表达式 THEN
    -- 条件为真执行
ELSE
    -- 条件为假执行
END IF;

-- 示例：判断数字正负
CREATE OR REPLACE FUNCTION judge_num(n int) RETURNS text AS $$
BEGIN
    IF n > 0 THEN
        RETURN '正数';
    ELSE
        RETURN '非正数'; -- 包含0和负数
    END IF;
END;
$$ LANGUAGE plpgsql;

形式3：IF-ELSIF-ELSE（多分支）

适合多个条件判断，按顺序检查，找到第一个满足的条件就执行：

-- 语法
IF 条件1 THEN
    -- 执行逻辑1
ELSIF 条件2 THEN
    -- 执行逻辑2
ELSIF 条件3 THEN
    -- 执行逻辑3
ELSE
    -- 以上都不满足时执行
END IF;

-- 示例：成绩评级
CREATE OR REPLACE FUNCTION grade_score(score int) RETURNS text AS $$
BEGIN
    IF score >= 90 THEN
        RETURN '优秀';
    ELSIF score >= 80 THEN
        RETURN '良好';
    ELSIF score >= 60 THEN
        RETURN '及格';
    ELSE
        RETURN '不及格';
    END IF;
END;
$$ LANGUAGE plpgsql;

2.2 CASE 系列：更简洁的多分支

CASE 分两种形式，适合“值匹配”或“条件匹配”的场景，比多层 IF 更简洁。

形式1：简单 CASE（值相等匹配）

适合判断一个变量等于某个/某些值的场景：

-- 语法
CASE 变量名
    WHEN 值1 [, 值2] THEN
        -- 变量等于值1/值2时执行
    WHEN 值3 THEN
        -- 变量等于值3时执行
    ELSE
        -- 都不匹配时执行
END CASE;

-- 示例：判断星期几
CREATE OR REPLACE FUNCTION get_weekday(n int) RETURNS text AS $$
DECLARE
    res text;
BEGIN
    CASE n
        WHEN 1 THEN res := '周一';
        WHEN 2,3,4,5 THEN res := '工作日';
        WHEN 6,7 THEN res := '周末';
        ELSE res := '无效数字';
    END CASE;
    RETURN res;
END;
$$ LANGUAGE plpgsql;

形式2：搜索式 CASE（条件匹配）

和 IF-ELSIF 功能几乎一样，只是写法不同：

-- 语法
CASE
    WHEN 条件1 THEN
        -- 执行逻辑1
    WHEN 条件2 THEN
        -- 执行逻辑2
    ELSE
        -- 都不满足时执行
END CASE;

-- 示例：和上面的成绩评级等价
CREATE OR REPLACE FUNCTION grade_score_case(score int) RETURNS text AS $$
DECLARE
    res text;
BEGIN
    CASE
        WHEN score >= 90 THEN res := '优秀';
        WHEN score >= 80 THEN res := '良好';
        WHEN score >= 60 THEN res := '及格';
        ELSE res := '不及格';
    END CASE;
    RETURN res;
END;
$$ LANGUAGE plpgsql;

IF vs CASE 怎么选？

简单二选一：用 IF-ELSE；
多分支且是“值匹配”：用简单 CASE；
多分支且是“复杂条件”：IF-ELSIF 或搜索式 CASE 都可以，看个人习惯；
注意：CASE 如果没有 ELSE 且无匹配项，会报错；而 IF-ELSIF 没有 ELSE 则什么都不执行。

三、循环执行：让函数“重复做事”

如果需要重复执行一段逻辑（比如遍历表的所有行、计算1到100的和），就需要用到循环。PL/pgSQL 提供了多种循环方式，按需选择。

3.1 LOOP：无条件循环（最灵活）

LOOP 是最基础的循环，会无限执行，必须用 EXIT 语句终止，适合“不确定循环次数”的场景。

基础语法

[ <<循环标签>> ] -- 可选，嵌套循环时用
LOOP
    -- 循环体逻辑
    EXIT [循环标签] [WHEN 条件]; -- 终止循环
    -- 可选：CONTINUE [WHEN 条件]; -- 跳过当前迭代剩余逻辑
END LOOP [循环标签];

示例：计算1到10的和

CREATE OR REPLACE FUNCTION sum_1_to_10() RETURNS int AS $$
DECLARE
    total int := 0;
    i int := 1;
BEGIN
    LOOP
        total := total + i;
        i := i + 1;
        EXIT WHEN i > 10; -- 条件满足时退出循环
    END LOOP;
    RETURN total; -- 结果是55
END;
$$ LANGUAGE plpgsql;

进阶：CONTINUE 跳过迭代

-- 计算1到10中偶数的和
CREATE OR REPLACE FUNCTION sum_even() RETURNS int AS $$
DECLARE
    total int := 0;
    i int := 0;
BEGIN
    LOOP
        i := i + 1;
        EXIT WHEN i > 10;
        CONTINUE WHEN i % 2 != 0; -- 不是偶数就跳过后续逻辑
        total := total + i;
    END LOOP;
    RETURN total; -- 结果是30
END;
$$ LANGUAGE plpgsql;

3.2 WHILE：条件满足才循环

适合“先判断条件，再执行循环”的场景，条件为真时执行循环体，为假时退出。

语法 + 示例

-- 语法
WHILE 布尔表达式 LOOP
    -- 循环体
END LOOP;

-- 示例：和上面sum_1_to_10等价
CREATE OR REPLACE FUNCTION sum_1_to_10_while() RETURNS int AS $$
DECLARE
    total int := 0;
    i int := 1;
BEGIN
    WHILE i <= 10 LOOP
        total := total + i;
        i := i + 1;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

3.3 FOR：固定次数/遍历查询结果

FOR 循环分两种场景，是日常用得最多的循环方式。

场景1：遍历整数范围

适合“知道循环次数”的场景，变量会自动声明，无需手动定义。

-- 语法
FOR 变量名 IN [REVERSE] 起始值 .. 结束值 [BY 步长] LOOP
    -- 循环体
END LOOP;

-- 示例1：正向遍历（1到10，步长1）
CREATE OR REPLACE FUNCTION for_int_demo() RETURNS void AS $$
BEGIN
    FOR i IN 1..10 LOOP
        RAISE NOTICE '当前数字：%', i;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 示例2：反向遍历（10到1，步长2）
CREATE OR REPLACE FUNCTION for_int_reverse() RETURNS void AS $$
BEGIN
    FOR i IN REVERSE 10..1 BY 2 LOOP
        RAISE NOTICE '当前数字：%', i; -- 输出10,8,6,4,2
    END LOOP;
END;
$$ LANGUAGE plpgsql;

场景2：遍历查询结果

这是最实用的场景！可以遍历 SELECT 语句的结果，逐行处理数据。

-- 语法
FOR 行变量 IN 查询语句 LOOP
    -- 处理每一行数据
END LOOP;

-- 示例：遍历所有用户，输出用户名
CREATE OR REPLACE FUNCTION print_all_users() RETURNS void AS $$
DECLARE
    u user_info%rowtype; -- 行变量，和表结构一致
BEGIN
    FOR u IN SELECT * FROM user_info LOOP
        RAISE NOTICE '用户名：%，年龄：%', u.name, u.age;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 进阶：动态查询遍历（用EXECUTE）
CREATE OR REPLACE FUNCTION print_users_by_age(min_age int) RETURNS void AS $$
DECLARE
    u user_info%rowtype;
BEGIN
    -- 动态拼接查询语句，USING传参数（防止SQL注入）
    FOR u IN EXECUTE 'SELECT * FROM user_info WHERE age >= $1' USING min_age LOOP
        RAISE NOTICE '符合条件的用户：%', u.name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

3.4 FOREACH：遍历数组

专门用来遍历数组的循环，支持遍历单个元素或“切片”（多维数组的行）。

场景1：遍历单个元素

-- 语法
FOREACH 元素变量 IN ARRAY 数组变量 LOOP
    -- 处理单个元素
END LOOP;

-- 示例：计算数组元素的和
CREATE OR REPLACE FUNCTION sum_array(arr int[]) RETURNS int8 AS $$
DECLARE
    total int8 := 0;
    x int;
BEGIN
    FOREACH x IN ARRAY arr LOOP
        total := total + x;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- 调用：SELECT sum_array(ARRAY[1,2,3,4,5]); → 输出15

场景2：遍历数组切片（多维数组）

-- 示例：遍历二维数组的每一行
CREATE OR REPLACE FUNCTION scan_2d_array(arr int[]) RETURNS void AS $$
DECLARE
    row_arr int[];
BEGIN
    FOREACH row_arr SLICE 1 IN ARRAY arr LOOP
        RAISE NOTICE '当前行：%', row_arr;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用：SELECT scan_2d_array(ARRAY[[1,2],[3,4],[5,6]]);
-- 输出：
-- NOTICE:  当前行：{1,2}
-- NOTICE:  当前行：{3,4}
-- NOTICE:  当前行：{5,6}

四、错误处理：让函数更健壮

默认情况下，函数里只要出现错误（比如除0、主键冲突），整个函数会终止，事务也会回滚。但我们可以用 EXCEPTION 捕获错误，让函数“优雅处理异常”。

4.1 基础语法

[ <<块标签>> ]
BEGIN
    -- 可能出错的逻辑
EXCEPTION
    WHEN 错误类型1 [OR 错误类型2] THEN
        -- 处理错误的逻辑
    WHEN OTHERS THEN
        -- 处理所有其他错误（兜底）
END;

4.2 核心示例：处理主键冲突

最常见的场景：插入数据时，如果主键已存在，就更新数据。

-- 先创建测试表
CREATE TABLE product (id int PRIMARY KEY, name text, price numeric);

-- 函数：插入/更新产品（避免主键冲突）
CREATE OR REPLACE FUNCTION upsert_product(p_id int, p_name text, p_price numeric) RETURNS void AS $$
BEGIN
    -- 先尝试更新
    UPDATE product SET name = p_name, price = p_price WHERE id = p_id;
    IF FOUND THEN -- FOUND是内置变量，判断上一条SQL是否影响行
        RETURN;
    END IF;
    
    -- 更新失败（数据不存在），尝试插入
    BEGIN
        INSERT INTO product(id, name, price) VALUES (p_id, p_name, p_price);
    EXCEPTION WHEN unique_violation THEN -- 捕获主键冲突错误
        -- 并发插入时可能触发，重新更新即可
        UPDATE product SET name = p_name, price = p_price WHERE id = p_id;
    END;
END;
$$ LANGUAGE plpgsql;

4.3 获取错误详情

如果想知道具体是什么错误，可以用 SQLSTATE/SQLERRM 或 GET STACKED DIAGNOSTICS 获取详细信息。

CREATE OR REPLACE FUNCTION test_error_handle() RETURNS text AS $$
DECLARE
    err_msg text;
    err_detail text;
BEGIN
    -- 故意触发除0错误
    PERFORM 1/0;
EXCEPTION WHEN OTHERS THEN
    -- 方式1：简单获取错误信息
    -- RAISE NOTICE '错误码：%，错误信息：%', SQLSTATE, SQLERRM;
    
    -- 方式2：获取更详细的信息
    GET STACKED DIAGNOSTICS 
        err_msg = MESSAGE_TEXT,
        err_detail = PG_EXCEPTION_DETAIL;
    RETURN '出错了：' || err_msg || '，详情：' || err_detail;
END;
$$ LANGUAGE plpgsql;

4.4 重要注意事项

错误块里的数据库修改会回滚：比如错误块内执行了 UPDATE，出错后这行 UPDATE 会撤销；
错误块的性能开销较高：非必要不要用，比如能提前判断的条件（比如除数是否为0），就先判断再执行；
OTHERS 会匹配几乎所有错误，建议最后用，且最好在里面记录错误日志。

五、实战小案例：综合运用控制结构

我们来写一个综合案例，把上面的知识点串起来：实现一个“用户积分结算”函数，功能包括：

遍历指定用户的所有订单；
按订单金额计算积分（1元=1积分，满1000元额外加50）；
累计积分后更新用户积分表；
处理可能的错误（比如用户不存在）。

-- 先创建所需表
CREATE TABLE user_score (user_id int PRIMARY KEY, total_score int DEFAULT 0);
CREATE TABLE user_order (id int, user_id int, amount numeric, create_time date);
INSERT INTO user_order VALUES (1, 1, 200, '2024-01-01'), (2, 1, 900, '2024-01-02'), (3, 1, 1500, '2024-01-03');

-- 积分结算函数
CREATE OR REPLACE FUNCTION settle_score(uid int) RETURNS int AS $$
DECLARE
    o record; -- 订单行变量
    add_score int := 0; -- 新增积分
    current_score int := 0; -- 当前总积分
BEGIN
    -- 先检查用户是否存在
    SELECT total_score INTO current_score FROM user_score WHERE user_id = uid;
    IF NOT FOUND THEN
        -- 用户不存在，初始化积分
        INSERT INTO user_score(user_id, total_score) VALUES (uid, 0);
        current_score := 0;
    END IF;

    -- 遍历用户的所有订单，计算新增积分
    FOR o IN SELECT * FROM user_order WHERE user_id = uid LOOP
        -- 基础积分：金额转整数
        add_score := add_score + o.amount::int;
        -- 满1000额外加50
        IF o.amount >= 1000 THEN
            add_score := add_score + 50;
        END IF;
    END LOOP;

    -- 更新用户积分
    BEGIN
        UPDATE user_score SET total_score = current_score + add_score WHERE user_id = uid;
        RETURN current_score + add_score;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE '更新积分失败：%', SQLERRM;
        RETURN -1; -- 返回-1表示失败
    END;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT settle_score(1);
-- 计算过程：200+900+1500=2600，1500满1000加50 → 总新增2650
-- 输出：2650（如果是首次执行，初始积分0，最终就是2650）

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

PL/pgSQL 入门教程（三）：控制结构