SQL高级第九章 9 银行ATM存取款机系统

最新推荐文章于 2023-09-22 17:56:58 发布

qq_36074362

最新推荐文章于 2023-09-22 17:56:58 发布

阅读量1.3k

点赞数

分类专栏： SQL高级

本文链接：https://blog.csdn.net/qq_36074362/article/details/56479103

版权

本文展示了如何使用SQL存储过程实现银行ATM系统的存取款功能，包括验证密码、检查余额、记录交易历史等操作。通过创建一系列的存储过程，实现了包括支取、存入、开户、打印交易对账单和转账等功能，确保了ATM交易的安全性和准确性。

摘要由CSDN通过智能技术生成

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


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
print '产生的随机卡号为：'+@mycardID
GO



if exists (select * from sysobjects where name = 'usp_openAccount')
	drop pro