ATM取款机项目

use master
go
if exists(select *from sysdatabases where name='bankDB')
drop database bankDB
exec xp_cmdshell 'mkdir D:\bank',no_output
create database bankDB
on
(
name='bankDB_data',
filename='D:\bank\bankDB_data.mdf',
size=5mb,
maxsize=20mb,
filegrowth=15%
)
log on
(
name='bankDB_log',
filename='D:\bank\bankDB_log.ldf',
size=1mb,
maxsize=5mb,
filegrowth=15%
)
go
--------------------------------------------------------------------userinfo用户信息表---------------------------------------------------------------
use bankDB
if exists(select*from sysobjects where name='userinfo')
drop table userinfo
create table userinfo
(
customerid int identity(1,1) not null,
customername varchar(12) not null,
pid varchar(32) not null,
telephone varchar(32) not null,
address varchar(32) null
)
go
-----------------------------------------------------------------向userinfo用户信息表加约束----------------------------------------------------------
alter table userinfo
add     constraint PK_customerid primary key(customerid),
constraint UQ_pid unique(pid),
constraint CK_pid check(len(pid)=18 or (len(pid)=15)),
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][0-9]'
   or 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 telephone like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
   or telephone like'[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
   or telephone like '[1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
constraint DF_address default('地址不详')for address
go
-----------------------------------------------------------------向userinfo用户信息表加数据----------------------------------------------------------
--delete from userinfo
insert into userinfo(customername,pid,telephone,address)
values('张三','123456789012345','010-67898978','北京海淀')
insert into userinfo(customername,pid,telephone,address)
values('李四','321245678912345678','0478-44443333',' ')
select *from userinfo
go
-----------------------------------------------------------------向cardinfo创建银行卡信息表----------------------------------------------------------
if exists(select*from sysobjects where name='cardinfo')
drop table cardinfo
create table cardinfo
(
cardid varchar(23) not null,
curtype varchar(12)   null,
savingtype varchar(12) null,
opendate datetime not null,
openmoney money not null,
balance money not null,
pass varchar(12) not null,
isreportloss varchar(12) not null,
customerid int not null,
)
go
-----------------------------------------------------------------为cardinfo银行卡信息表增加约束------------------------------------------------------
alter table cardinfo
add     constraint PK_cardid primary key(cardid),
constraint CK_cardid check(cardid like'1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'),
constraint DF_curtype default('RMB')for curtype,
constraint CK_savingtype check(savingtype in('活期','定活两便','定期')),
constraint DF_opendate default(getdate()) for opendate,
   constraint DF_openmoney check(openmoney>=1),
