第九章：银行ATM存取款机系统

--------------------创建库bankDB--------------------
if exists(select * from sysdatabases where name='bankDB')
drop database bankDB
go
CREATE DATABASE bankDB
 ON
 (
  NAME='bankDB_data',
  FILENAME='F:\bankDB_data.mdf',
  SIZE=3mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= 'bankDB_log',
  FILENAME='F:\bankDB_log.ldf',
  SIZE=3mb,
  FILEGROWTH=15%
 )
GO
--------------------建表--------------------

USE bankDB
GO

CREATE 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)
)
GO

CREATE 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
)
GO

CREATE TABLE tradeInfo  --交易信息表
(
  tradeID int identity(1,1) not null,
  tradeDate  DATETIME NOT NULL,
  tradeType  CHAR(4) NOT NULL,
  cardID  CHAR(19) NOT NULL,
  tradeMoney  MONEY NOT NULL,
  remark  TEXT  
)
GO

CREATE 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

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


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

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 tradeDate
GO

--------------------插入数据--------------------

--存款类型
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 DEPOSIT

INSERT 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 userInfo
SELECT * FROM cardInfo
GO



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 cardInfo
select * from Deposit
SELECT * FROM tradeInfo
select * from userInfo

--------------用例4:模拟常规业务-----------------------
---1.修改客户密码
--修改张三(卡号为1010 3576 1234 5678)银行卡密码为123456
update cardInfo set pass='123456'
where cardID='1010 3576 1234 5678' and customerID=(select customerID from userInfo where customerName='张三')
go

--修改李四(卡号为1010 3576 1212 1134)银行卡密码为123123
update cardInfo set pass='123123'
where cardID='1010 3576 1212 1134' and customerID=(select customerID from userInfo where customerName='李四')
go


--2.办理银行卡挂失
update cardInfo set IsReportLoss=1
where cardID='1010 3576 1212 1134' and customerID=(select customerID from userInfo where customerName='李四')
go

--修改密码和办理银行卡的结果
select cardID 卡号, curID 货币,de.savingName 储蓄种类,openDate 开户日期,
 openMoney 开户金额,balance 余额,pass 密码,IsReportLoss 是否挂失,us.customerName 客户姓名
from cardInfo ca
inner join Deposit de on ca.savingID=de.savingID
inner join userInfo us on ca.customerID=us.customerID
go

--3.统计银行资金流通余额和盈利结算
declare @inMoney money
declare @outMoney money
select @inMoney= SUM(tradeMoney) from tradeInfo where tradeType='存入'
select @outMoney=SUM(tradeMoney) from tradeInfo where tradeType='支取'
print '银行流通余额总计为：'+cast((@inMoney-@outMoney) as varchar(20))+'RMB'
print '盈利结算为：'+cast((@outMoney*0.008-@inMoney*0.003) as varchar(20))+'RMB'
go

--4.查询本周开户信息
--查询本周开户的卡号，显示该卡的相关信息
select  cardID 卡号,us.customerName 客户姓名, curID 货币,de.savingName 储蓄种类,openDate 开户日期,
 openMoney 开户金额,balance 余额,IsReportLoss 是否挂失
from cardInfo ca
inner join Deposit de on ca.savingID=de.savingID
inner join userInfo us on ca.customerID=us.customerID
where DATEDIFF(week, openDate,GETDATE())=0
go

--5.查询本月交易金额最高的卡号
select  cardID 卡号,us.customerName 客户姓名, curID 货币,de.savingName 储蓄种类,openDate 开户日期,
 openMoney 开户金额,balance 余额,IsReportLoss 是否挂失
from cardInfo ca
inner join Deposit de on ca.savingID=de.savingID
inner join userInfo us on ca.customerID=us.customerID
where cardID=(
 select cardID from tradeInfo
 where  tradeMoney=
  (
   select Max(tradeMoney)
    from tradeInfo 
    where DATEDIFF(MONTH, tradeDate,GETDATE())=0
   )
  and DATEDIFF(MONTH, tradeDate,GETDATE())=0
 )
 go

--6.查询挂失客户
--查询挂失账户的客户信息
select  cardID 卡号,us.customerName 客户姓名, curID 货币,de.savingName 储蓄种类,openDate 开户日期,
 openMoney 开户金额,balance 余额,IsReportLoss 是否挂失
