今天在学习数据库系统概论的时候,学习到了存储过程这一个章节,其中有一个模拟银行转账的案例,我想着用Sqlserver模拟实现一遍,一直没有写出来,后来查询很多资料终于写出来了.
1.建表
Create table Account(
id int primary key,
username varchar(10),
money float
)
-- 插入数据
select * from Account
insert into Account values
(1008611,'小明',10000.0),
(1008612,'徐晓红',10000.0)
2.建立存储过程
CREATE PROCEDURE transfer
@out_cardno int,
@in_cardno int,
@money float
as
BEGIN
DECLARE
@remain float,
@outid int,
@inid int
select @outid=id from account where id=@out_cardno;
select @inid=id from account where id=@in_cardno;
select @remain=money from account where id=@out_cardno;
-- 如果转账用户的余额小于转账金额,则报错
if @remain<@money
BEGIN
PRINT '余额不足'
END
-- 如果查询的用户不存在则报错
if @outid is null or @inid is null
BEGIN
PRINT '账户不存在'
END
-- 如果转账金额,账户都满足上面的条件则转账
if @remain>=@money and @outid is not null and @remain is not null
BEGIN
BEGIN TRANSACTION T1
update account set money = money-@money where id=@out_cardno;
update account set money = money+@money where id=@in_cardno;
PRINT '转账成功.'
-- 只要不满足其中之一就回滚
if @remain<@money or @outid is null or @remain is null
begin
rollback transaction;
end
COMMIT TRANSACTION T1 -- 提交事务
END
END
3.验证
EXEC transfer 1008612,1008611,10000.0
结果如下
参考文档
https://blog.csdn.net/pcschoolchina/article/details/3794116?utm_source=blogxgwz4
谢谢博主