ATM银行转账系统之数据库存储过程学习

           在网上下了一个关于ATM银行转账系统，里面用到了大量的存储过程，觉得挺好的，对于学习存储过程有很大的帮助！把整个代码粘贴下来！以供以后写存储过程时参考。/*银行取款机数据库*/ use master go if exists(select * from sysdatabases where name='bankDB') drop database bankDB go create database bankDB --建立数据库bankDB go use bankDB go if exists(select * from sysobjects where name='userInfo') drop table userInfo go create table userInfo --建立用户信息表 ( customerID int identity(1,1) primary key,--顾客编号 customerName varchar(20) not null,--开户名 PID numeric not null,--身份证号 telephone varchar(30) not null,--联系电话 userAddress varchar(100)--居住地址 ) go alter table userInfo add constraint UK_PID unique(PID) alter table userInfo add constraint CK_PID check(len(PID)=15 or len(PID)=18) alter table userInfo add constraint CK_telephone check(telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or len(telephone)=11) go use bankDB go if exists(select * from sysobjects where name='cardInfo') drop table cardInfo go create table cardInfo --建立银行卡信息表 ( cardID varchar(30) primary key,--卡号 curType varchar(10) not null,--货币种类 savingType varchar(20),--存款类型 openDate datetime not null,--开户日期 openMoney money not null,--开户金额 balance money not null,--余额 pass int not null,--密码 IsReportLoss bit not null,--是否挂失 customerID int not null--顾客编号 ) go alter table cardInfo add constraint CK_cardID check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]') alter table cardInfo add constraint DF_curType default('RMB') for curType alter table cardInfo add constraint CK_savingType check(savingType in ('活期','定活两便','定期')) alter table cardInfo add constraint DF_openDate default(getdate()) for openDate alter table cardInfo add constraint CK_openMoney check(openMoney>=1) alter table cardInfo add constraint CK_balance check(balance>=1) alter table cardInfo add constraint CK_pass check(len(pass)=6) alter table cardInfo add constraint DF_pass default(888888) for pass alter table cardInfo add constraint DF_IsReportLoss default(0) for IsReportLoss alter table cardInfo add constraint FK_customerID foreign key(customerID) references userInfo(customerID) go use bankDB go if exists(select * from sysobjects where name='transInfo') drop table transInfo go create table transInfo --建立交易信息表 ( transDate datetime not null,--交易日期 cardID varchar(30) not null,--卡号 transType varchar(10) not null,--交易类型 transMoney money not null,--交易金额 remark varchar(100) --备注 ) go alter table transInfo add constraint DF_transDate default(getdate()) for transDate alter table transInfo add constraint FK_cardID foreign key(cardID) references cardInfo(cardID) alter table transInfo add constraint CK_transType check(transType='存取' or transType='支取') alter table transInfo add constraint CK_transMoney check(transMoney>0) go /*填充数据*/ use bankDB go insert into userInfo(customerName,PID,telephone,userAddress) values('张三',123456789012345,'010-67898978','北京海淀') insert into userInfo(customerName,PID,telephone,userAddress) values('李四',321245678912345678,'027-67898978','武汉江岸区') insert into userInfo(customerName,PID,telephone,userAddress) values('王五',221245678912345672,'021-67898978','上海浦东区') insert into userInfo(customerName,PID,telephone,userAddress) values('赵六',521245678912345675,'13178910771','合肥肥西区') go insert into cardInfo(cardID,savingType,openMoney,balance,pass,customerID) values('1010 3576 1234 5678','活期',1000,1000,123123,1) insert into cardInfo(cardID,savingType,openMoney,balance,pass,customerID) values('1010 3576 1212 1134','定期',1,1,321321,2) insert into cardInfo(cardID,savingType,openMoney,balance,customerID) values('1010 3576 1256 2235','定活两便',50,50,3) insert into cardInfo(cardID,savingType,openMoney,balance,customerID) values('1010 3576 1358 3221','活期',500,500,4) go insert into transInfo(transDate,transType,cardID,transMoney) values(getdate(),'支取','1010 3576 1234 5678',900) update cardInfo set balance=balance-900 where customerID=1 insert into transInfo(transDate,transType,cardID,transMoney) values(getdate(),'存取','1010 3576 1212 1134',5000) update cardInfo set balance=balance+5000 where customerID=2 update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134' update cardInfo set IsReportLoss=1 where cardID='1010 3576 1256 2235' select * from userInfo select * from cardInfo select * from transInfo /*产生随机卡号存储过程*/ use bankDB go create proc p_randCardID @randCardID char(19) output as declare @r numeric(15,8) select @r=rand((datepart(mm,getdate())*10000)+(datepart(ss,getdate())*1000)+(datepart(ms,getdate()))) --declare @tempStr varchar(20) --set @tempStr=@r select @randCardID='1010 3576'+substring(cast(@r as varchar),3,4)+' '+subString(cast(@r as varchar),4,4) go declare @mycardID char(19) exec p_randCardID @mycardID output print '产生的随机号为：'+@mycardID /*开户存储过程*/ use bankDB go select * from userInfo select * from cardInfo select * from transInfo create proc p_openAccount @customerName char(8), @cardID varchar(30), @PID char(18), @telephone char(13), @openMoney money, @savingType char(8), @userAddress varchar(50)='' as if(@openMoney>=1) begin insert into userInfo(customerName,PID,telephone,userAddress) values(@customerName,@PID,@telephone,@userAddress) declare @cur_customerID int select @cur_customerID=customerID from userInfo where PID=@PID insert into cardInfo(cardID,savingType,openMoney,balance,customerID) values(@cardID,@savingType,@openMoney,@openMoney,@cur_customerID) end else print '开户金额不能小于1元' go /*存钱取钱存储过程*/ use bankDB go drop proc p_takeMoney go create proc p_takeMoney @card char(19),@m money,@type char(4),@inputPass int='' as declare @balance money select @balance=balance from cardInfo where @card=cardID if @type='存取' begin print '交易正进行，请稍后......' insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@card,'存取',@m) set @balance=@balance+@m update cardInfo set balance=balance+@m where @card=cardID if @@error=0 begin print '交易成功！' print '卡号:'+@card+' 余额：'+cast(@balance as varchar) end else print '交易失败！' end if @type='支取' begin declare @pass int select @pass=pass from cardInfo where @card=cardID if(@inputPass=@pass) begin begin tran print '交易正进行，请稍后......' insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@card,'支取',@m) set @balance=@balance-@m update cardInfo set balance=balance-@m where @card=cardID if @@error>0 begin rollback tran print '交易失败！余额不足' print '卡号:'+@card+' 余额：'+cast(@balance as varchar) end else begin commit tran print '交易成功！交易金额'+cast(@m as varchar(20)) print '卡号:'+@card+' 余额：'+cast(@balance as varchar) end end else print '密码输入不正确' end go /*转账存储过程*/ use bankDB go set nocount on drop proc p_transfer go create proc p_transfer @card1 char(19), @card2 char(19), @outMoney money as begin tran exec p_takeMoney @card1,@outMoney,'支取','321321' if @@error=0 begin print '开始转账，请稍后......' print '转账正在进行，请稍候......' insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@card2,'存取',@outMoney) declare @balance money select @balance=balance from cardInfo where cardID=@card2 update cardInfo set balance=balance+@outMoney where cardID=@card2 set @balance=@balance+@outMoney print '交易成功！交易金额：'+cast(@outMoney as varchar) print '卡号:'+@card2+' 余额:'+cast(@balance as varchar) commit tran end else begin raiserror('交易失败！',16,1) rollback tran end go /*存、取、转账业务*/ use bankDB go set nocount on exec p_openAccount '小霞','1010 3576 1111 1133','111331111111115511','021-63568978',900,'活期','河南新乡'--开户 exec p_takeMoney '1010 3576 1212 1134 ',200,'存取',321321 --存钱 exec p_takeMoney '1010 3576 1234 5678 ',111,'支取',123123 --取钱 exec p_transfer '1010 3576 1212 1134','1010 3576 1111 1122',5 --转账 select * from dbo.cardInfo select * from dbo.transInfo select * from dbo.userInfo /*银行相关业务*/ use bankDB go --统计银行的资金流通余额和盈利结算 declare @inMoney money,@outMoney money select @inMoney=sum(transMoney) from transInfo where transType='存取' select @outMoney=sum(transMoney) from transInfo where transType='支取' print '银行流通余额总计为：'+cast(@inMoney-@outMoney as varchar(20))+'RMB' print '盈利结算为：'+cast(@outMoney*0.008-@inMoney*0.003 as varchar(20))+'RMB' --查询本周开户的卡号，显示该卡相关信息 declare @openDay datetime select @openDay=openDate from cardInfo select * from cardInfo where datediff(w,@openDay,getdate())=0 --查询本月交易金额最高的卡号 declare @transCash money select @transCash=max(transMoney) from transInfo select 最高交易卡号=cardID from transInfo where transMoney=@transCash --催款提醒业务 select 客户姓名=userInfo.customerName,联系电话=userInfo.telephone,账上余额=cardInfo.balance from userInfo join cardInfo on userInfo.customerID=cardInfo.customerID where cardInfo.balance<200