T-sql 简易模拟ATM机 SQL存储过程和触发器回滚机制应用

--创建ATM数据库 在这就不进行判断了
create database atm on(
--创建主数据文件
 name="atms1",
 fileName="D:\blanks",
 size=100,
 maxsize=1000,
 fileGrowth=15%
)log on(
--创建日志文件
 name="Atm_log",
 fileName="D:\blankss"
)
go

use atm
--创建用户表
create table userInfo(
customerID int primary key identity(10000000,1),
customerName varchar(10) not null,
PID varchar(20) check(len([PID])=15 or len([PID])=18) not null ,
telephone varchar(15) not null,
address varchar(50)
)
go
--创建银行卡信息表cardInfo
create table cardInfo(
cardID varchar(30) primary key,
curType varchar(10) default 'RMB' not null ,
savingType varchar(5),
openDate datetime default getdate() not null,
openMoney money check ( openMoney >=1 )not null,
balance money check (balance>=1) not null,
pass varchar(16) default '888888' not null,
IsReportLoss varchar(2)  default '否' not null,
customerID int not null foreign key references userInfo(customerID)
)

--创建交易信息表
create table transInfo(
transDate datetime default getdate() not null,
cardID varchar(30) not null foreign key references cardInfo(cardID),
transType varchar(5) not null,
transMoney money not null,
remark varchar(100) 
)
go

 

/*
------------------------------------------------------
------------------------------------------------------
----------以下是创建存储过程和触发器------------------
------------------------------------------------------
------------------------------------------------------
*/
--创建用户存储过程

CREATE PROCEDURE proc_adduser(
  @name char(19),
  @pid varchar(20),
  @mobile char(18),
  @addresss varchar(30)
)as
    insert into userInfo values(@name,@pid,@mobile,@addresss)
go
--=====================================================
--=====================================================

 

 

--创建银行卡信息表存储过程
create procedure proc_addmessage(
 @cardID varchar(30),
 @curType varchar(10),
 @savingType varchar(5),
 @openMoney money ,
 @balance money ,
 @pass varchar(16),
 @IsReportLoss varchar(2),
 @customerID int
)
as
 if not exists(select * from userInfo where customerID =@customerID)
  begin
   raiserror('您好！您输入的用户不存在',16,1)
  end
  else
  begin
   insert into  cardInfo values(@cardID,@curType,@savingType,getdate(),@openMoney,@balance,@pass,@IsReportLoss,@customerID)
   print('恭喜您！'+convert(varchar(30),@cardID)+'银行卡开户成功')
  end

go

--===========================================================
--===========================================================

--创建交易触发器
create trigger tri_cardInfo
on cardInfo
for update
as
     declare @date datetime
  declare @cardId varchar(30)
  declare @transtype varchar(10)
  declare @trinsmoney money --存款后
  declare @remark varchar(30)
     declare @notrinsmoney money --存款前,

 declare @type1 varchar(5)
 declare @type2 varchar(5)
select @cardId = cardId,@trinsmoney  = balance ,@type1=IsReportLoss from inserted
select @notrinsmoney = balance ,@type2=IsReportLoss from deleted
if @type1=@type2
begin
 if @trinsmoney-@notrinsmoney<0
 begin
  insert into transInfo values(getdate(),@cardId,'取款',@notrinsmoney-@trinsmoney,'取款成功')
  print('取款成功！')
 end
 else
 begin
  insert into transInfo values(getdate(),@cardId,'存款',@trinsmoney-@notrinsmoney,'存款成功')
  print('存款成功！')
 end
 end
else
begin
 if @type1='是'
  begin
   insert into transInfo values(getdate(),@cardId,'挂失',@trinsmoney-@notrinsmoney,'挂失成功')
   print('挂失登记成功！')
  end
  else
 if @type1='否'
  begin
   insert into transInfo values(getdate(),@cardId,'补卡',10,'补卡成功')
   print('补卡登记成功！')
  end
end
go
--================================================================
--================================================================

