请求编写PLSQL转账存储过程：同表账户间资金划转

需求概述 #

编写名为trans_sp的PLSQL存储过程，实现同一账户表内两个账户间的资金划转，需包含以下IN参数：

• from_account_number：转出账户编号
• To_account_number：转入账户编号
• Credit_amount：转账金额

测试数据（假设表名为accounts） #

acc_num        acc_bal
12345          50000
67890          40000

调用示例 #

EXEC trans_sp(12345, 67890, 10000);

预期执行结果 #

acc_num   acc_bal
12345    40000
67890    50000

注：原需求中预期转入账户余额为60000，应为笔误，按转账逻辑40000+10000=50000

存储过程实现 #

CREATE OR REPLACE PROCEDURE trans_sp(
    p_from_account IN NUMBER,
    p_to_account IN NUMBER,
    p_credit_amount IN NUMBER
) AS
    v_from_balance NUMBER;
    -- 自定义异常
    e_insufficient_balance EXCEPTION;
    e_to_account_missing EXCEPTION;
BEGIN
    -- 设置保存点，用于异常回滚
    SAVEPOINT transfer_savepoint;

    -- 锁定转出账户并查询余额，防止并发修改
    SELECT acc_bal
    INTO v_from_balance
    FROM accounts
    WHERE acc_num = p_from_account
    FOR UPDATE;

    -- 检查余额是否充足
    IF v_from_balance < p_credit_amount THEN
        RAISE e_insufficient_balance;
    END IF;

    -- 转出账户扣减金额
    UPDATE accounts
    SET acc_bal = acc_bal - p_credit_amount
    WHERE acc_num = p_from_account;

    -- 转入账户增加金额
    UPDATE accounts
    SET acc_bal = acc_bal + p_credit_amount
    WHERE acc_num = p_to_account;

    -- 验证转入账户是否存在（更新行数为0则抛出异常）
    IF SQL%ROWCOUNT = 0 THEN
        RAISE e_to_account_missing;
    END IF;

    -- 提交事务
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('转账操作完成');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK TO transfer_savepoint;
        DBMS_OUTPUT.PUT_LINE('错误：转出账户不存在');
    WHEN e_insufficient_balance THEN
        ROLLBACK TO transfer_savepoint;
        DBMS_OUTPUT.PUT_LINE('错误：转出账户余额不足');
    WHEN e_to_account_missing THEN
        ROLLBACK TO transfer_savepoint;
        DBMS_OUTPUT.PUT_LINE('错误：转入账户不存在');
    WHEN OTHERS THEN
        ROLLBACK TO transfer_savepoint;
        DBMS_OUTPUT.PUT_LINE('转账失败：' || SQLERRM);
END;
/

关键说明 #

• 使用FOR UPDATE对转出账户加行锁，避免并发场景下的余额数据不一致
• 通过事务和保存点保证转账的原子性，任一环节出错都会回滚所有操作
• 针对账户不存在、余额不足等常见异常做了捕获和明确提示

内容的提问来源于stack exchange，提问作者Shahul Hameed