bankDB银行

--用例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.003
print   '盈利结算为:'+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.customerID
where cardInfo.balance<200


--创建 使用视图

--银行客户记录视图
go
create view vw_userInfo 
as
select customerID as 客户编号,customerName as 开户名,PID as 身份证号,telephone as 电话号码,address as 居住地址
from userInfo
go
select * from vw_userInfo


--银行卡记录视图
go
create view vw_cardInfo
as
select cardID as 卡号, userInfo.customerName as 客户,curID as 货币类型,Deposit.savingName as 存款类型 ,openDate as 开户日期,
balance as 余额,pass as 密码,IsReportLoss as 是否挂失 from cardInfo
inner join userInfo on userInfo.customerID=cardInfo.customerID
inner join Deposit on Deposit.savingID=cardInfo.savingID
go
select * from vw_cardInfo


--银行卡的交易记录视图
go 
create view vw_tradeInfo
as
select tradeDate as 交易日期,tradeType as 交易类型,cardID as 卡号,tradeMoney as 交易金额,remark as 备注 from tradeInfo
go 
select * from vw_tradeInfo



--用例6   使用存储过程实现业务处理
--1
if exists(select * from sysobjects where name='proc_inout')
drop proc proc_inout
go
create proc proc_inout
@cardid varchar(19),
@pass varchar(6),
@transtype varchar(4),
@transmoney money
as
set nocount on
declare @sumerror int
set @sumerror=0
if 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
 end
else
 begin
  print '您输入的卡号并不存在！'
 end
go
exec proc_inout '1010 3576 1234 5678','123456','存入',2000
exec proc_inout '1010 3576 1212 1134','123123','支取',2000
select*from vw_cardInfo
select*from vw_userInfo
select*from vw_tradeInfo
 

 --产生随机卡号·
if exists(select * from sysobjects where name='usp_randCardID')
drop proc usp_randCardID
go
create proc usp_randCardID
@randCardID varchar(19) output
as
declare @r numeric(15,8),
@tempStr varchar(10)
    --产生随机数,如:0.55181485
select @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)
go
declare @mycardID char(19)
exec usp_randCardID @mycardID output
print '产生的随机卡号为: ' +@mycardID


if exists (select * from sysobjects where name = 'usp_openAccount')
	drop proc usp_openAccount
GO
create 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_userInfo
select * from vw_cardInfo
GO


/*---- 4.打印对账单 ----*/
if exists (select * from sysobjects where name = 'usp_CheckSheet')
	drop proc usp_CheckSheet
GO
CREATE PROCEDURE usp_CheckSheet
  @cardID varchar(19),
  @date1 datetime=NULL,
  @date2 datetime=NULL
AS
	DECLARE @custName varchar(20)
	DECLARE @curName varchar(20)
	DECLARE @savingName varchar(20)
	DECLARE @openDate datetime
	SELECT @cardID=c.cardID, @curName=c.curID, @custName=u.customerName,
		   @savingName=d.savingName , @openDate=c.openDate 
	FROM cardInfo c, userInfo u, deposit d
	WHERE c.customerID=u.customerID and c.savingID = d.savingID and cardID = @cardID --and u.customerName = user_name()
	PRINT '卡号：' + @cardID
	PRINT '姓名:' + @custName
	PRINT '货币:' + @curName
	PRINT '存款类型：' + @savingName
	PRINT '开户日期：' + 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 
      BEGIN
		SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注
		FROM tradeInfo
		WHERE cardID='1010 3576 1212 1134'--@cardID
		ORDER BY tradeDate
		RETURN
      END
    ELSE IF @date2 IS NULL 
      SET @date2 = getdate()
      
	SELECT tradeDate 交易日, tradeType 类型, tradeMoney 交易金额, remark 备注
	FROM tradeInfo
	WHERE cardID=@cardID AND tradeDate BETWEEN @date1 AND @date2
	ORDER BY tradeDate
GO
--测试打印对帐单
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_pagingDisplay
GO
CREATE PROCEDURE usp_pagingDisplay
  @records int = 10,
  @page int = 1
AS
  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,@rec2
GO
--
EXEC usp_pagingDisplay 2,2


/*--6.查询、统计在指定时间段内没有发生交易的账户信息*/
if exists (select * from sysobjects where name = 'usp_getWithoutTrade')
	drop proc usp_getWithoutTrade
GO
create procedure usp_getWithoutTrade
  @Num int output,
  @Amount decimal(18,2) output,
  @date1 datetime = NULL,
  @date2 datetime = NULL
AS
  IF @date1 IS NULL
  BEGIN
	declare @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 NULL
	SET @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)
GO

DECLARE @NUM int
DECLARE @Amount decimal(18,2)
DECLARE @date1 datetime
DECLARE @date2 datetime
SET @date1 = '2009-1-1'
SET @date2 = getdate()
EXEC usp_getWithoutTrade @NUM OUTPUT, @Amount OUTPUT--, @date1, @date2
PRINT '统计未发生交易的客户'
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_getTradeInfo
GO
create 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) = NULL
AS
  -- 初始化变量
  SET @Num1 = 0
  SET @Amount1 = 0

  SET @Num2 = 0
  SET @Amount2 = 0

  IF @date2 IS NULL
	SET @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+'%'
    END
GO

--测试
DECLARE @CNT1 int
DECLARE @Total1 decimal(18,2)
DECLARE @CNT2 int
DECLARE @Total2 decimal(18,2)
DECLARE @date1 datetime
DECLARE @date2 datetime
SET @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_tradefer
GO
create 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 -1
			end
     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
        end
GO

--测试上述事务存储过程
--从李四的帐户转帐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,2000

select * from vw_userInfo
select * from vw_cardInfo
select * from vw_tradeInfo
GO