use master go if exists(select * from sysDatabases where name = 'BankDB') drop database BankDB go create database BankDB go use BankDB go --建用户信息表 if exists(select * from sysObjects where name = 'Xxl_UserInfo') drop table Xxl_UserInfo go create table Xxl_UserInfo ( Xxl_User_Id int not null primary key identity , Xxl_User_Name nvarchar(20) not null , Xxl_User_Sex bit not null , Xxl_User_IDcard char(18) not null unique , Xxl_User_Moblie char(11) not null check(Xxl_User_Moblie like '1[3579][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), Xxl_User_Address nvarchar(50) not null ) go --建用户卡信息表 if exists(select * from sysObjects where name = 'Xxl_CardInfo') drop table Xxl_CardInfo go create table Xxl_CardInfo ( Xxl_Card_No char(16) not null primary key check(Xxl_Card_No like '66668888[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') , Xxl_Card_pwd char(6) not null default('666888') , From_Xxl_User_Id int not null references Xxl_UserInfo(Xxl_User_Id), Xxl_Card_Date DateTime not null default(getdate()) , Xxl_Card_Balance decimal(18,2) not null check(Xxl_Card_Balance >= 0) , Xxl_Card_State int not null check(Xxl_Card_State in (0,1,2)), Xxl_Card_Text nvarchar(50) not null ) go --建交易信息表 if exists(select * from sysObjects where name = 'Xxl_TransInfo') drop table Xxl_TransInfo go create table Xxl_TransInfo ( Xxl_Trans_FlowNum int not null identity primary key , From_Xxl_Card_No char(16) not null references Xxl_CardInfo(Xxl_Card_No) , Xxl_Trans_Type int not null check(Xxl_Trans_Type in (1,2)) , Xxl_Trans_Quota decimal(18,2) not null check(Xxl_Trans_Quota > 0) , Xxl_Trans_Date DateTime not null default(getdate()) , Xxl_Trans_ed_Balance decimal(18,2) not null check(Xxl_Trans_ed_Balance >= 0) , Xxl_Trans_Text varchar(50) not null ) go ------添加用户信息 insert Xxl_UserInfo values('徐小龙',1,'42028120000114125X','13071226588','湖北武汉') insert Xxl_UserInfo values('张小杨',0,'42028119980515543X','13045114154','湖北武汉') insert Xxl_UserInfo values('吴小心',0,'42028120001202114X','13071557444','湖北武汉') ----添加用户卡信息 insert Xxl_CardInfo values('6666888845125214','666888',1,'2006-2-12',600,0,'使用') insert Xxl_CardInfo values('6666888865896548','666888',1,'2007-2-20',3000,0,'使用') insert Xxl_CardInfo values('6666888812454852','666888',2,'2016-6-12',6300,0,'使用') insert Xxl_CardInfo values('6666888852145698','666888',3,'2018-3-24',500,0,'使用') ----添加交易信息 insert Xxl_TransInfo values('6666888845125214',1,300,'2016-3-12',300,'存入300元') insert Xxl_TransInfo values('6666888845125214',1,300,'2017-5-3',600,'存入300元') insert Xxl_TransInfo values('6666888865896548',1,6000,'2013-9-1',6000,'存入6000元') insert Xxl_TransInfo values('6666888865896548',2,3000,'2014-9-1',3000,'转账3000元给6666888812454852') insert Xxl_TransInfo values('6666888812454852',1,3000,'2017-3-6',3000,'6666888865896548转入的3000元') insert Xxl_TransInfo values('6666888812454852',1,3300,'2017-12-1',6300,'存入3300元') insert Xxl_TransInfo values('6666888852145698',1,3000,'2018-6-3',3000,'存入3000元') insert Xxl_TransInfo values('6666888852145698',2,2500,'2018-7-3',500,'取出2500元') ------备份交易信息表 select * into Xxl_TransInfo_BAK from Xxl_TransInfo --------查询各表数据 --select * from Xxl_UserInfo --select * from Xxl_CardInfo --select * from Xxl_TransInfo --select * from Xxl_TransInfo_BAK ----------------------------------------创建函数---------------------------------------- --加逗号的函数 if exists(select * from sysObjects where name='function_JiaDouhao') drop function function_JiaDouhao go create function function_JiaDouhao( @Money decimal(18,2)) returns varchar(50) as begin declare @a varchar(50)= left(@Money,len(@Money)-3) declare @b varchar(50)= right(@Money,3) while (len(@a)>3) begin select @b = ','+right(@a,3)+@b select @a = left(@a,len(@a)-3) end return @a+@b end go ------------------------------------------结束------------------------------------------ ----------------------------------------创建视图---------------------------------------- --用户信息视图 if exists(select * from sysObjects where name ='vw_UserInfo') drop view vw_UserInfo go create view vw_UserInfo as select Xxl_User_Id 编号, Xxl_User_Name 姓名, case Xxl_User_Sex when 0 then '女' when 1 then '男' end 性别, Xxl_User_IDcard 身份证, Xxl_User_Moblie 联系电话, Xxl_User_Address 籍贯 from Xxl_UserInfo go --使用视图 --select * from vw_UserInfo --卡信息视图 if exists(select * from sysObjects where name='vw_CardInfo') drop view vw_CardInfo go create view vw_CardInfo as select Xxl_Card_No 卡号, Xxl_User_Name 姓名, Xxl_Card_Balance 余额, Xxl_Card_Date 开卡日期, case Xxl_Card_State when 0 then '正常' when 1 then '冻结' when 2 then '注销' end 状态, dbo.function_JiaDouhao(Xxl_Card_Balance) 货币表示 from Xxl_UserInfo UserInfo inner join Xxl_CardInfo CardInfo on UserInfo.Xxl_User_Id = CardInfo.From_Xxl_User_Id go --使用视图 --select * from vw_CardInfo --交易记录视图 if exists(select * from sysObjects where name='vw_TransInfo') drop view vw_TransInfo go create view vw_TransInfo as select ----卡号,交易日期,交易类型,交易金额,余额,描述 Xxl_Card_No 卡号, Xxl_Trans_Date 交易日期, case Xxl_Trans_Type when 1 then '存入' when 2 then '支取' end 交易类型, case Xxl_Trans_Type when 1 then '+'+convert(varchar(20),Xxl_Trans_Quota) when 2 then '-'+convert(varchar(20),Xxl_Trans_Quota) end 交易金额, Xxl_Trans_ed_Balance 余额, Xxl_Trans_Text 描述 from Xxl_CardInfo CardInfo inner join Xxl_TransInfo TransInfo on CardInfo.Xxl_Card_No = TransInfo.From_Xxl_Card_No go --使用视图 --select * from vw_TransInfo --------------------------------------------结束-------------------------------------------- ----------------------------------------创建存储过程---------------------------------------- --1、 查询余额 if exists(select * from sysObjects where name='p_SelectBalance') drop proc p_SelectBalance go create proc p_SelectBalance @CardNo char(16) as select 货币表示 as 余额 from vw_CardInfo where 卡号 = @CardNo go --exec p_SelectBalance '6666888845125214' --2、 查询某两日期之间交易记录 if exists(select * from sysObjects where name='p_SelectStart_StopDate') drop proc p_SelectStart_StopDate go create proc p_SelectStart_StopDate @CardNo char(16), @StartDate datetime, @StopDate datetime as select * from vw_TransInfo where 卡号 = @CardNo and 交易日期 >= @StartDate and 交易日期 < dateadd(dd,1,@StopDate) go --exec p_SelectStart_StopDate '6666888845125214','1990-1-1','2018-9-9' --3、 修改密码功能 if exists(select * from sysObjects where name='p_Update_Pwd') drop proc p_Update_Pwd go create proc p_Update_Pwd @CardNo char(16), @CardPwdStart char(6), @CardPwdStop char(6) as update Xxl_CardInfo set Xxl_Card_pwd=@CardPwdStop where Xxl_Card_No = @CardNo and Xxl_Card_pwd = @CardPwdStart go --exec p_Update_Pwd '6666888845125214','666888','548888' --4、 存款功能(备份) if exists(select * from sysObjects where name='p_SeveMoney') drop proc p_SeveMoney go create proc p_SeveMoney @CardNo char(16), @Quota decimal(18,2), @errMeg varchar(50) output as --判断存款金额 if @Quota <= 0 begin set @errMeg = '输入金额有误!' return -1 end begin tran declare @err int = 0 declare @startBalance decimal(18,2) = 0 select @startBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @CardNo --添加存款记录 insert Xxl_TransInfo values(@CardNo,1,@Quota,getdate(),(@startBalance + @Quota),('存入' + convert(varchar(50), @Quota) + '元')) select @err = @@ERROR + @err --更新余额 update Xxl_CardInfo set Xxl_Card_Balance = (@startBalance + @Quota) where Xxl_Card_No = @CardNo select @err = @@ERROR + @err if @err = 0 begin set @errMeg = '操作成功' commit tran return 0 end begin set @errMeg = '未知错误!' rollback tran return -1 end go --5、 取款功能(备份) if exists(select * from sysObjects where name='p_GetMoney') drop proc p_GetMoney go create proc p_GetMoney @CardNo char(16), @Quota decimal(18,2), @errMeg varchar(50) output as --判断取款金额 if @Quota <= 0 begin set @errMeg = '输入金额有误!' return -1 end --查询原有余额 declare @startBalance decimal(18,2) select @startBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @CardNo --判断余额是否足够 if @startBalance > @Quota begin set @errMeg = '余额不足!' return -1 end begin tran declare @err int = 0 --添加取款记录 insert Xxl_TransInfo values(@CardNo,2,@Quota,getdate(),(@startBalance - @Quota),('取出' + convert(varchar(50), @Quota) + '元')) select @err = @@ERROR + @err --更新余额 update Xxl_CardInfo set Xxl_Card_Balance = (@startBalance - @Quota) where Xxl_Card_No = @CardNo select @err = @@ERROR + @err if @err = 0 begin set @errMeg = '操作成功' commit tran return 0 end else begin set @errMeg = '未知错误!' rollback tran return -1 end go --6、 转帐功能(备份) if exists(select * from sysObjects where name='p_TeansferMoney') drop proc p_TeansferMoney go create proc p_TeansferMoney @FromCardNo char(16), @ToCardNo char(16), @Quota decimal(18,2), @errMeg varchar(50) output as --判断目标账户是否为本身 if @FromCardNo != @ToCardNo begin set @errMeg = '目标账户不可以为自己!' return -1 end --判断目标账户是否存在 if not exists(select * from Xxl_CardInfo where Xxl_Card_No = @ToCardNo) begin set @errMeg = '目标账户不存在!' return -1 end --判断转账金额是否正确 if @Quota <= 0 begin set @errMeg = '输入金额有误!' return -1 end --查询From原有余额 declare @FromStartBalance decimal(18,2) select @FromStartBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @FromCardNo --判断From余额是否充足 if @FromStartBalance < @Quota begin set @errMeg = '余额不足!' return -1 end begin tran declare @err int = 0 --查询To原有余额 declare @ToStartBalance decimal(18,2) select @ToStartBalance=Xxl_Card_Balance from Xxl_CardInfo where Xxl_Card_No = @ToCardNo --更新余额 update Xxl_CardInfo set Xxl_Card_Balance = (@FromStartBalance - @Quota) where Xxl_Card_No = @FromCardNo select @err = @@ERROR + @err update Xxl_CardInfo set Xxl_Card_Balance = (@ToStartBalance + @Quota) where Xxl_Card_No = @ToCardNo select @err = @@ERROR + @err --添加交易记录 insert Xxl_TransInfo values(@FromCardNo,2,@Quota,getdate(),(@FromStartBalance - @Quota), '转出' + convert(varchar(50), @Quota) + '元给'+@ToCardNo) select @err = @@ERROR + @err insert Xxl_TransInfo values(@ToCardNo,1,@Quota,getdate(),(@ToStartBalance + @Quota),('由' +@FromCardNo+ '转入'+ convert(varchar(50), @Quota) + '元')) select @err = @@ERROR + @err if @err = 0 begin set @errMeg = '操作成功!' commit tran return 0 end else begin set @errMeg = '未知错误!' rollback tran return -1 end go --exec p_TeansferMoney '6666888812454852','6666888845125214',300.00 --7、 随机产生卡号(卡号格式为:8228 6688 XXXX XXXX) 注:随机产生的卡号已经存在的不能用 if exists(select * from sysObjects where name='P_GenerateBankcard') drop proc P_GenerateBankcard go create proc P_GenerateBankcard @Card char(16) output as while 1 = 1 begin select @Card = convert (varchar(8), '66668888')+right(convert(dec(10,10),rand()),8) if not exists(select * from Xxl_CardInfo where Xxl_Card_No = @Card) break end go --declare @Card char(16) --exec P_GenerateBankcard @Card output --select @Card as 卡号 --8、 开户功能 if exists(select * from sysobjects where name = 'P_AccountOpening') drop proc P_AccountOpening go create proc P_AccountOpening @Name nvarchar(20), @Sex bit, @IdCard char(18), @Moblie char(11), @Address nvarchar(50), @errMeg varchar(50) output as if exists (select * from Xxl_UserInfo where Xxl_User_IDcard =@IdCard) begin set @errMeg = '存在此账户!' return -1 end begin tran declare @UserID int declare @err int = 0 insert Xxl_UserInfo values(@Name,@Sex,@IdCard,@Moblie,@Address) select @err = @@ERROR + @err declare @Card varchar(16) exec P_GenerateBankcard @Card output select @UserID = Xxl_User_Id from Xxl_UserInfo where Xxl_User_IDcard = @IdCard insert Xxl_CardInfo values(@Card,default,@UserId,getdate(),0,0,'使用') select @err = @@ERROR + @err if(@err = 0) begin set @errMeg = '开户成功!' commit tran return 0 end else begin set @errMeg = '未知错误!' rollback tran return -1 end go --9、 解冻功能 if exists(select * from sysobjects where name = 'P_ThawAccount') drop proc P_ThawAccount go create proc P_ThawAccount @CardNo nchar(18) as update Xxl_CardInfo set Xxl_Card_State = 0 where Xxl_Card_No = @CardNo return 0 go --10、 根据用户身份证,查询该用户下所有的银行卡信息 if exists(select * from sysobjects where name = 'P_SelectCard') drop proc P_SelectCard go create proc P_SelectCard @IdCard nchar(18) as declare @ID varchar(20) select @ID = Xxl_User_Id from Xxl_UserInfo where Xxl_User_IDcard = @IdCard select * from Xxl_CardInfo where From_Xxl_User_Id = @ID go --------------------------------------------结束--------------------------------------------