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 --------------------------------------------結束--------------------------------------------