ATM银行转账系统之存储过程

/*银行取款机数据库*/
use master
go
if exists(select * from sysdatabases where name='bankDB')
drop database bankDB
go
create database bankDB --建立数据库bankDB
go
use bankDB
go
if exists(select * from sysobjects where name='userInfo')
drop table userInfo
go
create table userInfo  --建立用户信息表
(
 customerID int identity(1,1) primary key,--顾客编号
 customerName varchar(20) not null,--开户名
 PID numeric not null,--身份证号
 telephone varchar(30) not null,--联系电话
 userAddress varchar(100)--居住地址
)
go
alter table userInfo add constraint UK_PID unique(PID)
alter table userInfo add constraint CK_PID check(len(PID)=15 or len(PID)=18)
alter table userInfo add constraint CK_telephone check(telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or len(telephone)=11)
go

use bankDB
go
if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
go
create table cardInfo  --建立银行卡信息表
(
cardID varchar(30) primary key,--卡号
curType varchar(10) not null,--货币种类
savingType varchar(20),--存款类型
openDate datetime not null,--开户日期
openMoney money not null,--开户金额
balance money not null,--余额
pass int not null,--密码
IsReportLoss bit not null,--是否挂失
customerID int not null--顾客编号
)
go
alter table cardInfo add constraint CK_cardID check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')
alter table cardInfo add constraint DF_curType default('RMB') for curType
alter table cardInfo add constraint CK_savingType check(savingType in ('活期','定活两便','定期'))
alter table cardInfo add constraint DF_openDate default(getdate()) for openDate
alter table cardInfo add constraint CK_openMoney check(openMoney>=1)
alter table cardInfo add constraint CK_balance check(balance>=1)
alter table cardInfo add constraint CK_pass check(len(pass)=6)
alter table cardInfo add constraint DF_pass default(888888) for pass
alter table cardInfo add constraint DF_IsReportLoss default(0) for IsReportLoss
alter table cardInfo add constraint FK_customerID foreign key(customerID) references userInfo(customerID)
go

use bankDB
go
if exists(select * from sysobjects where name='transInfo')
drop table transInfo
go
create table transInfo  --建立交易信息表
(
 transDate datetime not null,--交易日期
 cardID varchar(30) not null,--卡号
 transType varchar(10) not null,--交易类型
 transMoney money not null,--交易金额
 remark varchar(100) --备注
)
go
alter table transInfo add constraint DF_transDate default(getdate()) for transDate
alter table transInfo add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)
alter table transInfo add constraint CK_transType check(transType='存取' or transType='支取')
alter table transInfo add constraint CK_transMoney check(transMoney>0)
go





/*填充数据*/
use bankDB
go
insert into userInfo(customerName,PID,telephone,userAddress) values('张三',123456789012345,'010-67898978','北京海淀')
insert into userInfo(customerName,PID,telephone,userAddress) values('李四',321245678912345678,'027-67898978','武汉江岸区')
insert into userInfo(customerName,PID,telephone,userAddress) values('王五',221245678912345672,'021-67898978','上海浦东区')
insert into userInfo(customerName,PID,telephone,userAddress) values('赵六',521245678912345675,'13178910771','合肥肥西区')
go

insert into cardInfo(cardID,savingType,openMoney,balance,pass,customerID) 
values('1010 3576 1234 5678','活期',1000,1000,123123,1)
insert into cardInfo(cardID,savingType,openMoney,balance,pass,customerID) 
values('1010 3576 1212 1134','定期',1,1,321321,2)
insert into cardInfo(cardID,savingType,openMoney,balance,customerID) 
values('1010 3576 1256 2235','定活两便',50,50,3)
insert into cardInfo(cardID,savingType,openMoney,balance,customerID) 
values('1010 3576 1358 3221','活期',500,500,4)
go

insert into transInfo(transDate,transType,cardID,transMoney) values(getdate(),'支取','1010 3576 1234 5678',900)
update cardInfo set balance=balance-900 where customerID=1

insert into transInfo(transDate,transType,cardID,transMoney) values(getdate(),'存取','1010 3576 1212 1134',5000)
update cardInfo set balance=balance+5000 where customerID=2

update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'
update cardInfo set IsReportLoss=1 where cardID='1010 3576 1256 2235'


select * from userInfo 
select * from cardInfo
select * from transInfo




/*产生随机卡号存储过程*/
use bankDB
go
create proc p_randCardID
@randCardID char(19) output
as
declare @r numeric(15,8)
select @r=rand((datepart(mm,getdate())*10000)+(datepart(ss,getdate())*1000)+(datepart(ms,getdate())))
--declare @tempStr varchar(20)
--set @tempStr=@r
select @randCardID='1010 3576'+substring(cast(@r as varchar),3,4)+' '+subString(cast(@r as varchar),4,4)
go

