存储过程示例

创建数据库和表

首先，我们需要创建一个数据库和几个相关的表来存储账户信息和交易记录。

CREATE DATABASE BankDB;
USE BankDB;

-- 创建 Accounts 表
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY IDENTITY,
    AccountHolderName NVARCHAR(100),
    Balance DECIMAL(18, 2) DEFAULT 0.00
);

-- 创建 Transactions 表
CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY IDENTITY,
    AccountID INT,
    TransactionType CHAR(1), -- 'D' for Deposit, 'W' for Withdrawal
    Amount DECIMAL(18, 2),
    TransactionDate DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID)
);

创建存储过程

接下来，我们将创建四个存储过程来处理不同的操作：

1. CreateAccount：创建一个新的银行账户。
2. Deposit：向账户存入资金。
3. Withdraw：从账户取出资金。
4. GetBalance：查询账户的当前余额。

1. CreateAccount 存储过程

CREATE PROCEDURE CreateAccount
    @AccountHolderName NVARCHAR(100),
    @InitialBalance DECIMAL(18, 2) = 0.00
AS
BEGIN
    BEGIN TRANSACTION;

    INSERT INTO Accounts (AccountHolderName, Balance)
    VALUES (@AccountHolderName, @InitialBalance);

    COMMIT TRANSACTION;
END;

2. Deposit 存储过程

CREATE PROCEDURE Deposit
    @AccountID INT,
    @Amount DECIMAL(18, 2)
AS
BEGIN
    IF @Amount <= 0
    BEGIN
        RAISERROR('Deposit amount must be greater than zero.', 16, 1);
        RETURN;
    END

    BEGIN TRANSACTION;

    UPDATE Accounts
    SET Balance = Balance + @Amount
    WHERE AccountID = @AccountID;

    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR('Account not found.', 16, 1);
        RETURN;
    END

    INSERT INTO Transactions (AccountID, TransactionType, Amount)
    VALUES (@AccountID, 'D', @Amount);

    COMMIT TRANSACTION;
END;

3. Withdraw 存储过程

CREATE PROCEDURE Withdraw
    @AccountID INT,
    @Amount DECIMAL(18, 2)
AS
BEGIN
    IF @Amount <= 0
    BEGIN
        RAISERROR('Withdrawal amount must be greater than zero.', 16, 1);
        RETURN;
    END

    DECLARE @CurrentBalance DECIMAL(18, 2);

    SELECT @CurrentBalance = Balance
    FROM Accounts
    WHERE AccountID = @AccountID;

    IF @CurrentBalance IS NULL
    BEGIN
        RAISERROR('Account not found.', 16, 1);
        RETURN;
    END

    IF @CurrentBalance < @Amount
    BEGIN
        RAISERROR('Insufficient funds.', 16, 1);
        RETURN;
    END

    BEGIN TRANSACTION;

    UPDATE Accounts
    SET Balance = Balance - @Amount
    WHERE AccountID = @AccountID;

    INSERT INTO Transactions (AccountID, TransactionType, Amount)
    VALUES (@AccountID, 'W', @Amount);

    COMMIT TRANSACTION;
END;

4. GetBalance 存储过程

CREATE PROCEDURE GetBalance
    @AccountID INT
AS
BEGIN
    SELECT Balance
    FROM Accounts
    WHERE AccountID = @AccountID;
END;

测试存储过程

现在我们可以测试这些存储过程来确保它们按预期工作。

创建账户

EXEC CreateAccount 'Alice Johnson', 1000.00;
EXEC CreateAccount 'Bob Smith', 500.00;

查询账户列表

SELECT * FROM Accounts;

存款

EXEC Deposit 1, 500.00; -- 向 Alice 的账户存入 500 元
EXEC Deposit 2, 200.00; -- 向 Bob 的账户存入 200 元

取款

EXEC Withdraw 1, 200.00; -- 从 Alice 的账户取出 200 元
EXEC Withdraw 2, 300.00; -- 尝试从 Bob 的账户取出 300 元（应失败，因为余额不足）

查询余额

EXEC GetBalance 1; -- 查询 Alice 的账户余额
EXEC GetBalance 2; -- 查询 Bob 的账户余额

查看交易记录

最后，我们可以查看交易记录以确认所有操作都被正确记录。

SELECT * FROM Transactions;

在路上 🦕