银行转账与事务处理、create procedure语句2

修改后的版本

建立数据库text

1. 建表
CREATE TABLE dbo.account
（
   acID     varchar(50)  NOT NULL,
   balance  money        not null
 ）； 

GO

CREATE TABLE dbo.tra
（
   acID       varchar(50) NOT NULL,
   tra_type   int          not null，
   tra_money  money       not null
   ）；

GO

2. 准备数据
INSERT INTO dbo.account
     VALUES ('1', 100.00)
INSERT INTO dbo.account
     VALUES ('2', 200.00)
INSERT INTO dbo.account
     VALUES ('3', 500.00)
INSERT INTO dbo.account
     VALUES ('4', 500.00)
GO

3.
USE text;
GO

create procedure TransferMoney1
    @FromAccountNo varchar(50) output,-- 转出账号
    @ToAccountNo varchar(50)output,--转入账号
    @MoneyCount money output--转账金额

as
if exists (select acID =@FromAccountNo from account)
begin
    if exists (select acID = @ToAccountNo from account  )
    begin   --转出账户和转入账户都存在，则开始转账
        --判断转出金额是否大于当前余额
        if (select balance from account where acID = @FromAccountNo) >= @MoneyCount
        begin
            --开始转账
            begin transaction --事务转账开始
            insert into tra (acID,tra_type, tra_money) values(@FromAccountNo, -1,@MoneyCount)--（-1）代表存取类型
            update account---转账账户更新
            set balance=balance-@MoneyCount
            where acID=@FromAccountNo
            if @@error <> 0
            begin
                rollback transaction--发生错误则回滚事务，无条件退出
                return
            end
            insert into [tra] (acID,tra_type, tra_money) values(@ToAccountNo, 1,@MoneyCount)
            update account----转账账户更新
            set balance=balance+@MoneyCount
            where acID=@ToAccountNo
            if @@error <> 0
            begin
                rollback tran
                return
            end
            commit transaction --两条语句都完成，提交事务
        end
        else   
            raiserror ('转账金额不能大于该账号的余额',16,1)
    end
    else
    raiserror ('转入账号不存在',16,1)
end
else
    raiserror ('转出账号不存在',16,1)

4 执行

exec TransferMoney 3,4,100
select * from account
select * from tra

5
wait for delay ‘00：00:10’