创建存储过程.sql

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

use bankDB
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
GO

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