bankDB银行
来源：互联网 发布：汽车ecu编程器 编辑：程序博客网 时间：2024/04/29 23:09
--用例4：模拟常规业务--修改客户密码UPDATE cardInfo SET pass = '123456' WHERE cardId = '1010 3576 1234 5678' UPDATE cardInfo SET pass = '123123' WHERE cardId = '1010 3576 1212 1134'--办理银行卡挂失UPDATE cardInfo SET IsReportLoss = '挂失' WHERE cardId = '1010 3576 1212 1134'--3.统计银行资金流通余额和盈利结算declare @inMoney money ,@outmoney money ,@yumoney money select @inMoney=SUM(tradeMoney) from tradeInfo where tradeType='存入'select @outmoney=sum(trademoney) from tradeInfo where tradetype='支取'print '银行流通余额总计：'+convert (varchar(20) ,(@inmoney-@outmoney))+'RMB'set @yumoney=@outmoney*0.008-@inMoney*0.003print '盈利结算为:'+convert(varchar(20),@yumoney)+'RMB'--4.查询本周开户信息select * from cardInfo where DateDiff(wk, openDate, GetDate()) >= 0 and DateDiff(wk, openDate, GetDate()) <= 6--5.查询本月交易金额最高卡号select cardid from tradeInfo where trademoney= (select MAX(trademoney) from tradeInfo) --6.查询挂失客户select * from userInfo where [customerID] in (select customerID from cardInfo where IsReportLoss=1 )--7.催款提醒业务select [customerName] as 客户名字 , [telephone] as 联系电话,[balance] as 卡内余额from userInfo inner join cardInfo on cardInfo.customerID=userInfo.customerIDwhere cardInfo.balance<200--创建 使用视图--银行客户记录视图gocreate view vw_userInfo asselect customerID as 客户编号,customerName as 开户名,PID as 身份证号,telephone as 电话号码,address as 居住地址from userInfogoselect * from vw_userInfo--银行卡记录视图gocreate view vw_cardInfoasselect cardID as 卡号, userInfo.customerName as 客户,curID as 货币类型,Deposit.savingName as 存款类型 ,openDate as 开户日期,balance as 余额,pass as 密码,IsReportLoss as 是否挂失 from cardInfoinner join userInfo on userInfo.customerID=cardInfo.customerIDinner join Deposit on Deposit.savingID=cardInfo.savingIDgoselect * from vw_cardInfo--银行卡的交易记录视图go create view vw_tradeInfoasselect tradeDate as 交易日期,tradeType as 交易类型,cardID as 卡号,tradeMoney as 交易金额,remark as 备注 from tradeInfogo select * from vw_tradeInfo--用例6 使用存储过程实现业务处理--1if exists(select * from sysobjects where name='proc_inout')drop proc proc_inoutgocreate proc proc_inout@cardid varchar(19),@pass varchar(6),@transtype varchar(4),@transmoney moneyasset nocount ondeclare @sumerror intset @sumerror=0if exists(select * from cardinfo where cardid=@cardid) begin if exists(select*from cardinfo where pass=@pass and cardid=@cardid) begin if(@transtype='存入') begin update cardinfo set balance=balance+@transmoney where cardid=@cardid and pass=@pass insert tradeInfo(cardid,tradeType,tradeMoney) values(@cardid,'存入',@transmoney) print '交易成功！' declare @bal1 money select @bal1=balance from cardinfo where pass=@pass and cardid=@cardid print '卡号：'+@cardid+' 余额：'+convert(varchar(20),@bal1) end else begin if(@transtype='支取') begin --开始事物 begin transaction update cardinfo set balance=balance-@transmoney where cardid=@cardid and pass=@pass set @sumerror=@sumerror+@@error insert tradeInfo(cardid,tradeType,tradeMoney) values(@cardid,'支取',@transmoney) set @sumerror=@sumerror+@@error if(@sumerror<>0) begin print '交易失败！' rollback transaction --回滚事物 end else begin print '交易成功！' commit transaction --提交事物 declare @bal2 money select @bal2=balance from cardinfo where pass=@pass and cardid=@cardid print '卡号：'+@cardid+' 余额：'+convert(varchar(20),@bal2) end end end end else begin print '您输入的密码错误！' end endelse begin print '您输入的卡号并不存在！' endgoexec proc_inout '1010 3576 1234 5678','123456','存入',2000exec proc_inout '1010 3576 1212 1134','123123','支取',2000select*from vw_cardInfoselect*from vw_userInfoselect*from vw_tradeInfo --产生随机卡号·if exists(select * from sysobjects where name='usp_randCardID')drop proc usp_randCardIDgocreate proc usp_randCardID@randCardID varchar(19) outputasdeclare @r numeric(15,8),@tempStr varchar(10) --产生随机数,如:0.55181485select @r=RAND((DATEPART(mm,getdate())*1000)+(datepart(ss,getdate())*1000)+(datepart(ms,getdate()))) --转化成字符串数据类型set @tempStr=CONVERT(varchar(10),@r) --拼接字符串==========SUBSTRING(@tempStr,3,4)----从第三个开始(5)长度为4--(5518)set @randCardID='1010 3756 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4)godeclare @mycardID char(19)exec usp_randCardID @mycardID outputprint '产生的随机卡号为: ' +@mycardIDif exists (select * from sysobjects where name = 'usp_openAccount')drop proc usp_openAccountGOcreate procedure usp_openAccount @customerName char(8),@PID char(18),@telephone char(13) ,@openMoney money,@savingName char(8),@address varchar(50)='' AS DECLARE @mycardID char(19),@cur_customerID int, @savingID int --调用产生随机卡号的存储过程获得随机卡号 EXECUTE usp_randCardID @mycardID OUTPUT while exists(SELECT * FROM cardInfo WHERE cardID=@mycardID) EXECUTE usp_randCardID @mycardID OUTPUT print '尊敬的客户,开户成功!系统为您产生的随机卡号为:'+@mycardID print '开户日期'+convert(char(10),getdate(),111)+' 开户金额:'+convert(varchar(20),@openMoney) IF not exists(select * from userInfo where PID=@PID) INSERT INTO userInfo(customerName,PID,telephone,address ) VALUES(@customerName,@PID,@telephone,@address) SELECT @savingID = savingID FROM deposit WHERE savingName =@savingName--SELECT savingID FROM deposit WHERE savingName ='活期'if @savingID is NULL BEGIN RAISERROR('存款类型不正确,请重新输入!',16,1) RETURN -1 END--PRINT CAST(@savingID AS varchar(10)) select @cur_customerID=customerID from userInfo where PID=@PID--PRINT CAST(@cur_customerID AS varchar(10)) INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES(@mycardID,@savingID,@openMoney,@openMoney,@cur_customerID)GO--调用存储过程重新开户EXEC usp_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡' EXEC usp_openAccount '赵二','213445678912342222','0760-44446666',1,'定期一年' select * from vw_userInfoselect * from vw_cardInfoGO/*---- 4.打印对账单 ----*/if exists (select * from sysobjects where name = 'usp_CheckSheet')drop proc usp_CheckSheetGOCREATE PROCEDURE usp_CheckSheet @cardID varchar(19), @date1 datetime=NULL, @date2 datetime=NULLASDECLARE @custName varchar(20)DECLARE @curName varchar(20)DECLARE @savingName varchar(20)DECLARE @openDate datetimeSELECT @cardID=c.cardID, @curName=c.curID, @custName=u.customerName, @savingName=d.savingName , @openDate=c.openDate FROM cardInfo c, userInfo u, deposit dWHERE c.customerID=u.customerID and c.savingID = d.savingID and cardID = @cardID --and u.customerName = user_name()PRINT '卡号：' + @cardIDPRINT '姓名:' + @custNamePRINT '货币:' + @curNamePRINT '存款类型：' + @savingNamePRINT '开户日期：' + CAST(DATEPART(yyyy,@openDate) AS VARCHAR(4))+'年' + CAST(DATEPART(mm,@openDate) AS VARCHAR(2))+'月' + CAST(DATEPART(dd,@openDate) AS VARCHAR(2))+'日'PRINT ' 'print '--------------------------------------------------------------------' IF @date1 IS NULL AND @date2 IS NULL BEGINSELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注FROM tradeInfoWHERE cardID='1010 3576 1212 1134'--@cardIDORDER BY tradeDateRETURN END ELSE IF @date2 IS NULL SET @date2 = getdate() SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注FROM tradeInfoWHERE cardID=@cardID AND tradeDate BETWEEN @date1 AND @date2ORDER BY tradeDateGO--测试打印对帐单EXEC usp_CheckSheet '1010 3576 1212 1134'EXEC usp_CheckSheet '1010 3576 1212 1134','2009-11-2','2009-11-30'/*--5.输入页数和每页显示的记录数，实现分页显示*/if exists (select * from sysobjects where name = 'usp_pagingDisplay')DROP PROCEDURE usp_pagingDisplayGOCREATE PROCEDURE usp_pagingDisplay @records int = 10, @page int = 1AS SET NOCOUNT ON DECLARE @rec1 int SET @rec1 = @records --@page * @records DECLARE @rec2 int SET @rec2 = (@page - 1) * @records DECLARE @statement nvarchar(200) SET @statement='SELECT TOP ' + CAST(@rec1 AS varchar(10)) + ' tradeDate 交易日期,tradeType 交易类型,cardID 卡号,trademoney 交易金额 FROM tradeInfo WHERE cardID not in (SELECT TOP '+ CAST(@rec2 AS varchar(10)) + ' cardID FROM tradeInfo)' --print @statement EXEC SP_EXECUTESQL @statement,N'@rec1 int,@rec2 int',@rec1,@rec2GO--EXEC usp_pagingDisplay 2,2/*--6.查询、统计在指定时间段内没有发生交易的账户信息*/if exists (select * from sysobjects where name = 'usp_getWithoutTrade')drop proc usp_getWithoutTradeGOcreate procedure usp_getWithoutTrade @Num int output, @Amount decimal(18,2) output, @date1 datetime = NULL, @date2 datetime = NULLAS IF @date1 IS NULL BEGINdeclare @dateStr varchar(50)set @dateStr = convert(varchar(4),DATEPART(YY,GETDATE())) + '-'+convert(varchar(2),DATEPART(mm,GETDATE())) + '-1 00:00:00.000' set @date1 = convert(datetime, @datestr,101) END IF @date2 IS NULLSET @date2 = getdate() SELECT distinct u.customerID 客户号,u.customerName 客户姓名,u.PID 身份证号,u.telephone 电话,address 地址 FROM userInfo u JOIN cardInfo c ON u.customerID = c.customerID WHERE c.cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between @date1 and @date2) SELECT @Num=COUNT(customerID), @Amount=SUM(balance) FROM cardInfo WHERE cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between @date1 and @date2)GODECLARE @NUM intDECLARE @Amount decimal(18,2)DECLARE @date1 datetimeDECLARE @date2 datetimeSET @date1 = '2009-1-1'SET @date2 = getdate()EXEC usp_getWithoutTrade @NUM OUTPUT, @Amount OUTPUT--, @date1, @date2PRINT '统计未发生交易的客户'PRINT '---------------------------------------'PRINT '客户人数：' + CAST(@NUM AS varchar(10)) + ' 客户总余额：' + CAST(@Amount AS varchar(20))/*--7.统计银行卡交易量和交易额*/if exists (select * from sysobjects where name = 'usp_getTradeInfo')drop proc usp_getTradeInfoGOcreate procedure usp_getTradeInfo @Num1 int output, @Amount1 decimal(18,2) output, @Num2 int output, @Amount2 decimal(18,2) output, @date1 datetime, @date2 datetime = NULL, @address varchar(20) = NULLAS -- 初始化变量 SET @Num1 = 0 SET @Amount1 = 0 SET @Num2 = 0 SET @Amount2 = 0 IF @date2 IS NULLSET @date2 = getdate() IF @address IS NULL BEGIN SELECT @Num1=COUNT(tradeMoney), @Amount1=SUM(tradeMoney) FROM tradeInfo WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='存入' SELECT @Num2=COUNT(tradeMoney), @Amount2=SUM(tradeMoney) FROM tradeInfo WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='支取' END ELSE BEGIN SELECT @Num1=COUNT(tradeMoney), @Amount1=SUM(tradeMoney) FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID JOIN userInfo ON cardInfo.customerID = userInfo.customerID WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='存入' AND address Like '%'+@address+'%' SELECT @Num2=COUNT(tradeMoney), @Amount2=SUM(tradeMoney) FROM tradeInfo JOIN cardInfo ON tradeInfo.cardID = cardInfo.cardID JOIN userInfo ON cardInfo.customerID = userInfo.customerID WHERE tradeDate BETWEEN @date1 AND @date2 AND tradeType='支取' AND address Like '%'+@address+'%' ENDGO--测试DECLARE @CNT1 intDECLARE @Total1 decimal(18,2)DECLARE @CNT2 intDECLARE @Total2 decimal(18,2)DECLARE @date1 datetimeDECLARE @date2 datetimeSET @date1 = '2009-1-1'SET @date2 = getdate()EXEC usp_getTradeInfo @CNT1 OUTPUT, @Total1 OUTPUT, @CNT2 OUTPUT, @Total2 OUTPUT, @date1, @date2--, '北京'PRINT '统计银行卡交易量和交易额'PRINT ''PRINT '起始日期：' + CONVERT(varchar(10),@date1,102) + ' 截止日期：' + CONVERT(varchar(10),@date2,102)PRINT '-----------------------------------------------------------'PRINT '存入笔数：' + CAST(@CNT1 AS varchar(20)) + ' 存入金额：' + CAST(@Total1 AS varchar(20))PRINT '支取笔数：' + CAST(@CNT2 AS varchar(20)) + ' 支取金额：' + CAST(@Total2 AS varchar(20))PRINT '-----------------------------------------------------------'PRINT '发生笔数：' + CAST(@CNT1+@CNT2 AS varchar(20)) + ' 结余金额：' + CAST(@Total1-@Total2 AS varchar(20))GO--转帐的事务存储过程if exists (select * from sysobjects where name = 'usp_tradefer')drop proc usp_tradeferGOcreate procedure usp_tradefer @card1 char(19), @pwd char(6), @card2 char(19), @outmoney money AS DECLARE @date1 datetime DECLARE @date2 datetime SET @date1 = getdate() begin tran print '开始转帐，请稍后......' DECLARE @errors int set @errors=0 DECLARE @result int EXEC @result=usp_takeMoney @card1,@outmoney ,'支取',@pwd --'123123' set @errors=@errors+@@error if (@errors > 0 or @result <> 0)begin print '转帐失败！' rollback tran RETURN -1end EXEC @result=usp_takeMoney @card2,@outmoney ,'存入' set @errors=@errors+@@error if (@errors > 0 or @result <> 0) begin print '转帐失败！' rollback tran RETURN -1 end else begin print '转帐成功！' commit tran SET @date2 = getdate() print '打印转出账户对账单' PRINT '-------------------' EXEC usp_CheckSheet @card1,@date1,@date2 print '打印转入账户对账单' PRINT '-------------------' EXEC usp_CheckSheet @card2,@date1,@date2 RETURN 0 endGO--测试上述事务存储过程--从李四的帐户转帐2000到张三的帐户--同上一样,现实中的取款机依靠读卡器读出张三/李四的卡号,这里根据张三/李四的名字查出考号来模拟declare @card1 char(19),@card2 char(19)select @card1=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='李四'select @card2=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='张三'--调用上述事务过程转帐EXEC usp_tradefer @card1,'123123',@card2,2000select * from vw_userInfoselect * from vw_cardInfoselect * from vw_tradeInfoGO 0 0
- bankDB银行
- 创建银行数据库bankDB
- 创建BankDB银行数据库，创建表，插…
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- 银行
- ImageView保持前景src和ImageView的高度一致
- 古董华为3026交换机端口隔离抵御ARP的办法
- SQL 第九章
- ATM银行取款系统
- 【程序】STM32使用SPI接口读取93C46存储器上的数据（非软件模拟SPI时序）
- bankDB银行
- excel表格数据导入SQL数据库中的注意点
- 学C语言和英语水平的关系顶多不过半毛
- 第九章 银行
- ms16_032系统提权
- SameTree解法思路及源代码
- D.Marr视觉理论
- Android PreferenceFragment
- 【Spring】-- spring websocket样例
