在存储过程中运用事务
create database study
use study
create table peopleInfor --用户信息表
(
ID int primary key identity(1001,1),--主键自动增长
name1 nvarchar(30),--用户名
YMoney money default(0)--卡上剩余金额
)
create table peopleInfor_mx --用户取款明细
(
mx_id int primary key identity(100001,1), --主键自动增长
mx_Money money , --取款或付款金额
mx_type nvarchar(20) check (mx_type='取钱' or mx_type ='存钱'),--代表此条数据是 存钱,还是 取钱
peopleId int foreign key (peopleID)references peopleInfor(id) not null
)
--创建一个基本的存储过程
create procedure pro_insert(@name nvarchar(30),@money money)as
begin
insert into peopleInfor(name1,YMoney)values(@name,@money)
end
--执行 存储过程。
exec pro_insert '赵刚',20000
exec pro_insert '王五',30000
--错误的例子模仿银行转账
create procedure pro_zhuangzhang
@name_Jin nvarchar(30),--转进账户名称
@name_chu nvarchar(30), --转出帐户名称
@money money --转入钱数)
as
begin tran --开发事务
declare @YMoney money --账上余额
declare @chuID int --出款账号的ID
declare @JinID int --进款账号的ID
select @YMoney=YMoney from peopleInfor where name1=@name_chu --获取账上余额
if(@money<=@YMoney)--判断 账号余额 是否 大于等于 转款余额
begin
update peopleInfor set YMoney=YMoney-@money where name1=@name_chu -- 从 出帐 帐户中减去 转出金额
select @chuID=ID from peopleInfor where name1=@name_chu -- 获得出帐ID
insert into peopleInfor_mx (mx_Money,mx_type,peopleId)values(@money,'取钱',@chuID) --把转出金额 插入明细 表中并标明是 取出的钱
update peopleInfor set YMoney=YMoney+@money where name1=@name_Jin-- 在 进帐 帐户中加上 转出金额
select @chuID=ID from peopleInfor where name1=@name_Jin-- -- 获得出帐ID
insert into peopleInfor_mx (mx_Money,mx_type,peopleId)values(@money,'存钱',@JinID)
-- 此时 @JinID 为空时 ,不能插入此列。故软件报错。
if @@error<>0
begin
rollback tran
end
end
commit tran --执行事务
GO
--对的例子 模仿银行转账
create procedure pro_zhuangzhang
@name_Jin nvarchar(30),--转进账户名称
@name_chu nvarchar(30), --转出帐户名称
@money money --转入钱数)
as
begin tran --开发事务
declare @YMoney money
declare @chuID int
declare @JinID int
select @YMoney=YMoney from peopleInfor where name1=@name_chu
if(@money<@YMoney)
begin
update peopleInfor set YMoney=YMoney-@money where name1=@name_chu
select @chuID=ID from peopleInfor where name1=@name_chu
insert into peopleInfor_mx (mx_Money,mx_type,peopleId)values(@money,'取钱',@chuID)
update peopleInfor set YMoney=YMoney+@money where name1=@name_Jin
select @JinID=ID from peopleInfor where name1=@name_Jin
insert into peopleInfor_mx (mx_Money,mx_type,peopleId)values(@money,'存钱',@JinID)
if @@error<>0
begin
rollback tran
end
end
commit tran --执行事务
GO
select * from peopleInfor
select * from peopleInfor_mx
delete from peopleInfor where id=1003
exec pro_zhuangzhang '赵刚','王五',20000
'赵刚',20000
'王五',30000