from cardInfo ca
inner join Deposit de on ca.savingID=de.savingID
inner join userInfo us on ca.customerID=us.customerID
where IsReportLoss=1
go


--9.催款提醒业务
select us.customerName 客户姓名,us.telephone 电话,ca.balance 存款余额 from userInfo us
inner join cardInfo ca on us.customerID=ca.customerID  where ca.balance<200 and GETDATE()=(
 SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))  )
go

----------------用例5：创建，使用视图--------------------
--创建视图vw_userInfo:输出银行客户记录
if exists(select * from sysobjects where name='vw_userInfo')
drop view vw_userInfo
go
create view vw_userInfo
as
  select customerID 客户编号,customerName 客户姓名,PID 身份证号,telephone 电话号码,address 居住地址 from userInfo
go


--创建视图vw_cardInfo:输出银行卡记录
if exists(select * from sysobjects where name='vw_cardInfo')
drop view vw_cardInfo
go
create view vw_cardInfo
as
  select cardID 卡号,us.customerName 客户姓名, curID 货币,de.savingName 储蓄种类,openDate 开户日期,
    balance 余额,pass 密码,IsReportLoss 是否挂失
  from cardInfo ca
  inner join Deposit de on ca.savingID=de.savingID
  inner join userInfo us on ca.customerID=us.customerID
go

--创建视图vw_transInfo:输出银行卡的交易记录
if exists(select * from sysobjects where name='vw_transInfo')
drop view vw_transInfo
go
create view vw_transInfo
as
  select tradeDate 交易时间,tradeType 交易类型,cardID 卡号,tradeMoney 交易金额,remark 备注 from tradeInfo
go

--创建视图vw_oneUserInfo:根据客户登录名查询该客户账户信息
if exists(select * from sysobjects where name='vw_oneUserInfo')
drop view vw_oneUserInfo
go
create view vw_oneUserInfo
as
    select customerID 客户编号,customerName 客户姓名,PID 身份证,telephone 联系电话,address 居住地址
 from userInfo where customerName=SYSTEM_USER
go

--查看创建的视图
select * from vw_userInfo
select * from vw_cardInfo
select * from vw_transInfo
select * from vw_oneUserInfo
go

 

-----------------------------用例6：使用存储过程实现业务处理---------------------
--1.完成存款或取款业务

if exists(select * from sysobjects where name='usp_takeMoney')
drop procedure usp_takeMoney
go
create procedure usp_takeMoney
 @cardID char(19) output, --卡号
 @balance money output,  --余额
 @customerName varchar(20) ,
 @tradeMoney money,   --交易金额
 @pass varchar(20)=null
as
 print '交易正进行，请稍后..........'
 if(@customerName is null or @tradeMoney is null)
 begin
  print '请确认账户姓名和交易金额'
  return -1
 end
 declare @error int
 set @error=0   --统计错误数
 
 --查出客户的卡号
    select @cardID=cardID from cardInfo where customerID=(select customerID from userInfo where customerName=@customerName)
   
 if(@pass is null)  --存款
  begin
   begin transaction
   update cardInfo set balance=balance+@tradeMoney where customerID=(select customerID from userInfo where customerName=@customerName)
   set @error=@error+@@ERROR
   insert into tradeInfo values(GETDATE(),'存入',(select cardID from cardInfo where customerID=(select customerID from userInfo where customerName=@customerName)),@tradeMoney,null)
   set @error=@error+@@ERROR
   if(@error=0)
   begin  
    commit transaction
   end
   else
   begin
    raiserror('交易失败！',16,1)
    rollback transaction
   end
  
  end
 else    --取款
  begin
   if(@pass=(select pass from cardInfo where customerID=(select customerID from userInfo where customerName=@customerName)))--判断密码相等
   begin
    if(@tradeMoney+1<=(select balance from cardInfo where customerID=(select customerID from userInfo where customerName=@customerName)))--判断取款金额小于余额
    begin
     begin transaction   --开始事务
     update cardInfo set balance=balance-@tradeMoney where customerID=(select customerID from userInfo where customerName=@customerName)
     set @error=@error+@@ERROR
     insert into tradeInfo values(GETDATE(),'支取',(select cardID from cardInfo where customerID=(select customerID from userInfo where customerName=@customerName)),@tradeMoney,null)
     set @error=@error+@@ERROR
     if(@error=0)
     begin  
      commit transaction
     end
     else
     begin
      raiserror('交易失败！',16,1)
      rollback transaction
     end
    end
    else
    begin
     raiserror('交易失败！余额不足！',16,1)
    end
   end
   else
   begin
    raiserror('密码错误！请重新确认密码',16,1)
   end
  end
  select @balance=balance from cardInfo where customerID=(select customerID from userInfo where customerName=@customerName)
 