declare @mycardID char(19)
exec p_randCardID @mycardID output
print '产生的随机号为：'+@mycardID





/*开户存储过程*/
use bankDB
go
select * from userInfo 
select * from cardInfo
select * from transInfo
create proc p_openAccount
@customerName char(8),
@cardID varchar(30),
@PID char(18),
@telephone char(13),
@openMoney money,
@savingType char(8),
@userAddress varchar(50)=''
as
if(@openMoney>=1)
begin
insert into userInfo(customerName,PID,telephone,userAddress) 
values(@customerName,@PID,@telephone,@userAddress)
    declare @cur_customerID int
select @cur_customerID=customerID from userInfo where PID=@PID
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values(@cardID,@savingType,@openMoney,@openMoney,@cur_customerID)
end
else
    print '开户金额不能小于1元'
go




/*存钱取钱存储过程*/
use bankDB
go
drop proc p_takeMoney
go

create proc p_takeMoney
@card char(19),@m money,@type char(4),@inputPass int=''
as
 declare @balance money
 select @balance=balance from cardInfo where @card=cardID
if @type='存取'
begin
 print '交易正进行，请稍后......'
  insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@card,'存取',@m) 
  set @balance=@balance+@m
  update cardInfo set balance=balance+@m where @card=cardID
  if @@error=0
  begin
  print '交易成功！'
  print '卡号:'+@card+' 余额：'+cast(@balance as varchar)
  end
  else
  print '交易失败！'
end
if @type='支取'
begin
      declare @pass int
      select @pass=pass from cardInfo where @card=cardID
  if(@inputPass=@pass)
begin
begin tran
  print '交易正进行，请稍后......'
  insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@card,'支取',@m)
              set @balance=@balance-@m 
  update cardInfo set balance=balance-@m where @card=cardID 
if @@error>0 
              begin
rollback tran
                print '交易失败！余额不足'
                print '卡号:'+@card+' 余额：'+cast(@balance as varchar)
              end
else
               begin
commit tran
print '交易成功！交易金额'+cast(@m as varchar(20))
print '卡号:'+@card+' 余额：'+cast(@balance as varchar)
               end 
end
        else 
          print '密码输入不正确'
end
go



/*转账存储过程*/
use bankDB
go
set nocount on
drop proc p_transfer
go

create proc p_transfer 
@card1 char(19),
@card2 char(19),
@outMoney money
as
begin tran
 exec p_takeMoney @card1,@outMoney,'支取','321321'
if @@error=0
 begin
 print '开始转账，请稍后......'
     print '转账正在进行，请稍候......'
 insert into transInfo(transDate,cardID,transType,transMoney) values(getdate(),@card2,'存取',@outMoney)
     declare @balance money
     select @balance=balance from cardInfo where cardID=@card2
 update cardInfo set balance=balance+@outMoney where cardID=@card2
     set @balance=@balance+@outMoney
     print '交易成功！交易金额：'+cast(@outMoney as varchar)
     print '卡号:'+@card2+' 余额:'+cast(@balance as varchar)
 commit tran
 end
else
begin
     raiserror('交易失败！',16,1)
 rollback tran
end
go




/*存、取、转账业务*/
use bankDB
go
set nocount on
exec p_openAccount '小霞','1010 3576 1111 1133','111331111111115511','021-63568978',900,'活期','河南新乡'--开户
exec p_takeMoney '1010 3576 1212 1134 ',200,'存取',321321 --存钱
exec p_takeMoney '1010 3576 1234 5678 ',111,'支取',123123 --取钱

exec p_transfer '1010 3576 1212 1134','1010 3576 1111 1122',5  --转账

select * from dbo.cardInfo
select * from dbo.transInfo
select * from dbo.userInfo

/*银行相关业务*/
use bankDB 
go
--统计银行的资金流通余额和盈利结算
declare @inMoney money,@outMoney money
select @inMoney=sum(transMoney) from transInfo where transType='存取'
select @outMoney=sum(transMoney) from transInfo where transType='支取'
print '银行流通余额总计为：'+cast(@inMoney-@outMoney as varchar(20))+'RMB'
print '盈利结算为：'+cast(@outMoney*0.008-@inMoney*0.003 as varchar(20))+'RMB'

--查询本周开户的卡号，显示该卡相关信息
declare @openDay datetime
select @openDay=openDate from cardInfo 
select * from cardInfo where datediff(w,@openDay,getdate())=0

--查询本月交易金额最高的卡号
declare @transCash money
select @transCash=max(transMoney) from transInfo
select 最高交易卡号=cardID from  transInfo where transMoney=@transCash

--催款提醒业务
select 客户姓名=userInfo.customerName,联系电话=userInfo.telephone,账上余额=cardInfo.balance
from userInfo join cardInfo on userInfo.customerID=cardInfo.customerID 
where cardInfo.balance<200