atm提款机.txt

QL SERVER 应用开发 银行ATM(取款机）系统 数据库设计 我在学校的 SQL项目,模拟 银行ATM(取款机）系统

--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆

--->>>>创建视图

create view view_userInfo
as
select 客户编号=customerID,开户名=customerName,身份证号=PID,电话号码=telephone,居住地址=address from userInfo

GO

create view view_cardInfo
as
select 卡号=cardID,货币种类=curType,存款类型=savingType,开户日期=openDate,开户金额=openMoney,余额=balance,密码=pass,是否挂失=IsReportLoss,顾客编号=customerID from cardInfo
GO

create view view_transInfo
as
select 交易日期=transDate,卡号=cardID,交易类型=transType,交易金额=transMoney,备注=remark from transInfo

Go
select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo
GO

--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆

--创建触发器
if exists(select * from sysobjects where name='trig_trans')
drop trigger trig_trans
GO
create trigger trig_trans
on transInfo
for insert
as
-------定义变量
declare @type char(4)
declare @myBalance money
declare @outMoneys money
declare @myCardID char(19)
select @type=transType,@outMoneys=transMoney,@myCardID=cardID from inserted
select @myBalance=balance from cardInfo where cardID=@myCardID
if(@type='支取')
begin
  if(@myBalance<=@outMoneys+1)
  begin
  raiserror('交易失败!余额不足!',16,1)
  print '卡号 '+@myCardID+' 余额 '+convert(varchar(30),@myBalance)
  rollback tran
  end
else
  begin
  update cardInfo set balance=balance-@outMoneys where cardID=@myCardID
  print '交易成功!交易金额: '+ convert(varchar(30),@outMoneys)
  select @myBalance=balance from cardInfo where cardID=@myCardID
  print '卡号 '+@myCardID+' 余额 '+convert(varchar(30),@myBalance)
  end

end
else
begin
  update cardInfo set balance=balance+@outMoneys where cardID=@myCardID
  print '交易成功!交易金额: '+ convert(varchar(30),@outMoneys)
  select @myBalance=balance from cardInfo where cardID=@myCardID--重新查询出余额
  print '卡号 '+@myCardID+' 余额 '+convert(varchar(30),@myBalance)
end

GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆

--测试触发器
declare @card char(19)
select @card=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='张三'
insert into transInfo(transType,cardID,transMoney)values('支取',@card,1000)

GO

declare @cards char(19)
select @cards=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='李四'
insert into transInfo(transType,cardID,transMoney)values('存入',@cards,200)

GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--取钱和存钱的存储过程

if exists(select name from sysobjects where name='proc_takeMoney')
drop proc proc_takeMoney
GO
create proc proc_takeMoney
@card char(19), 
@m money,
@type char(4),
@inputPass char(6)=''
as
set nocount on
declare @oldPass char(6)
select @oldPass=pass from cardInfo where cardID=@card
if(@type='支取')
  begin
if(@inputPass=@oldPass)
  insert into transInfo(transType,cardID,transMoney)values(@type,@card,@m)
else
  raiserror('交易失败!密码有误!',15,1)

  end 
else
insert into transInfo(transType,cardID,transMoney)values(@type,@card,@m)
---测试取钱,需要密码 
declare @card char(19)
select @card=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='张三'
exec proc_takeMoney @card,300,'支取','123456'

--测试存钱,不需密码
declare @card char(19)
select @card=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='张三'
exec proc_takeMoney @card,300,'存入'
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
-->>产生随机种子的存储过程

if exists(select name from sysobjects where name='proc_randCardID')
drop proc proc_randCardID
GO
create proc proc_randCardID
@randCardID char(19) output
as
declare @invariableCardID char(9) 
declare @r numeric(10,8)
declare @tempStr varchar(19)
set @invariableCardID='1010 3576'--固定卡号前八位
select @r=rand((datepart(yy,getdate())*1000000)+(datepart(mm,getdate())*100000)+(datepart(ss,getdate())*1000)+(datepart(ms,getdate())))
set @tempStr=convert(varchar(19),@r)
set @randCardID=@invariableCardID+' '+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4)
GO

--测试:产生随机卡号


declare @mycardID char(19)
exec proc_randCardID @mycardID output
print '产生的随机号为: '+@mycardID
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--创建开户存储过程

if exists(select name from sysobjects where name='proc_openAccount')
drop proc proc_openAccount
GO
create proc proc_openAccount
@customerName char(8),@PID varchar(18),@telephone char(13),@openMoney money,@savingType char(8),
@address varchar(20)=''
as
set nocount on
declare @mycardID char(19)
declare @cur_customerID int
declare @cur_openDate datetime
exec proc_randCardID @mycardID output
if exists(select PID from userInfo where PID=@PID)
raiserror('此用户已经存在!',17,1)
else
begin
while exists(select cardID from cardInfo where cardID=@mycardID)
exec proc_randCardID @mycardID output
insert into userInfo(customerName,PID,telephone,address)
values(@customerName,@PID,@telephone,@address)

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

insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values(@mycardID,@savingType,@openMoney,@openMoney,@cur_customerID)
select @cur_openDate=openDate from cardInfo where cardID=@mycardID
print '尊敬的客户,开户成功!系统为您产生的随机卡号为: '+@mycardID
print '开户日期 '+convert(varchar(30),@cur_openDate,111)+' 开户金额: '+convert(varchar(30),@openMoney)
end

-->>测试开户存储过程
exec proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡'
exec proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期',default

--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
-->>创建事务
if exists(select name from sysobjects where name='proc_transfer')
drop proc proc_transfer
GO
create proc proc_transfer
@card1 char(19),
@card2 char(19),
@outMoney money
as
print'开始转帐，请稍后......'
begin tran
set nocount on
declare @errcount int

set @errcount=0
insert into transInfo(transType,cardID,transMoney)values('支取',@card1,@outMoney)
set @errcount=@errcount+@@error
insert into transInfo(transType,cardID,transMoney)values('存入',@card2,@outMoney)
set @errcount=@errcount+@@error
if(@errcount<>0)
  begin
  print'转帐失败'
  rollback tran
end

else 
  print'转帐成功!'
  commit tran
  
GO

---测试转帐
declare @card1 char(19)
select @card1=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='张三'

declare @card2 char(19)
select @card2=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='李四'

exec proc_transfer @card1,@card2,2000--调用转帐存储过程


GO

--查询结果
select * from view_cardInfo
select * from view_transInfo

GO
--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
--添加登录帐号
exec sp_addlogin 'sysAdmin','1234'
exec sp_grantdbaccess 'sysAdmin'
grant select,insert,update,delete on userInfo to sysAdmin
grant select,insert,update,delete on cardInfo to sysAdmin
grant select,insert,update,delete on transInfo to sysAdmin

GO
--测试帐号语句
select * from userInfo
insert into userInfo(customerName,PID,telephone,address)values('赵六','43072619860218431X','020-85543665','广州天河')
update userInfo set customerName='谢霆锋' where customerName='赵六'

--◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo

GO