ATM银行取款系统

select * from [dbo].[cardInfo]
select * from [dbo].[Deposit]
select * from [dbo].[tradeInfo]
select * from [dbo].[userInfo]

--用例4  模拟常规业务
--1.修改用户密码
update [dbo].[cardInfo] set  [pass]='123456'where [cardID]='1010 3576 1212 1004'
update [dbo].[cardInfo] set  [pass]='123123'where [cardID]='1010 3576 1212 1134'

use bankDB
update [dbo].[cardInfo]
set  [pass]='123456'
where [cardID]='1010 3576 1234 5678' and [customerID]
in (select [customerID] from [dbo].[userInfo] where [customerName]='张三')
--2办理银行卡挂失
update [dbo].[cardInfo]
set [IsReportLoss]='1'
where [cardID]='1010 3576 1212 1134' and [customerID]
in (select [customerID] from [dbo].[userInfo] where [customerName]='李四')
go
select cardID as 卡号, curID as 货币, savingID as 储蓄种类, openDate as 开户日期,
 openMoney as 开户金额, balance as 余额, pass as 密码, IsReportLoss as 是否挂失,[customerName] as 客户姓名
from [dbo].[cardInfo] ,[dbo].[userInfo]
where  [dbo].[cardInfo].customerID=[dbo].[userInfo].customerID
 --3.统计银行资金流通余额和盈利结算
 declare @inMoney money
 declare @outMoney money
 declare @yuMoney money
 declare @yingMoney money
 select @outMoney=sum([tradeMoney]) from [dbo].[tradeInfo]
 where (tradeType='支取')
 select @inMoney=sum([tradeMoney]) from [dbo].[tradeInfo]
 where (tradeType='存入')
 set @yuMoney=@inMoney-@outMoney
 set @yingMoney=@outMoney*0.008-@inMoney*0.003
 print '银行流通余额总计为：'+convert(varchar(20),@yuMoney)+'      '+'盈利结算为：'+convert(varchar(20),@yingMoney)+'RMB'
 --4.查询本周开户信息
 select ,[customerName] as 客户姓名,curID as 货币,
 savingName as 存款类型,openDate as 开户日期,balance as 余额
 from [dbo].[cardInfo],[dbo].[Deposit],[dbo].[userInfo]
 where [dbo].[cardInfo].customerID=[dbo].[userInfo].customerID and [dbo].[cardInfo].savingID=[dbo].[Deposit].savingID

 --5.查询本月交易金额最高的卡号
 select  cardID as 卡号, [tradeMoney] as 交易金额
 from [dbo].[tradeInfo]
 where [tradeMoney] =(select max([tradeMoney]) from [dbo].[tradeInfo] where tradeType='支取'  )
 --6.查询挂失客户
 select cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss,[dbo].[cardInfo]. customerID,
 [dbo].[userInfo].customerID, customerName as 客户姓名, PID, telephone, address
 from [dbo].[cardInfo],[dbo].[userInfo]
 where [dbo].[cardInfo].[customerID]=[dbo].[userInfo].[customerID] and
 [dbo].[cardInfo].customerID in (select customerID from [dbo].[cardInfo] where [IsReportLoss]='1')
 --7.催款提醒业务（小余200元）
 select customerName as 客户姓名,telephone as 联系电话,balance as 存款余额
 from [dbo].[cardInfo],[dbo].[userInfo]
 where [dbo].[cardInfo].[customerID]=[dbo].[userInfo].[customerID]
 and  balance<200
 --用例5   创建，使用视图

 create view vw_userInfo
 as
 select [dbo].[cardInfo].[customerID]  as 客户编号, customerName as 客户姓名,cardID as 身份证号,address as 居住地址
  from [dbo].[cardInfo],[dbo].[userInfo]
 where [dbo].[cardInfo].[customerID]=[dbo].[userInfo].[customerID]
  order by [dbo].[cardInfo].[customerID]

  create view vw_cardInfo
  as
  select cardID  as 卡号, customerName as 客户,curID as 货币种类,savingID as 存款类型,
  openDate as 开户日期,balance as 余额,pass as 密码,IsReportLoss as 是否挂失
 from [dbo].[cardInfo],[dbo].[userInfo]
 where [dbo].[cardInfo].[customerID]=[dbo].[userInfo].[customerID]
  

  create view vw_transInfo
  as
  select tradeDate as 交易日期,tradeType as 交易类型,cardID as 卡号,tradeMoney as 交易金额,remark as 备注
  from [dbo].[tradeInfo]

  --用例6   使用存储过程实现业务处理

  --1.完成存款或取款业务(procedure)
  create proc usp_takeMoney
 as
  declare @cunkuan money
  declare @qukuan money
  declare @zongcunkuan money
  declare @zongqukuan money

  --------------------------------------------------------------------------------------------------------------------------------、
  ---------------------------------------------------------------------------------------------------------------------------------、

  --1.完成存款或取款业务
  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 0
GO

declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
   cardInfo.customerID=userInfo.customerID where customerName='张三'
EXEC usp_takeMoney @card,10 ,'支取','123456'
GO

select * 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_cardInfo
select * from vw_tradeInfo
GO

--2.产生随机号
if exists (select * from sysobjects where name = 'usp_randCardID')
 drop proc usp_randCardID
go
create 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
--测试产生随机卡号
DECLARE @mycardID char(19)
EXECUTE usp_randCardID @mycardID OUTPUT   --exec 和execute 是一样的  执行的意思
print '产生的随机卡号为：'+@mycardID
GO

--3.完成开户业务
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

 if @savingID is NULL
  BEGIN
   RAISERROR('存款类型不正确,请重新输入!',16,1)
   RETURN -1
  END

    select @cur_customerID=customerID from userInfo where PID=@PID

    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

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'

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
  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)'

  EXEC SP_EXECUTESQL @statement,N'@rec1 int,@rec2 int',@rec1,@rec2
GO
--
EXEC usp_pagingDisplay 2,2

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

--用例7
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))

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

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

--1.添加SQL登录帐号
CREATE LOGIN appAdmin WITH PASSWORD='bank1234'
GO
--2.创建数据库用户
USE bankDB
GO
CREATE USER appAdmin FOR LOGIN appAdmin
GO
--3. 给数据库用户appAdmin授权(增删改查的权限)
 GRANT select,insert,update,delete,select  ON vw_userInfo
                TO appAdmin
 GRANT select,insert,update,delete,select ON vw_tradeInfo
                TO appAdmin
 GRANT select,insert,update,delete,select ON vw_cardInfo
                TO appAdmin
GO