实用的银行转账存储过程和流水号生成存储过程
银行转账存储过程
USE[BankInfor] GO SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO ALTERPROCEDURE[dbo].[Transfer](@inAccountint,@outAccountint,@amountfloat) asdeclare @totalDepositfloat; begin select@totalDeposit=totalfromAccountwhereAccountNum=@outAccount; if@totalDepositisnull begin rollback; print'转出账户不存在或账户中没有存款' return; end if@totalDeposit<@amount begin rollback; print'余额不足,不能操作' return; end updateAccountsettotal=total-@amountwhereAccountNum=@outAccount; updateAccountsettotal=total+@amountwhereAccountNum=@inAccount; print'转账成功!' commit; end;
流水号生成存储过程
ifexists(select1fromsysobjectswhereid=OBJECT_ID('GetSerialNo')andxtype='p') dropprocGetSerialNo go Createprocedure[dbo].[GetSerialNo] ( @sCodevarchar(50) ) as begin Declare@sValuevarchar(16),@dTodaydatetime,@sQZvarchar(50)--这个代表前缀 BeginTran BeginTry --锁定该条记录,好多人用lock去锁,起始这里只要执行一句update就可以了 --在同一个事物中,执行了update语句之后就会启动锁 UpdateSerialNosetsValue=sValuewheresCode=@sCode Select@sValue=sValueFromSerialNowheresCode=@sCode Select@sQZ=sQZFromSerialNowheresCode=@sCode --因子表中没有记录,插入初始值 If@sValueisnull Begin Select@sValue=convert(bigint,convert(varchar(6),getdate(),12)+'000001') UpdateSerialNosetsValue=@sValuewheresCode=@sCode endelse 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') UpdateSerialNosetsValue=@sValuewheresCode=@sCode End Selectresult=@sQZ+@sValue CommitTran EndTry BeginCatch RollbackTran Selectresult='Error' EndCatch end select*fromSerialNo selectconvert(varchar(6),getdate(),12)+'000001'