Sqlserver存储过程模拟银行转账业务

今天在学习数据库系统概论的时候,学习到了存储过程这一个章节,其中有一个模拟银行转账的案例,我想着用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
谢谢博主