存储过程与函数
中等 约 40 分钟
你将构建什么
一组银行应用的存储过程 —— 用事务转账、计算账户余额、记录转账日志。
学习目标
- 编写带参数的存储过程
- 在过程中使用事务控制
- 创建用户自定义函数
- 用 RAISE EXCEPTION 处理错误
前置要求
- INSERT、UPDATE、DELETE
- 事务基础(BEGIN/COMMIT)
- 函数和过程的概念
步骤
- 1
建立银行表
为简单银行系统创建 accounts 和 transfers 表。
代码CREATE TABLE accounts ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, balance NUMERIC(12,2) NOT NULL DEFAULT 0 ); CREATE TABLE transfers ( id SERIAL PRIMARY KEY, from_acct INTEGER REFERENCES accounts(id), to_acct INTEGER REFERENCES accounts(id), amount NUMERIC(12,2) NOT NULL, created_at TIMESTAMP DEFAULT NOW() ); INSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00), ('Bob', 500.00);预期结果
创建两个账户:Alice 有 1000,Bob 有 500。
- 2
编写转账过程
创建一个存储过程,原子性地在账户间转账 —— 借记一方、贷记另一方、记录转账,全部在一个事务中。
代码CREATE OR REPLACE PROCEDURE transfer_funds( p_from INTEGER, p_to INTEGER, p_amount NUMERIC(12,2) ) LANGUAGE plpgsql AS $$ BEGIN IF p_amount <= 0 THEN RAISE EXCEPTION 'Amount must be positive'; END IF; UPDATE accounts SET balance = balance - p_amount WHERE id = p_from; UPDATE accounts SET balance = balance + p_amount WHERE id = p_to; INSERT INTO transfers (from_acct, to_acct, amount) VALUES (p_from, p_to, p_amount); COMMIT; END; $$;预期结果
过程已创建,可用于原子性转账。
- 3
调用过程
调用 transfer_funds 过程将 200 从 Alice 转给 Bob,然后验证余额。
提示
使用 CALL transfer_funds(1, 2, 200.00); 然后 SELECT * FROM accounts;
- 4
创建余额函数
编写一个用户自定义函数,返回指定账户 ID 的当前余额。函数可在 SELECT 语句中使用。
提示
CREATE FUNCTION get_balance(p_id INTEGER) RETURNS NUMERIC AS $$ ... $$ LANGUAGE sql;
- 5
添加透支保护
修改转账过程,在转账前检查是否有足够资金。如果余额不足则抛出异常。
代码CREATE OR REPLACE PROCEDURE transfer_funds( p_from INTEGER, p_to INTEGER, p_amount NUMERIC(12,2) ) LANGUAGE plpgsql AS $$ DECLARE v_balance NUMERIC(12,2); BEGIN SELECT balance INTO v_balance FROM accounts WHERE id = p_from; IF v_balance < p_amount THEN RAISE EXCEPTION 'Insufficient funds: have %, need %', v_balance, p_amount; END IF; UPDATE accounts SET balance = balance - p_amount WHERE id = p_from; UPDATE accounts SET balance = balance + p_amount WHERE id = p_to; INSERT INTO transfers (from_acct, to_acct, amount) VALUES (p_from, p_to, p_amount); END; $$;预期结果
超过余额的转账现在会抛出异常,而不是变成负数。
自己动手试试
起始代码
CREATE OR REPLACE PROCEDURE transfer_funds(p_from INTEGER, p_to INTEGER, p_amount NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
-- TODO: implement transfer
END;
$$;解决方案代码
CREATE OR REPLACE PROCEDURE transfer_funds(p_from INTEGER, p_to INTEGER, p_amount NUMERIC(12,2))
LANGUAGE plpgsql AS $$
BEGIN
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
INSERT INTO transfers (from_acct, to_acct, amount) VALUES (p_from, p_to, p_amount);
END;
$$;你学到了什么
你编写了存储过程和函数 —— 将业务逻辑封装在数据库中,具备事务安全性、参数验证和错误处理。
相关资源
相关术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。