第九章 银行
来源：互联网 发布：淘宝火拼 编辑：程序博客网 时间：2024/05/16 12:55
--创建建库bankDBCREATE DATABASE bankDB ON ( NAME='bankDB_data', FILENAME='F:\\bankDB_Mdf', SIZE=5mb, FILEGROWTH=15% ) LOG ON ( NAME= 'bankDB_log', FILENAME='F:\\bankDB_ldf', SIZE=2mb, FILEGROWTH=15% )GO/*$$$$$$$$$$$$$建表$$$$$$$$$$$$$$$$$$$$$$$$*/USE bankDBGOCREATE TABLE userInfo --用户信息表( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, PID CHAR(18) NOT NULL, telephone CHAR(20) NOT NULL, address VARCHAR(50))GOCREATE TABLE cardInfo --银行卡信息表( cardID CHAR(19) NOT NULL, curID VARCHAR(10) NOT NULL, savingID INT NOT NULL, openDate DATETIME NOT NULL, openMoney MONEY NOT NULL, balance MONEY NOT NULL, pass CHAR(6) NOT NULL, IsReportLoss BIT NOT NULL, customerID INT NOT NULL)GOCREATE TABLE tradeInfo --交易信息表( tradeDate DATETIME NOT NULL, tradeType CHAR(4) NOT NULL, cardID CHAR(19) NOT NULL, tradeMoney MONEY NOT NULL, remark TEXT )GOCREATE TABLE Deposit --存款类型表( savingID INT IDENTITY(1,1), savingName VARCHAR(20) NOT NULL, descrip VARCHAR(50))GO/*$$$$$$$$$$$$$加约束$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/ALTER TABLE Deposit ADD CONSTRAINT PK_savingID PRIMARY KEY(savingID)GO/* userInfo表的约束customerID顾客编号自动编号（标识列），从1开始，主键customerName开户名必填PID身份证号必填，只能是18位或15位，身份证号唯一约束telephone联系电话必填，格式为xxxx-xxxxxxxx或手机号13位address居住地址可选输入*/ALTER TABLE userInfo ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID), CONSTRAINT CK_PID CHECK( len(PID)=18 or len(PID)=15 ), CONSTRAINT UQ_PID UNIQUE(PID), --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][0-9]' or 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)=13 ) 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][0-9]' or 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 telephone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )GO/*cardInfo表的约束cardID卡号必填，主健 , 银行的卡号规则和电话号码一样，一般前8位代表特殊含义， 如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始curType货币必填，默认为RMBsavingType存款种类活期/定活两便/定期openDate开户日期必填，默认为系统当前日期openMoney开户金额必填，不低于1元balance余额必填，不低于1元,否则将销户pass密码必填，6位数字，默认为6个8IsReportLoss是否挂失 必填，是/否值，默认为”否”customerID顾客编号必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡*/ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID), CONSTRAINT CK_cardID CHECK(cardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'), CONSTRAINT DF_curID DEFAULT('RMB') FOR curID, --CONSTRAINT CK_savingType CHECK(savingType IN ('活期','定活两便','定期')), CONSTRAINT DF_openDate DEFAULT(getdate()) FOR openDate, CONSTRAINT CK_openMoney CHECK(openMoney>=1), CONSTRAINT CK_balance CHECK(balance>=1), CONSTRAINT CK_pass CHECK(pass LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'), CONSTRAINT DF_pass DEFAULT('888888') FOR pass, CONSTRAINT DF_IsReportLoss DEFAULT(0) FOR IsReportLoss, CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID), CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID)GO/* tradeInfo表的约束tradeType 必填，只能是存入/支取 cardID卡号必填，外健，可重复索引tradeMoney交易金额必填，大于0tradeDate交易日期必填，默认为系统当前日期remark备注可选输入，其他说明*/ALTER TABLE tradeInfo ADD CONSTRAINT CK_tradeType CHECK(tradeType IN ('存入','支取')), CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID), CONSTRAINT CK_tradeMoney CHECK(tradeMoney>0), CONSTRAINT DF_tradeDATE DEFAULT(getdate()) FOR tradeDateGO--存款类型INSERT INTO deposit (savingName,descrip) VALUES ('活期','按存款日结算利息')INSERT INTO deposit (savingName,descrip) VALUES ('定期一年','存款期是1年')INSERT INTO deposit (savingName,descrip) VALUES ('定期二年','存款期是2年')INSERT INTO deposit (savingName,descrip) VALUES ('定期三年','存款期是3年')INSERT INTO deposit (savingName) VALUES ('定活两便')INSERT INTO deposit (savingName) VALUES ('通知')INSERT INTO deposit (savingName,descrip) VALUES ('零存整取一年','存款期是1年')INSERT INTO deposit (savingName,descrip) VALUES ('零存整取二年','存款期是2年')INSERT INTO deposit (savingName,descrip) VALUES ('零存整取三年','存款期是3年')INSERT INTO deposit (savingName,descrip) VALUES ('存本取息五年','按月支取利息')SELECT * FROM DEPOSITINSERT INTO userInfo(customerName,PID,telephone,address ) VALUES('张三','123456789012345','010-67898978','北京海淀')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1234 5678',1,1000,1000,1)INSERT INTO userInfo(customerName,PID,telephone) VALUES('李四','321245678912345678','0478-44443333')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1212 1134',2,1,1,2)INSERT INTO userInfo(customerName,PID,telephone) VALUES('王五','567891234532124670','010-44443333')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1212 1130',2,1,1,3)INSERT INTO userInfo(customerName,PID,telephone) VALUES('丁六','567891321242345618','0752-43345543')INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID) VALUES('1010 3576 1212 1004',2,1,1,4)SELECT * FROM userInfoSELECT * FROM cardInfoGO/*张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。说明：当存钱或取钱（如300元）时候，会往交易信息表（tradeInfo）中添加一条交易记录， 同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少300元）*//*--------------交易信息表插入交易记录--------------------------*/INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('支取','1010 3576 1234 5678',900) /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010 3576 1234 5678'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('存入','1010 3576 1212 1130',300) /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance+300 WHERE cardID='010 3576 1212 1130'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('存入','1010 3576 1212 1004',1000) /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010 3576 1212 1004'INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('支取','1010 3576 1212 1130',1900) /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance+1900 WHERE cardID='010 3576 1212 1130'/*--------------交易信息表插入交易记录--------------------------*/INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES('存入','1010 3576 1212 1134',5000) /*-------------更新银行卡信息表中的现有余额-------------------*/UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010 3576 1212 1134'GO/*--------检查测试数据是否正确---------*/SELECT * FROM cardInfoSELECT * FROM tradeInfo------------------04-01-修改客户密码--1：修改张三update cardInfo set pass='123456'where cardID='1010 3576 1234 5678'--修改李四update cardInfo set pass='123123'where cardID='1010 3576 1212 1134'--2:李四挂失update cardInfo set IsReportLoss=1where cardID='1010 3576 1212 1134'--update cardInfo set isreportloss = 1-- where customerID = (select customerID from userInfo where customerName='李四')--3:统计银行资金流通金额和盈利结算declare @inMoney moneyselect @inMoney=SUM(tradeMoney) from tradeInfo where tradeType = '存入' declare @outMoney moneyselect @outMoney=SUM(tradeMoney) from tradeInfo where tradeType = '支取' print '银行流通余额总计为:'+convert(varchar(20),@inMoney-@outMoney) + 'RMB'print '盈利结算为:'+convert(varchar(20),@outMoney * 0.008-@inMoney * 0.003) + 'RMB'-- 4.查询本周开户信息select * from cardInfo where datediff(day,getDate(),openDate) < datepart(weekday,openDate) --5:查询本月交易金额最高的卡号SELECT distinct (cardID) from tradeInfowhere tradeMoney=(select MAX(tradeMoney)FROM tradeInfo WHERE tradeType = '存入' )-- 5.查询本月交易金额最高的卡号select top 1 cardID 卡号 ,max(tradeMoney) 最高交易金额from tradeInfo where datepart(mm,getDate())=datepart(mm,tradeDate)group by cardID order by max(tradeMoney)desc -- 6.查询挂失客户select * from userInfo where customerID in (select customerID from cardInfo where IsReportLoss = 1)select * from userInfo inner join cardInfo on userInfo.customerID=cardInfo.customerID where IsReportLoss = 1-- 7.催款提醒业务select customerName as 客户姓名,telephone as 联系电话,balance as 存款余额 from userInfoinner join cardInfo on userInfo.customerID=cardInfo.customerID where balance<200--创建,使用视图-- 1if exists (select * from sysobjects where name='vw_userInfo' ) DROP VIEW vw_userInfo GO CREATE VIEW vw_userInfo AS select customerID as 客户编号,customerName as 客户名,PID as 身份证号,address as 居住地 from userInfogo-- 2if exists (select * from sysobjects where name='vw_cardInfo' ) DROP VIEW vw_cardInfo GO CREATE VIEW vw_cardInfo AS select cardID as 卡号,customerName as 客户,curID as 货币种类,savingName as 存款类型,openDate as 开户日期,balance as 余额, pass as 密码,case IsReportLoss when 1 then '挂失' when 0 then '未挂失' end 是否挂失 from cardInfo,Deposit,userInfo where userInfo.customerID=cardInfo.customerID and cardInfo.savingID=Deposit.savingID go -- 3 if exists (select * from sysobjects where name='vw_transInfo' ) DROP VIEW vw_transInfo GO CREATE VIEW vw_transInfo AS select tradeDate as 交易日期,tradeType as 交易类型,cardID as 卡号,tradeMoney as 交易金额,remark as 备注 from tradeInfo go --1使用存储实现业务处理 --create procedure usp_takeMoney --@uptake int output --as create procedure usp_takeMoney @card char(19), @m money, @type char(4), @inputPass char(6)='' AS print '交易正进行,请稍后......' if (@type='支取') if ((SELECT pass FROM cardInfo WHERE cardID=@card)<>@inputPass ) begin raiserror ('密码错误!',16,1) return -1 end DECLARE @mytradeType char(4),@outMoney MONEY,@myCardID char(19) SELECT @mytradeType=tradeType,@outMoney=tradeMoney ,@myCardID=cardID FROM tradeInfo where cardID=@card DECLARE @mybalance money SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card if (@type='支取') if (@mybalance>=@m+1) update cardInfo set balance=balance-@m WHERE cardID=@myCardID else begin raiserror ('交易失败！余额不足！',16,1) print '卡号'+@card+' 余额：'+convert(varchar(20),@mybalance) return -2 end else update cardInfo set balance=balance+@m WHERE cardID=@card print '交易成功！交易金额：'+convert(varchar(20),@m) SELECT @mybalance=balance FROM cardInfo WHERE cardID=@card print '卡号'+@card+' 余额：'+convert(varchar(20),@mybalance) INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES(@type,@card,@m) RETURN 0GOdeclare @card char(19)select @card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='大豆'EXEC usp_takeMoney @card,10 ,'支取','123456' GOselect * from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName='黄豆'declare @card char(19)select @card=cardID from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID where customerName=' 小毛豆'EXEC usp_takeMoney @card,500 ,'存入'select * from vw_cardInfoselect * from vw_transInfo GOif exists (select * from sysobjects where name = 'usp_randCardID')drop proc usp_randCardIDgocreate procedure usp_randCardID @randCardID char(19) OUTPUT AS DECLARE @r numeric(15,8) DECLARE @tempStr char(10) SELECT @r=RAND((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) set @tempStr=convert(char(10),@r) set @randCardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4) --组合为规定格式的卡号GO --2用例6使用存储过程实现业务处理create procedure usp_randCardID@mycardID varchar(20) outputasdeclare @r numeric(15,8),@tempStr varchar(20)select @r=RAND ((DATEPArT(mm,GETDATE())*100000 )+(DATEPArt(ss,GETDATE())*1000)+(DATEPArt(ms,GETDATE())))set @tempStr=CONVERT(varchar(20),@r)set @mycardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4)godeclare @mycardID VARCHAR(19)execute usp_randCardID @mycardID outputprint '产生的随机卡号为'+@mycardID -- --完成开户业务--go-- create procedure use_oppenAcount-- @wwcardID varchar(20),-- @cardi varchar(20) output--as--declare @r numeric(15,8),--@tempStr varchar(20) +(DATEPArt(ms,GETDATE())))--set @tempStr=CONVERT(varchar(20),@r)--set @wwcardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4)--go--declare @wwcardID VARCHAR(19)--execute usp_randCardID @wwcardID output--if(@cardi.contains(select @cardID FOR tradeInfo WHERE ))--begin--print '尊敬的客户，开户成功！系统为您产生的随机卡号为'+@wwcardID+'开户日期'+tradeDate+'开户金额'+tradeMoney--end /*--3.开户的存储过程--*/if 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 0 0
- 第九章 银行
- 第九章 银行管理系统
- 第九章：银行ATM存取款机系统
- SQL高级 第九章 9 银行ATM存取款机系统
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- 第九章
- ATM银行取款系统
- 【程序】STM32使用SPI接口读取93C46存储器上的数据（非软件模拟SPI时序）
- bankDB银行
- excel表格数据导入SQL数据库中的注意点
- 学C语言和英语水平的关系顶多不过半毛
- 第九章 银行
- ms16_032系统提权
- SameTree解法思路及源代码
- D.Marr视觉理论
- Android PreferenceFragment
- 【Spring】-- spring websocket样例
- 10分钟学会缓存redis
- msfvenom反弹
- 【程序】STM32单片机操作ST93C46存储器的程序（3.2V电压）
