SQL 第九章

/*--1.取钱或存钱的存储过程*/
if exists (select * from sysobjects where name = 'usp_takeMoney')
	drop proc usp_takeMoney
go
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
--调用存储过程取钱或存钱 张三取300，李四存500
 --现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟
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.产生随机卡号的存储