ATM取款机模拟

use master
go
if exists(select * from sysdatabases where [name]='bankDB')
 drop database bankDB
go

create database bankDB
on
(
name='bankDB',
filename='D:/bank/bankDB_data.mdf',
size=3mb,
filegrowth=15%
)
log on
(
name='bankDB_log',
filename='D:/bank/bankB_log.ldf',
size=3mb,
filegrowth=10%
)
go

use bankDB
go
create table userinfo --用户信息表
(
customerID int identity(1,1),--用户编号
customerName char(8) not null,--用户姓名
PID char(18) not null,--身份证号码
telephone char(13) not null,--电话
address varchar(50)--地址
)
go
create table cardinfo --银行卡信息表
(
cardID char(19) not null,--卡号
curType char(5) not null,--币种
savingType char(8) not null,--存款类型
openDate datetime not null,--开户日期
openMoney money not null,--开户金额
balance money not null,--余额
pass char(6) not null,--密码
isReportLoss bit not null,--是否挂失
customerID int not null--客户编号
)
go
create table transInfo --交易信息
(
transDate datetime not null,--交易日期
transType char(4) not null,--交易类型
cardID char(19) not null,--卡号
transMoney money not null,--交易金额
remark text--备注
)
go

---加约束
--userInfo 表的约束
alter table userinfo
 add constraint PK_customerID primary key(customerID),--主键
  constraint CK_PID check(len(PID)=18 or len(PID)=15),--身份证号为15位或18位
  constraint UQ_PID unique(PID),--身份证必须唯一
  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 len(telephone)=13)
  --联系电话格式为xxxx-xxxxxxxx或手机号13位
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,--货币，默认为RMB
  constraint CK_savingType check(savingType in('活期','定活两便','定期')),--存款种类
  constraint DF_openDate  default(getdate()) for openDate,--开卡日期
  constraint CK_openMoney check(openMoney>=1),--开户金额不能低于1元
  constraint CK_balance check(balance>=1),--余额不能低于1元
  constraint CK_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]'),--密码只能为密码只能6位数字
  constraint DF_pass default('888888') for pass,--密码默认为6个8
  constraint DF_isReportLoss default(0) for isReportLoss,--是否挂失，默认为“否”
  constraint FK_customerID foreign key(customerID) references userInfo(cusTomerID)--客户编号，一个客户可以开多张卡
go
--transinfo 表的约束
alter table transInfo
 add constraint CK_transType check(transType in('存入','支取')),--必填，只能是存入或者支取
  constraint FK_cardID foreign key(cardID) references cardinfo(cardID),--外键
  constraint CK_transMoney check(transMoney>0),--交易金额，必需大于0
  constraint DF_transDate default(getdate()) for transDate--交易日期默认为当前系统时间
go

--插入测试数据：
set nocount on  --不显示受影响的条数信息
insert into userInfo(customerName,PID,telephone,address)
 values('张三','123456789012345','0010-67898978','北京海淀')
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
 values('1010 3576 12345678','活期',1000,1000,1)
select * from userinfo
select * from cardinfo

insert into userInfo(customerName,PID,telephone)
 values('李四','321245678912345678','0478-44443333')
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
 values('1010 3576 12121134','定期',1,1,2)

--插入交易信息
insert into transinfo(transType,cardid,transMoney)
values('支取','1010 3576 12345678',900)
update cardinfo
set balance=balance-900 where cardid='1010 3576 12345678'

insert into transinfo(transType,cardid,transMoney)
values('存入','1010 3576 12121134',5000)
update cardinfo
set balance=balance+5000 where cardid='1010 3576 12121134'
select * from transinfo

--修改密码：
update cardinfo set pass='123456' where cardID='1010 3576 12345678'
update cardinfo set pass='123123' where cardID='1010 3576 12121134'
select * from cardinfo

--挂失账号：
update cardinfo set isReportLoss=1 where cardID='1010 3576 12121134'

