PostgreSQL / 系统课时

认识 PL/pgSQL 函数

认识 PL/pgSQL 函数

本课解决“普通 SQL 难以表达多步判断”的问题。你将创建一个可直接调用的 PL/pgSQL 函数,用变量保存中间状态,用条件分支决定折扣,并在参数非法时主动抛出异常。

学习目标

  • 理解 PL/pgSQL 函数的基本结构与执行过程
  • 使用变量、条件分支和返回值组织过程化逻辑
  • 为非法输入添加可识别的异常处理

PL/pgSQL 函数解决什么问题

SQL 擅长集合查询,但当一次数据库操作需要声明变量、执行多步判断、循环处理或捕获异常时,单条 SQL 往往不够清晰。PL/pgSQL 是 PostgreSQL 内置的过程化语言,可以把这些步骤封装为数据库函数。

函数在数据库服务器内部执行,调用方只需传入参数并接收结果。适合封装需要原子执行、会被多个调用方复用且主要依赖数据库数据的逻辑,不适合直接承担远程接口调用、页面流程或复杂业务编排。

函数的基本结构

一个 PL/pgSQL 函数通常由参数、返回类型、语言声明和函数体组成。函数体中的 DECLARE 用于定义局部变量,BEGIN 与 END 包围可执行语句,RETURN 返回结果,EXCEPTION 处理运行期间的异常。

参数名建议带有 p_ 前缀,局部变量可使用 v_ 前缀。它们不是语法要求,但能降低参数、列名和变量同名时的歧义。

创建并调用函数

下面的脚本创建一个应付金额计算函数。普通会员不打折,银卡会员打九五折,金卡会员打九折;金额小于等于零时抛出参数异常。

sql
CREATE OR REPLACE FUNCTION calc_payable_amount(
    p_amount numeric,
    p_member_level text
)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
    -- 局部变量保存本次计算使用的折扣率
    v_discount_rate numeric := 0;
BEGIN
    IF p_amount IS NULL OR p_amount <= 0 THEN
        RAISE EXCEPTION USING
            ERRCODE = '22023',
            MESSAGE = 'amount must be greater than 0';
    END IF;

    IF lower(p_member_level) = 'gold' THEN
        v_discount_rate := 0.10;
    ELSIF lower(p_member_level) = 'silver' THEN
        v_discount_rate := 0.05;
    END IF;

    -- numeric 计算后统一保留两位小数
    RETURN round(p_amount * (1 - v_discount_rate), 2);
EXCEPTION
    WHEN invalid_parameter_value THEN
        RAISE;
    WHEN others THEN
        RAISE EXCEPTION USING
            ERRCODE = 'P0001',
            MESSAGE = 'failed to calculate payable amount: ' || SQLERRM;
END;
$$;

SELECT calc_payable_amount(1000, 'gold') AS payable_amount;

预期结果

text
 payable_amount
----------------
         900.00
(1 row)

实际列宽可能因客户端不同而变化,但返回值应为 900.00。

异常与边界

调用 SELECT calc_payable_amount(0, 'gold'); 时,函数会返回 SQLSTATE 22023,并提示金额必须大于零。调用方可以据此区分参数错误与其他数据库错误。

EXCEPTION 块会增加控制复杂度,不应把所有错误都吞掉并返回默认值。能够交给约束处理的问题优先使用约束;需要向调用方明确说明失败原因时,再抛出带稳定 SQLSTATE 的异常。

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

目录与阅读设置

阅读设置

17

记录一个想法

认识 PL/pgSQL 函数

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

登录哪都通