go

--调用存取款存储过程
declare  @cardID char(19)
declare @balance money
declare @customerName varchar(20)
set @customerName='张三'
declare @tradeMoney money 
set @tradeMoney=500 
declare @pass varchar(20)
set @pass=123456

exec usp_takeMoney @cardID output,@balance output,@customerName,@tradeMoney,@pass
print '卡号：'+cast(@cardID as varchar(20))+'  余额：'+cast(@balance as varchar(20))
go


--2.产生随机卡号

if exists(select * from sysobjects where name='usp_randCardID')
drop procedure usp_randCardID
go
create procedure usp_randCardID
 @cardID 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=CAST(@r as CHAR(19))
 set @cardID='1010 3576 '+SUBSTRING(@tempStr,3,4)+' '+SUBSTRING(@tempStr,7,4)
go

--调用产生随机数的存储过程
declare @mycardID char(19)
execute usp_randCardID @mycardID output
print '产生的随机卡号为：'+cast(@mycardID as char(19))
go

--3.完成开户业务
if exists(select * from sysobjects where name='usp_openAccount')
drop procedure usp_openAccount
go
create procedure usp_openAccount
 @custorName char(8),    --开户名
 @PID char(18),      --身份证号
 @phone char(20) ,     --电话号码
 @openAccountMoney money,   --开户金额
 @savingName varchar(20),   --存款类型
 @address varchar(20)    --地址
as
 declare @error int
 set @error=0
 declare @mycardID char(19)
 execute usp_randCardID @mycardID output
 while exists(select * from cardInfo where cardID=@mycardID)
 begin
  execute usp_randCardID @mycardID output
 end
 begin transaction
 if not exists(select * from userInfo where customerName=@custorName)
 begin
  insert into userInfo values(@custorName,@PID,@phone,@address)  --客户表插数据
  set @error=@error+@@ERROR
  insert into cardInfo values(@mycardID,default,(select savingID from Deposit where savingName=@savingName),GETDATE(),@openAccountMoney,@openAccountMoney,default,default,(select MAX(customerID) from userInfo))
  set @error =@error+@@ERROR
 end
 else
 begin
  insert into cardInfo values(@mycardID,default,(select savingID from Deposit where savingName=@savingName),GETDATE(),@openAccountMoney,@openAccountMoney,default,default,(select MAX(customerID) from userInfo))
  set @error =@error+@@ERROR
 end
 if(@error=0)
 begin
  print '尊敬的客户，开户成功！系统为你产生的随机卡号为：'+@mycardID
  print '开户日期：'+ cast(getdate() as varchar(20))+' 开户金额 ：'+cast(@openAccountMoney as varchar(20))
  commit transaction
 end
 else
 begin
  raiserror('开户失败！',16,1)
  rollback transaction
 end
go

--调用开户存储过程
declare @custorName char(8)   --开户名
set @custorName='小紫'
declare @PID char(18)    --身份证号
set @PID='360323199905254567'
declare @phone char(20)    --电话号码
set @phone='010-22783256'
declare @openAccountMoney money  --开户金额
set @openAccountMoney=100
declare @savingName varchar(20)  --存款类型
set @savingName='活期'
declare @address varchar(20)  --地址
set @address='广东深圳'

execute usp_openAccount @custorName,@PID,@phone,@openAccountMoney,@savingName,@address
go


--4.分页显示查询交易数据
if exists(select * from sysobjects where name='usp_pagingDisplay')
drop procedure usp_pagingDisplay
go
create procedure usp_pagingDisplay
 @pageNumber int,  --页数
 @lineNumber int   --行数
as
 select top (@lineNumber) tradeDate 交易日期,tradeType 交易类型,cardID 卡号,tradeMoney 交易金额 from tradeInfo
 where  tradeID not in(select top ((@pageNumber-1)*@lineNumber) tradeID from tradeInfo order by tradeDate desc ) order by tradeDate desc
go