constraint CK_pass check(len(pass)=6),
constraint DF_pass default('888888') for pass,
constraint CK_balance check(balance>=1),
constraint DF_isreportloss default('否')for isreportloss,
constraint FK_customerid foreign key(customerid)references userinfo(customerid)
go
------------------------------------------------------------------为cardinfo银行卡信息表增加值--------------------------------------------------------
--delete from cardinfo
insert into cardinfo(cardid,curtype,savingtype,openmoney,balance,customerid)
values('1010 3576 1212 1134','RMB','定期',1.0000,1.0000,'2')
insert into cardinfo(cardid,curtype,savingtype,openmoney,balance,customerid)
values('1010 3576 1234 5678','RMB','活期',1000.0000,1000.0000,'1')
select*from cardinfo
------------------------------------------------------------------创建transinfo交易信息表------------------------------------------------------------
if exists(select *from sysobjects where name='transinfo')
drop table transinfo
create table transinfo
(
transdate datetime not null,
cardid   varchar(23) not null,
transtype varchar(12) not null,
transmoney money not null,
remark varchar(12) null,
)
go
------------------------------------------------------------------为transinfo交易信息表加约束--------------------------------------------------------
alter table transinfo
add     constraint DF_transdate default(getdate()) for transdate,
constraint FK_cardid foreign key(cardid) references cardinfo(cardid),
constraint CK_transtype check(transtype like'存入'or transtype like'支取'),
constraint CK_transmoney check(transmoney >0),
constraint DF_remark default('NULL') for remark
go
------------------------------------------------------------------为transinfo交易信息表加值----------------------------------------------------------
insert into transinfo(transtype,cardid,transmoney)
values('支取','1010 3576 1212 1134',900)
update cardinfo set balance=balance-900 where balance=1000
insert into transinfo(transtype,cardid,transmoney)
values('存入','1010 3576 1234 5678',5000)
update cardinfo set balance=balance+5000 where balance=1
go
------------------------------------------------------------------业务模拟修改密码-------------------------------------------------------------------
update cardinfo set pass=123456 where cardid='1010 3576 1234 5678'
update cardinfo set pass=123123 where cardid='1010 3576 1212 1134'
select*from cardinfo
go
--------------------------------------------------------------------挂失账号------------------------------------------------------------------------
update cardinfo set isreportloss='是' where cardid='1010 3576 1212 1134'
select*from cardinfo
go
------------------------------------------------------------------统计银行的资金流通余额和盈利计算----------------------------------------------------
declare @a float--总存储        
select @a=transmoney from transinfo where (transtype='存入')
declare @b float--总支出
select @b= transmoney from transinfo where (transtype='支取')
print'银行流通余额为:'+convert (varchar(20),@a-@b)+'RMB'
print'盈利计算为:'+convert (varchar(30),@b*0.008-@a*0.003)+'RMB'
go
------------------------------------------------------------------查询本周开户账号，显示该卡的信息-----------------------------------------------------
print '本周开户的卡号信息如下:'
declare @temp int
select 客户姓名=customerName,联系电话=telephone ,开户金额=openMoney,
开户日期=opendate from userInfo inner
join cardinfo on userinfo.customerID=cardinfo.customerID
where datediff(dd,opendate,getdate())<=(datepart(dw,getdate())-1)
select * from cardinfo
go
------------------------------------------------------------------查询本月最高的卡号-----------------------------------------------------------------
select distinct cardID,transMoney,datediff(dd,transDate,getdate()) from transInfo
where datediff(dd,transDate,getdate())<=31 and transMOney in (select max(transMOney) from transInfo
where datediff(dd,transDate,getdate())<=31)
select datepart(dd,getdate())
------------------------------------------------------------------查询挂失账号的客户信息-------------------------------------------------------------
select customername as 客户姓名 ,telephone as 联系电话 from userInfo
where customerid in(select customerID from cardInfo
where IsReportLoss='是')
go
------------------------------------------------------------------催款提醒业务-----------------------------------------------------------------------
select 客户姓名=customerName,联系电话=telephone ,
帐上余额=balance from userInfo inner
join cardinfo on userinfo.customerID=cardinfo.customerID
where balance<200
go
-----------------------------------------------------------------创建索引和视图---------------------------------------------------------------------
--索引
if exists(select*from sysindexes where name='ix_transInfo_cardid')
drop index transinfo.ix_transInfo_cardid
go
create nonclustered index ix_transInfo_cardid
on transinfo(cardid)
with fillfactor=70
go
----------------------------------------------------------------------------------------------------------------------------------------------------
print '按索引查询'
select * from transInfo(index=ix_transInfo_cardid)
where cardID=(select cardID from cardInfo where
customerID =(select customerID from userInfo where customername='张三'))
select * from transInfo (index=ix_transInfo_cardid)
where cardID='1010 3576 1212 1134'
---------视图
if exists (select*from sysobjects where name='view_userinfo')
drop view view_userinfo
go
create view view_userinfo
as
select 客户编号=customerid,开户名=customername,电话号码=pid,居住地址=address
from userinfo
go
select * from view_userinfo
-------------------------------------------------------------------------------------------------------------------
if exists (select*from sysobjects where name='view_cardinfo')
drop view view_cardinfo
go
create view view_cardinfo
as
select 卡号=cardid,货币种类=curtype,存款类型=savingtype,
开户日期=openmoney,余额=balance,密码=pass,是否挂失=isreportloss,客户编号=customerid
from cardinfo
go
select*from view_cardinfo
-------------------------------------------------------------------------------------------------------------------
if exists (select*from sysobjects where name='view_transinfo')
drop view view_transinfo
go
create view view_transinfo
as
select 卡号=cardid,交易日期=transdate,交易类型=transtype, 交易金额=transmoney,备注=remark
from transinfo
go
select*from view_transinfo
--------------------------------------------创建触发器trig_trans------------------------------------------------------------------------------------
if exists (select * from sysobjects where name='trig_trans')
drop trigger trig_trans
go
create trigger trig_trans
on transInfo
for insert
as
declare @cardID char(19),    --交易卡号,@cardID
@transMoney money,    --交易金额,@transMoney
@transType char(8),    --交易类型,@transType
@balance money,     --交易前余额,@balance
@newbalance money    --交易后余额,@newbalance
--获取要交易的　交易卡号　交易金额　交易类型
select @cardID =cardID ,
   @transMoney=transMoney,
   @transType=transType from inserted