select * from cardinfo where ((balance between 3000 and 6000) and savingType='定期')

declare @inMoney money
declare @outMoney money
declare @profit money
select @inmoney=sum(transMoney) from transinfo where (transType='存入')
select @outmoney=sum(transMoney) from transinfo where transType='支取'
print '银行流通余额总计为：'+convert(varchar(20),@inMoney-@outMoney)+'RMB'
set @profit=@outMoney*0.008-@inMoney*0.003
print '盈利结算为：'+convert(varchar(20),@profit)+'RMB'

--查询本周开户的卡号，显示该卡相关信息
select * from cardinfo where (datediff(day,getdate(),openDate)<datepart(weekday,opendate))
--查询本月交易金额最高的卡号：
select distinct cardid from transinfo where transMoney=(select max(transMoney) from transinfo)
--查询挂失账号的客户信息：
select customerName as 客户姓名,telephone as 联系电话 from userinfo
where customerID in (select customerID from cardinfo where isReportLoss=1)
--催款提醒，少于200元，就催款
select customerName as 客户姓名,telephone as 联系电话,balance as 账上余额
from userinfo inner join cardinfo on userinfo.customerid=cardinfo.customerid where balance<200

--$$$$$$$$$$建索引和视图
--1.创建索引：
create nonclustered index index_cardid on transinfo(cardid) with fillfactor=70
--2.按指定索引查询张三的交易记录
select * from transinfo(index=index_cardid) where cardid='1010 3576 12345678'
GO
SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 12345678'
GO

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

create view view_cardinfo
 as
select cardid as 卡号,curType as 货币种类,savingType as 存款类型,openDate as 开户日期,
balance as 余额,pass as 密码,isReportLoss as 是否挂失,customerid as 客户编号 from cardinfo
go

create view view_transinfo
as
select transDate as 交易日期,transType as 交易类型,cardid as 卡号,transMoney as 交易金额,
remark as 备注 from transinfo
go

select * from view_cardinfo
select * from view_transinfo
select * from view_userinfo

/*$$$$$$$$$$存储过程$$$$$$$$$$$$$*/
--1.取钱或存钱的存储过程
if exists(select * 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
  print '交易正在进行，请稍后……'
  if (@type='支取')
 begin
  if((select pass from cardinfo where cardid=@card)<>@inputpass)
   begin
    raiserror('密码错误！',16,1)
    return
   end
  end
declare @mybalance money
select @mybalance=balance from cardinfo where cardid=@card
if(@type='支取')
 if(@mybalance>=@m+1)
  update cardinfo set balance=balance-@m where cardid=@Card
 else
  begin
   raiserror('交易失败！余额不足！',16,1)
   print '卡号'+@card+' 余额：'+convert(varchar(20),@mybalance)
   return
  end
else
 update cardinfo set balance=balance+@m where cardid=@card
print '交易成功！交易金额：'+convert(varchar(20),@m)
select @mybalance=balance from cardinfo where cardid=@card
print '卡号'+@card+' 余额：'+convert(varchar(20),@mybalance)
insert into transinfo(transType,cardid,transmoney) values(@type,@card,@m)
go

--2.调用存储过程取钱或存钱
declare @card char(19)
select @card=cardid from cardinfo inner join userinfo on
cardinfo.customerid=userinfo.customerid where userinfo.customerName='张三'
exec proc_takeMoney @card,600,'支取','123456'
go

declare @card char(19)
select @card=cardid from cardinfo inner join userinfo on
cardinfo.customerid=userinfo.customerid where userinfo.customerName='李四'
exec proc_takeMoney @card,500,'存入','123123'

select * from userinfo
select * from cardinfo
select * from transinfo

--3.产生随机卡号的存储过程（一般用当前月份数、当前秒数、当前毫秒数乘以一定的系数作为随机种子）
if exists(select * from sysobjects where name='proc_randCardID')
 drop proc proc_randCardID
go
create proc proc_randCardID @randCardID char(19) output
as
declare @a int  --由小时+分钟+秒+毫秒组成的一串数字，做为随机数的种子
declare @r numeric(11,8) --用来保存算出的随机数，8位小数
declare @tempStr char(10) --用来保存将随机数转成的字符
select @a=(select datepart(hh,getdate())*10000000+datepart(mi,getdate())*100000 +datepart(ss,getdate())*1000 +datepart(ms,getdate()))--小时+分钟+秒+毫秒组成的一串数字
set @r=rand(@a)--用时间做种子，算出8位小数的随机数
set @tempStr=convert(char(10),@r)
set @randCardid='1010 3576 '+substring(@tempStr,3,4)+substring(@tempStr,7,4)  --组合为规定格式的卡号
print @randCardid
go

--4.测试产生的随机卡号
declare @mycardid char(19)
exec proc_randCardid @mycardid output
print '产生的随机卡号为：'+@mycardid
go

--5.开户的存储过程
if exists(select * from sysobjects where name='proc_openAccount')
 drop proc proc_openAccount
go
create proc proc_openAccount @customerName char(8),@PID char(19),@telephone char(13)
 ,@openMoney money,@savingType char(8),@address varchar(50)=''
as
declare @mycardID char(19),@cur_customerID int
--调用产生随机卡号的存储过程获得随机卡号
exec proc_randCardID @mycardID output
while  exists(select * from cardinfo where cardid=@mycardid)
 exec proc_randCardID @mycardID output
print '尊敬的客户，开户成功！系统为您产生的随机卡号为：'+@mycardID
print '开户日期：'+convert(char(10),getdate(),111)+'  开户金额：'+convert(varchar(20),@openMoney)
if not exists(select * from userinfo where PID=@PID)
 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)