--操作系统 创建存储对象
create procedure proc_caoz(
 @cardId varchar(30),
 @pass1 varchar(20),
 @type varchar(10),
 @moneys money =0, 
 @newpass varchar(20)='',
 @newpass1 varchar(20)=''
)
as
declare @pass varchar(20)
declare @state1 varchar(5)
declare @sum money
select @pass=pass ,@state1=IsReportLoss from  cardInfo where cardId = @cardId
if  exists(select * from cardInfo where cardId =@cardId)
begin
if @type!='存款'
 begin
  if @pass=@pass1
   begin
     if @type='挂失'
    begin
     if @state1='否'
      begin
       update cardInfo set IsReportLoss='是' where cardId = @cardId
       print('挂失成功！')
      end
      else
      begin
       raiserror('已经挂失，无需再次申请，请及时补卡',16,1)
      end
    end
    else
    if @type='补卡'
    begin
     if @state1='是'
      begin
       update cardInfo set IsReportLoss='否' where cardId = @cardId
       print('补卡成功！')
      end
      else
      begin
       raiserror('补卡请先挂失',16,1)
      end
    end
    else
    if @type='改密码'
    begin
     if @newpass=@newpass1
     begin
      if @state1='否'
      begin
       if len(@newpass)>=6 and len(@newpass)<=20
       begin
        if @newpass!=@pass1
        begin
         update cardInfo set pass=@newpass where  cardId = @cardId
         print('密码修改成功，请牢记您的新密码')
        end
        else
        begin
         raiserror('新密码不能与原密码相同，消息由系统自动发出',16,1)
        end
       end
       else
       begin
        raiserror('密码不能低于六位和大于20位',16,1)
       end
      end
      else
      begin
       raiserror('卡已经挂失，请本人携带本人开户身份证到本银行营业厅办理补卡手续再行改密码！本消息由系统自动发出',16,1)
      end
     end
     else
     begin
      raiserror('您输入的两次密码不一样,请重新输入',16,1)
     end

    end
    else
    --取款代码
    if @type='取款'
    begin
      if @state1='否'
      begin
       select @sum = balance from  cardInfo where cardId = @cardId
       if @sum-@moneys>1
        begin
         update cardInfo set balance=balance-@moneys where cardId = @cardId
        end
        else
        begin
         raiserror('您好！余额不足！本消息由系统自动发出',16,1)
        end
      end
      else
      begin
       raiserror('卡已经挂失，请携带本人带本人开户身份证到银行办理补卡手续',16,1)
      end

    end
    else
    begin
     raiserror('没有该服务类型',16,1)
    end
   end
   else
   begin
    raiserror('密码错误！操作失败',16,1)
   end
  end
  else
  begin
    select @sum = balance from cardInfo where cardId =@cardId
    if @moneys+@sum>1000000
    begin
      raiserror('您好！该账户出现异常，请到营业厅咨询！本消息由系统自动发出',16,1)
    end
    else
    begin
     update cardInfo set  balance=balance+@moneys where cardId = @cardId
     print('存款操作成功，账户：'+convert(varchar(30),@cardID)+'存入'+convert(varchar(10),@moneys)+'元')
    end
  end
end
else
begin
 raiserror('你输入的账户不存在',16,1)
end
go
--===================================================================
--===================================================================

--=================创建获取随机卡号的存储过程=======================

create procedure proc_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 proc_randCardID @mycardID OUTPUT
print ('产生的随机卡号为：'+@mycardID)
GO

 

/*
=====================数据库安全还原备份====================
*/
--物理数据库备份
backup database atm
to disk ='F:\QQPCmgr\桌面\个人项目设计一\atm.bak'

--数据库还原
use master
go
drop database atm
restore database atm
from disk='F:\QQPCmgr\桌面\个人项目设计一\atm.bak'
with replace
use atm
go
----物理备份到此结束

--逻辑备份
--保留每次的数据状态
backup database atm
to atm_1

--逻辑数据恢复
use master
go
drop database atm
restore database atm
from atm_1
with file=2,replace
use atm
go

--========================================================

/*
=====================================================================

=====================银行系统操作区域================================
*/
delete cardInfo     --删除银行卡信息
delete transInfo --删除交易表信息
delete userInfo  --删除用户表信息

drop procedure proc_adduser    --删除开户存储过程  
drop procedure proc_addmessage --删除办卡存储过程
drop procedure proc_caoz       --删除系统功能存储过程
drop trigger tri_cardInfo      --删除交易信息触发器

--============
--开户窗口
--============
exec proc_adduser '杨静','360781198804764435','15954677865','江西赣州' --开户

--============
--办卡窗口
--============
declare @mycardID varchar(30)
EXECUTE proc_randCardID @mycardID OUTPUT
exec proc_addmessage @mycardID,'RBB','定期',10,10,'888888','否',4--办卡

/*
ATM银行系统 pro_caoz 存储过程的操作说明书
exec proc_caoz [卡号] [银行密码] [功能选择] [交易金额] [新密码] [确认新密码]
非改密码时     新密码和确认密码不填
非存取钱操作时 操作金额不用填
存款时         密码不用填
*/
exec proc_caoz

               /*[账****号]*/'10001 49878 89989 87980 76667',
               /*[密****码]*/'8',
               /*[业****务]*/'取款',--存款 、取款、挂失 、改密码、补卡
               /*[金****额]*/ 90,
               /*[新密**码]*/ 9109091,
               /*[确认密码]*/ 9109091
--===================================================================

--查询信息
select * from userInfo   --查询用户表信息
select * from cardInfo  --查询账户信息
select transDate as 交易时间,cardID as 交易卡号,transType as 交易类型,transMoney
as 交易金额,remark as 备注 from transInfo --查询交易信息

/*
=====================银行系统操作区域结束============================

=====================================================================
*/