实用的银行转账存储过程和流水号生成存储过程
程序员文章站
2022-06-29 12:33:45
银行转账存储过程
use [bankinfor]
go
set ansi_nulls on
go
set quoted_identifier on
go...
银行转账存储过程
use [bankinfor] go set ansi_nulls on go set quoted_identifier on go alter procedure [dbo].[transfer](@inaccount int,@outaccount int,@amount float) as declare @totaldeposit float; begin select @totaldeposit=total from account where accountnum=@outaccount; if @totaldeposit is null begin rollback; print'转出账户不存在或账户中没有存款' return; end if @totaldeposit<@amount begin rollback; print'余额不足,不能操作' return; end update account set total=total-@amount where accountnum=@outaccount; update account set total=total+@amount where accountnum=@inaccount; print'转账成功!' commit; end;
流水号生成存储过程
if exists(select 1 from sysobjects where id=object_id('getserialno') and xtype='p') drop proc getserialno go create procedure [dbo].[getserialno] ( @scode varchar(50) ) as begin declare @svalue varchar(16),@dtoday datetime,@sqz varchar(50) --这个代表前缀 begin tran begin try -- 锁定该条记录,好多人用lock去锁,起始这里只要执行一句update就可以了 --在同一个事物中,执行了update语句之后就会启动锁 update serialno set svalue=svalue where scode=@scode select @svalue = svalue from serialno where scode=@scode select @sqz = sqz from serialno where scode=@scode -- 因子表中没有记录,插入初始值 if @svalue is null begin select @svalue = convert(bigint, convert(varchar(6), getdate(), 12) + '000001') update serialno set svalue=@svalue where scode=@scode end else begin --因子表中没有记录 select @dtoday = substring(@svalue,1,6) --如果日期相等,则加1 if @dtoday = convert(varchar(6), getdate(), 12) select @svalue = convert(varchar(16), (convert(bigint, @svalue) + 1)) else --如果日期不相等,则先赋值日期,流水号从1开始 select @svalue = convert(bigint, convert(varchar(6), getdate(), 12) +'000001') update serialno set svalue =@svalue where scode=@scode end select result = @sqz+@svalue commit tran end try begin catch rollback tran select result = 'error' end catch end select*from serialno select convert(varchar(6), getdate(), 12)+'000001'
上一篇: MyBatis批量修改操作