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 的异常。
按自己的节奏,确认真正理解。