Skip to content

存储过程与函数

中等 约 40 分钟

你将构建什么

一组银行应用的存储过程 —— 用事务转账、计算账户余额、记录转账日志。

学习目标

  • 编写带参数的存储过程
  • 在过程中使用事务控制
  • 创建用户自定义函数
  • 用 RAISE EXCEPTION 处理错误

前置要求

  • INSERT、UPDATE、DELETE
  • 事务基础(BEGIN/COMMIT)
  • 函数和过程的概念

步骤

  1. 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. 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. 3

    调用过程

    调用 transfer_funds 过程将 200 从 Alice 转给 Bob,然后验证余额。

  4. 4

    创建余额函数

    编写一个用户自定义函数,返回指定账户 ID 的当前余额。函数可在 SELECT 语句中使用。

  5. 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;
    $$;

    预期结果

    超过余额的转账现在会抛出异常,而不是变成负数。

自己动手试试

你学到了什么

你编写了存储过程和函数 —— 将业务逻辑封装在数据库中,具备事务安全性、参数验证和错误处理。

相关资源

相关课程

相关术语

相关术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误

← 返回主题