--调用分页显示存储过程
declare @pageNumber int      --页数
set @pageNumber=2
declare @lineNumber int   --行数
set @lineNumber=2
execute usp_pagingDisplay @pageNumber,@lineNumber
go

select * from tradeInfo
go


--5.打印客户对账单
if exists(select * from sysobjects where name='usp_CheckSheet')
drop procedure usp_CheckSheet
go
create procedure usp_CheckSheet
 @cardID char(19),  --账号
 @startTime datetime=null,  --时间段的开始时间
 @endTime datetime=null   --时间段的结束时间
as
 if(@endTime is null)
 begin
  set @endTime=GETDATE()
 end
 print '卡号：'+@cardID
 declare @customerName varchar(20)
 declare @curID varchar(20)
 declare @savingName varchar(20)
 declare @openDate datetime
 select @openDate= openDate,@curID=curID ,
 @savingName=(select savingName  from Deposit where savingID=(select savingID from cardInfo where cardID=@cardID)),
 @customerName=(select customerName  from userInfo where customerID=(select  customerID from cardInfo where cardID=@cardID))
 from cardInfo where cardID=@cardID
 
 print '姓名：'+@customerName
 print '货币：'+@curID
 print '存款类型：'+@savingName
 print '开户日期：'+convert(varchar(20), @openDate)
 if(@startTime is null)
 begin
  select tradeDate 交易日期,tradeType 交易类型,tradeMoney 交易金额,remark 备注 from tradeInfo where cardID=@cardID
 end
 else
 begin
  select tradeDate 交易日期,tradeType 交易类型,tradeMoney 交易金额,remark 备注 from tradeInfo where cardID=@cardID and tradeDate between @startTime and @endTime
 end
go
--调用打印客户对账单的存储过程
declare @cardID char(19)
set @cardID='1010 3576 1212 1134'
execute usp_CheckSheet @cardID
go


--6.统计未发生交易的帐户
if exists(select * from sysobjects where name='usp_getWithoutTrade')
drop procedure usp_getWithoutTrade
go
create procedure usp_getWithoutTrade
 @startTime datetime =null,  --起始日期
 @endTime datetime=null   --截至日期
as
 if(@startTime is null)
 begin
  set @startTime=CONVERT(datetime, convert(char(6),getdate(),112)+'01')
 end
 if(@endTime is null)
 begin
  set @endTime=getdate()
 end
 select customerID 客户号,customerName 客户姓名,PID 身份证号,telephone 电话,address 地址
 from userInfo
 where customerID in
  (
  select customerID from cardInfo
  where cardID not in
   (
   select cardID from tradeInfo
   where tradeDate between @startTime  and @endTime
   )
  )
 print '统计未发生交易的客户'
 select count(customerID) 客户人数  from userInfo
 where customerID in
  (
  select customerID from cardInfo
  where cardID not in
   (
   select cardID from tradeInfo
   where tradeDate between @startTime  and @endTime
   )
  )
 
 select SUM(balance) 客户总金额 from cardInfo
  where cardID not in
   (
   select cardID from tradeInfo
   where tradeDate between @startTime  and @endTime
   )
go

--调用统计未发生交易的帐户存储过程
declare @startTime datetime
declare @endTime datetime
execute usp_getWithoutTrade @startTime ,@endTime
go

--7.统计银行卡交易量和交易额
if exists(select * from sysobjects where name='usp_getTradeInfo')
drop procedure usp_getTradeInfo
go
create procedure usp_getTradeInfo
 @address varchar(20)=null,   --地区
 @startTime datetime =null,  --起始日期
 @endTime datetime=null   --截至日期