go

--6.测试开卡，调用存储过程重新开户
exec proc_openAccount '王五','334456889012678','2222-63598978',8000,'活期','河南新乡'
exec proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期'

/*$$$$$$$$$$$$$   事  务  $$$$$$$$$$$$$$$$$$$*/
--1. 转账的事务存储过程
if exists(select * from sysobjects where name='proc_transfer')
 drop proc proc_transfer
go
create proc proc_transfer @card1 char(19),@card2 char(19),@outmoney money
as
begin
 print '开始转账，请稍候.....'
 declare @errors int
 set @errors=0
 exec proc_takeMoney @card1,@outmoney,'支取','123123'
 set @errors=@errors+@@error
 exec proc_takeMoney @card2,@outMoney,'存入'
 set @errors=@errors+@@error
 if(@errors>0)
  begin
   print '转账失败！'
   rollback tran
  end
 else
  begin
   print '转账成功！'
   commit tran
  end
end
go

--2.测试上述事务存储过程
--从李四的账户转账2000 到张三的账户
--现实中取款机依靠读卡器读出张三、李四的卡号，这里根据名字查出卡号来模拟
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_transfer @card1,@card2,2000

select * from userinfo
select * from cardinfo
select * from transinfo

/*$$$$$$$$$$$$$$$$$$   安  全   $$$$$$$$$$$$$$$$$$$$*/
--1.添加SQL登录账号
if not exists(select * from master.dbo.syslogins where loginname='sysAdmin')
 begin
  exec sp_addlogin 'sysAdmin','1234'  --添加SQL登录账号
  exec sp_defaultdb 'sysAdmin','bankDB'  --修改登录的默认数据库为bankDB
 end
go
--2.创建数据库用户
exec sp_grantdbaccess 'sysAdmin','sysAdminDBUser'
go
--3.给数据库用户授权
--为sysAdminDBUser 分配对象权限（增删改查的权限）
grant select,insert,update,delete,select on transinfo to sysAdminDBUser
grant select,insert,update,delete,select on userinfo  to sysAdminDBUser
grant select,insert,update,delete,select on cardinfo  to sysAdminDBUser
go