--获取交易前的余额
select @balance=balance from cardInfo where [email=cardID=@cardID]cardID=@cardID[/email]
--嵌套if 首先判断交易类型,如果是支取再判断余额是否足够交易
if (@transType='存入')
   begin
   set nocount on
   print '交易正在进行中,请稍候....'
   update cardInfo set [email=balance=@balance+@transMoney]balance=@balance+@transMoney[/email] where [email=cardID=@cardID]cardID=@cardID[/email]
    print '存储成功,存储金额为'+convert(varchar(20),@transMoney)
   SELECT @newbalance=balance from cardInfo where [email=cardID=@cardID]cardID=@cardID[/email]
   PRINT '卡号'+convert(varchar(19),@cardID)+'目前余额'+convert (varchar(8),@newbalance)

   end
else if (@transType='支取')
begin
   if(@balance-@transMoney<1)
    begin
     --set nocount on
         raiserror('支取失败，余额不足',16,1)
         print '卡号'+convert(varchar(19),@cardID)+'目前余额'+convert (varchar(8),@balance)
         rollback tran--回滚事务，取消交易
    
       end
   else
    begin
         set nocount on
     print '交易正在进行中,请稍候....'
     update cardInfo set [email=balance=@balance-@transMoney]balance=@balance-@transMoney[/email] where [email=cardID=@cardID]cardID=@cardID[/email]
         print '支取成功,支取金额为:'+convert(varchar(20),@transMoney)
         SELECT @newbalance=balance from cardInfo where [email=cardID=@cardID]cardID=@cardID[/email]
         PRINT '卡号'+convert(varchar(19),@cardID)+'目前余额'+convert (varchar(8),@newbalance)
       end
end
go
-------------------------------------------------------------------测试触发器：李四的触发器存入200-----------------------------------------------------
declare @cardID2 char (19)
select @cardID2=cardID from cardInfo
where customerID=(select customerID from userInfo where customername='李四')
if not exists (select * from cardInfo where [email=cardID=@cardID2]cardID=@cardID2[/email])
   begin
   raiserror ('没有该用户',16,1)
   return
   end
insert into transInfo(cardID,transType, transMoney)values (@cardID2,'存入',200)
-------------------------------------------------------------------测试触发器：张三的卡号支取1000-----------------------------------------------------
declare @card char(19)
select @card=cardID from cardInfo Inner join userinfo on
cardInfo.customerID =userInfo.customerID where customername='张三'
if not exists (select * from cardInfo where [email=cardID=@card]cardID=@card[/email])
begin
   raiserror ('没有该用户',16,1)
   return
   end
insert into transInfo(cardID,transType, transMoney )values (@card,'支取',1000)
----------------------------------------------------------------------------------------------------------------------------------------------------
select*from userinfo
select*from cardinfo
select*from transinfo
go
----------------------------------------------------------------------------------------------------------------------------------------------------
select*from view_userinfo
select*from view_cardinfo
select*from view_transinfo
go
------------------------------------------------------------------创建存储过程存钱或取钱proc_takemoney------------------------------------------------
if exists(select*from sysobjects where name='proc_takemoney')
drop procedure proc_takemoney
go
create procedure proc_takeMoney
   @IDcard char(19),   --交易卡号
   @m money,    --交易金额
   @type char(8),    --交易类型
   @inputpass char(6)=''   --取款密码
as
if (@inputpass<>(select pass from cardInfo where [email=cardID=@IDcard]cardID=@IDcard[/email]))--判断密码是否相符
    begin
    raiserror ('密码错误,请核实',16,1)
   return
   end
if not exists (select * from cardInfo where [email=cardID=@IDcard]cardID=@IDcard[/email])--判断用户信息表中是否存在取钱或存钱的用户
    begin
    raiserror ('没有该用户',16,1)
   return
   end