as 
 declare @cun money   --存入金额
 declare @qu money   --支取金额
 declare @zong int   --发生交易笔数
 if(@startTime is null)
 begin
  set @startTime=CONVERT(datetime, convert(char(4),getdate(),112)+'0101')
 end
 if(@endTime is null)
 begin
  set @endTime=GETDATE()
 end
 print '统计银行卡交易量和交易额'
 print '起始日期：'+convert(varchar(20), @startTime)
 print '截至日期：'+convert(varchar(20), @endTime)
 if(@address is null)
 begin
  select tradeType 存入类型, COUNT(*) 笔数,SUM(tradeMoney) 金额 from tradeInfo
  where tradeDate between @startTime and @endTime
  group by tradeType
  
  select @zong= COUNT(*) from  tradeInfo where tradeDate between @startTime and @endTime
  select @cun=SUM(tradeMoney) from  tradeInfo
  where tradeDate between @startTime and @endTime and tradeType='存入'
  select @qu=SUM(tradeMoney) from  tradeInfo
  where tradeDate between @startTime and @endTime and tradeType='支取'
  print '发生的笔数：'+convert(varchar(20),@zong)+'结余金额：'+convert(varchar(20),(@cun-@qu))
 end
 else
 begin
  select tradeType 存入类型, COUNT(*) 笔数,SUM(tradeMoney) 金额 from tradeInfo
  where cardID in
  (
   select cardID from cardInfo where customerID in
     (
   select customerID from userInfo where address like '%'+@address+'%'
   )
  )  and tradeDate between @startTime and @endTime 
  group by tradeType
  
  
  select @zong= COUNT(*) from  tradeInfo where cardID in
  (
   select cardID from cardInfo where customerID in
     (
   select customerID from userInfo where address like '%'+@address+'%'
   )
  )  and tradeDate between @startTime and @endTime
  
  
  select @cun=SUM(tradeMoney) from  tradeInfo
  where cardID in
  (
   select cardID from cardInfo where customerID in
     (
   select customerID from userInfo where address like '%'+@address+'%'
   )
  )  and tradeDate between @startTime and @endTime and tradeType='存入'
  
  select @qu=SUM(tradeMoney) from  tradeInfo
  where cardID in
  (
   select cardID from cardInfo where customerID in
     (
   select customerID from userInfo where address like '%'+@address+'%'
   )
  )  and tradeDate between @startTime and @endTime  and tradeType='支取'
  print '发生的笔数：'+convert(varchar(20),@zong)+'结余金额：'+convert(varchar(20),(@cun-@qu))
 end
go


--调用统计银行卡交易量和交易额的存储过程
declare @address varchar(20)
declare @startTime datetime
declare @endTime datetime
execute usp_getTradeInfo @address,@startTime,@endTime

 


--------------------------用例7:利用事务实现较复杂的数据更新------------------------------
--使用事务和存储过程实现转账业务
if exists(select * from sysobjects where name='usp_tradefer')
drop procedure usp_tradefer
go
create procedure usp_tradefer
 @quCardID char(19),  --支取帐号
 @quPass varchar(20), --支取密码
 @cunCardID char(19), --存入帐号
 @tradeMoney money  --交易金额
as
 print '开始转账，请稍候.......'
 declare @error int
 set @error=0
 declare @startTime datetime
 set @startTime=GETDATE()
 begin transaction
 if(@quPass=(select pass from cardInfo where cardID=@quCardID))--判断密码相等
  begin
   if(@tradeMoney+1<=(select balance from cardInfo where cardID=@quCardID ))--判断取款金额小于余额
    begin
     print '交易正进行，请稍候.............'
     --支取
     update cardInfo set balance=balance-@tradeMoney where cardID=@quCardID
     set @error=@error+@@ERROR
     insert into tradeInfo values(GETDATE(),'支取',@quCardID,@tradeMoney,default)
     set @error=@error+@@ERROR
     --存入
     update cardInfo set balance=balance+@tradeMoney where cardID=@cunCardID
     set @error=@error+@@ERROR
     
     insert into tradeInfo values(GETDATE(),'存入',@cunCardID,@tradeMoney,default)
     set @error=@error+@@ERROR
     
     if(@error<1)
      begin
       commit transaction
       print '转账成功！'
       print '打印转出账户对账单'
       
       execute usp_CheckSheet @quCardID, @startTime
       print '打印转入帐户对账单'
       execute usp_CheckSheet @cunCardID, @startTime
      end
     else
      begin
       raiserror('转账失败！',16,1)
       rollback transaction
      end
    end
   else
    begin
     raiserror('卡内余额不足！转账失败！',16,1)
     rollback transaction
    end
  end
 else
  begin
   raiserror('密码错误！',16,1)
   rollback transaction
  end 
go


--调用转账存储过程
declare @quCardID char(19) --支取帐号
set @quCardID='1010 3576 1234 5678'
declare @quPass varchar(20) --支取密码
set @quPass='123456'
declare @cunCardID char(19) --存入帐号
set @cunCardID='1010 3576 1212 1134'
declare @tradeMoney money
set @tradeMoney=1000

execute usp_tradefer @quCardID,@quPass,@cunCardID ,@tradeMoney
go