insert transInfo (cardID,transType,transMoney) values (@IDcard,@type,@m)
insert into transInfo values(default,@IDcard,@type,@m,default)
go
---------------------------------------------------------------测试：调用取钱或存钱的存储过程---------------------------------------------------------
declare @card char(19)
select @card=cardID from cardInfo
where customerID =(select customerID from userInfo where customername='张三')
exec proc_takemoney @card,300,'支取','123456'--调用过程，执行取钱
go
declare @card2 char(19)
select @card2=cardID from cardInfo
where customerID =(select customerID from userInfo where customername='李四')
exec proc_takemoney @card2,500,'存入','123123'--调用过程，执行存钱
go
---------------------------------------------------------------产生随机卡号的存储过程----------------------------------------------------------------
if exists(select*from sysobjects where name='proc_randcardid')
drop procedure proc_randcardid
go
create procedure proc_randcardid
@randCardID char(19) output,
@id char(10)='1010 3576'            --前8位
as
declare @r numeric(15,8)           --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=@id+''+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4)
go
--------------------------------------------------------------测试：调用随机卡号的存储过程------------------------------------------------------------
declare @mycardID char(19)
exec proc_randCardID @mycardID output--执行存储过程
print '产生的随机卡号为:'+@mycardID
go
--------------------------------------------------------------开户的存储过程proc_openAccount------------------------
if exists(select*from sysobjects where name='proc_openAccount')
drop procedure proc_openAccount
go
create procedure proc_openAccount
@customername char(8),
@pid char(18),
@telephone char(13),
@savingtype char(12),
@openmoney money,
@address varchar(50)=''
as
declare @mycardID_2 char(19),--卡号
@openDate datetime--开户时间
execute proc_randCardID @mycardID_2 output--调用随机卡号存储过程产生卡号
   while exists (select * from cardINfo where [email=cardID=@mycardID_2]cardID=@mycardID_2[/email])--如果存在则重新产生新号
exec proc_randCardID @mycardID_2 output--产生随机卡号
insert into userInfo values (@customerName,@PID,@telephone,@address)
select *from cardinfo
declare @cur_customerId int--顾客编号
select @cur_customerId=customerID from userInfo where [email=PID=@PID]PID=@PID[/email] --获取新开户顾客编号
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values (@mycardID_2,@savingType,@openMoney,@openMoney,@cur_customerId)--向银行卡信息表 cardInfo 插入数据 卡号，存款类型，余额，顾客编号
select distinct @openDate=openDate from cardinfo
inner join userinfo on userinfo.customerID =cardinfo.customerID
where [email=customername=@customerName]customername=@customerName[/email]--开户用户的开户时间
--开户成功后-显示 信息
print ''
print '尊敬的客户 ：[email='+@customerName]'+@customerName[/email]
print '恭喜您开户成功!'
print '系统为您产生的随机卡号为'+convert(varchar(19),@mycardID_2)
print '开户日期'+convert(varchar(10),@openDate,111)+
' 开户金额:'+convert(varchar(10),@openMoney)+'RMB'
go
-------------------------------------------------------------------------------------------------------------------
execute proc_openAccount '王五','334456889012678','0000-63598978','活期',1000,'河南新乡'
set nocount on
execute proc_openAccount @customername='赵二',@PID ='213445789123422222',
@telephone='0760-44446666',@openMoney=1,@savingType='定期'
---------------------------------------------------创建事务---------------------------------------------------------
if exists(select * from sysobjects where name='proc_tranfer')
drop procedure proc_tranfer
go
create procedure proc_tranfer @card1 char(19),@card2 char(19),@outmoney money=100
as
begin tran
declare @errorsum int
set @errorsum=0
insert into transinfo(cardid,transtype,transmoney)
values(@card1,'支取',@outmoney)
set @errorsum=@errorsum+@@error
insert into transinfo(cardid,transtype,transmoney)
values(@card2,'存入',@outmoney)
set @errorsum=@errorsum+@@error
if @errorsum<>0
begin
print '交易失败,回滚事务'
rollback tran
end
else
begin
print '交易成功,永久保存'
commit tran
end
go
--------------------------------------------------------------------------------------------------------------------
declare @card1 char(19),@card2 char(19)
select @card1=cardid from cardinfo inner join userinfo on
cardinfo.customerid=userinfo.customerid where customername='李四'
select @card2=cardid from cardinfo inner join userinfo on
cardinfo.customerid=userinfo.customerid where customername='张三'
exec proc_tranfer @card1,@card2,1000
go
select * from view_cardinfo
select * from view_transinfo
select*from userinfo
select*from cardinfo
select*from transinfo
go
------------------------------------------------------------------------------------------------------------------
--添加SQL登陆账号
exec sp_addlogin 'sysAdmin','1234'
--创建数据库用户
exec sp_grantdbaccess 'sysAdmin','sysAdminDBUser'
grant select,insert,update,delete on userinfo to sysAdminDBUser
grant select,insert,update,delete on cardinfo to sysAdminDBUser
grant select,insert,update,delete on transinfo to sysAdminDBUser
select*from userinfo
select*from cardinfo
select*from transinfo