exec xp_cmdshell 'mkdir D:\bank',no_output
go
if exists (select * from sysdatabases where name='bankDB')
drop database bankDB
create database bankDB
on primary
(
name='bankDB_data',
filename='D:\bank\bankDB_data.mdf',
size=10mb,
filegrowth=10%
)
log on
(
name='bankDB_log',
filename='D:\bank\bankDB_log.ldf',
maxsize=50mb,
filegrowth=2
)
go
use bankDB
go
------------创建用户信息表
if exists(select * from sysobjects where name='userInfo')
drop table userInfo
go
create table userInfo
(
customerID int not null identity(1,1) primary key,
customerName varchar(30) not null,
PID bigint not null,
telephone varchar(13) not null,
address varchar(30) null
)
go
------------创建银行卡信息表
if exists(select * from sysobjects where name='cardInfo')
drop table cardInfo
go
create table cardInfo
(
cardID varchar(19) not null primary key,
curType varchar(10) not null,
savingType varchar(8) not null,
openDate datetime not null,
openMoney float not null,
balance float not null,
pass int not null,
IsReportLoss bit not null,
customerID int not null
)
go
------------创建交易信息表
if exists(select * from sysobjects where name='transInfo')
drop table transInfo
go
create table transInfo
(
transDate datetime not null,
cardID varchar(19) not null,
transType nchar(2) not null,
transMoney float not null,
remark varchar(30) null
)
go
---------添加约束
alter table userInfo
add constraint CK_PID check(len(PID)=18 or len(PID)=15),
constraint CK_telephone check(telephone like replicate('[0-9]',4)+'-'+replicate('[0-9]',8)
or telephone like replicate('[0-9]',3)+'-'+replicate('[0-9]',8) or len(telephone)=11)
go
alter table cardInfo
add constraint CK_cardID check(cardID like '1010 3576 '+replicate('[0-9]',4)+' '+replicate('[0-9]',4)),
constraint DF_curType default('RMB') for curType,
constraint CK_savingType check(savingType in('活期','定活两便','定期')),
constraint DF_openDate default(getDate()) for openDate,
constraint CK_openMoney check(openMoney >= 1),
constraint CK_balance check(balance >= 1),
constraint CK_pass check(len(pass)=6),
constraint DF_pass default(888888) for pass,
constraint DF_IsReportLoss default(0) for IsReportLoss,
constraint FK_customerID foreign key(customerID) references userInfo(customerID)
go
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 in('存入','支取')),
constraint CK_transMoney check(transMoney >0)
go
---------插入测试数据
insert into userInfo values('张三',123456789012345,'010-67898978','北京海淀')
insert into userInfo (customerName,PID,telephone) values('李四',321245678912345678,'0478-44443333')
insert into cardInfo values('1010 3576 1212 1134',default,'定期',default,1.00,1.00,default,0,2)
insert into cardInfo values('1010 3576 1234 5678',default,'活期',default,1000.00,1000.00,default,0,1)
---------插入交易信息
insert into transInfo(transType,cardID,transMoney) values('支取','1010 3576 1234 5678',900.00)
update cardInfo set balance=balance-900.00 where cardID='1010 3576 1234 5678'
insert into transInfo(transType,cardID,transMoney) values('存入','1010 3576 1212 1134',5000.00)
update cardInfo set balance=balance+5000.00 where cardID='1010 3576 1212 1134'
---------常规业务模拟
--(1)修改密码
update cardInfo set pass=123456 where cardID='1010 3576 1234 5678'
update cardInfo set pass=123123 where cardID='1010 3576 1212 1134'
--(2)银行挂失
update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'
--(3)统计银行的资金流通余额和盈利结算
declare @inMoney money --总存入量
declare @outMoney money --总支取量
select @inMoney=sum(transMoney) from transInfo where transType='存入'
select @outMoney=sum(transMoney) from transInfo where transType='支取'
print '银行流通余额总计为：'+convert(varchar,(@inMoney-@outMoney))+'RMB'
print '盈利总结为：'+convert(varchar,(@outMoney*0.008-@inMoney*0.003))+'RMB'
--(4)查询本周开户的卡号，显示该卡的相关信息
select userInfo.*,cardInfo.* from cardInfo
inner join userInfo on userInfo.customerID=cardInfo.customerID
where datepart(wk,getDate())=datepart(wk,openDate) and datediff(yy,openDate,getDate())=0
--(5)查询本月交易金额最最高的卡号
select distinct cardID as '卡号'from transInfo
where transMoney = (select max(transMoney) from transInfo where datediff(mm,transDate,getDate())=0)
--(6)查询挂失账号的客户的信息
select '客户姓名'=customerName,'身份证号'=PID,'电话号码'=telephone,'地址'=address
from userInfo
where customerID in (select distinct customerID from cardInfo where IsReportLoss=1)
--(7)催款提醒业务
select '客户姓名'=customerName,'联系电话'=telephone,'账上余额'=balance
from userInfo inner join cardInfo on userInfo.customerID=cardInfo.customerID
where balance<200
--(8)创建索引和视图
--创建索引
if exists(select * from sysindexes where name='index_cardID')
drop index transInfo.index_cardID
create nonclustered index index_cardID
on transInfo(cardID)
with fillfactor=70
go
select * from transInfo where cardID='1010 3576 1212 1134'
go
--创建视图
if exists(select * from sysobjects where name='view_userInfo')
drop view view_userInfo
go
create view view_userInfo
as
select '客户编号'=customerID,'开户名'=customerName,'身份证号'=PID,'电话'=telephone,'地址'=address
from userInfo
go
if exists(select * from sysobjects where name='view_cardInfo')
drop view view_cardInfo
go
create view view_cardInfo
as
select '卡号'=cardID,'货币种类'=curType,'存款类型'=savingType,'开户日期'=openDate,'开户金额'=openMoney
,'余额'=balance,'密码'=pass,'是否挂失'=IsReportLoss,'客户编号'=customerID
from cardInfo
go
if exists(select * from sysobjects where name='view_Info')
drop view view_Info
go
create view view_Info
as
select '交易日期'=transDate,'卡号'=cardID,'交易类型'=transType,'交易金额'=transMoney,'备注'=remark
from transInfo
go
--(9)创建存储过程
drop procedure proc_takeMoney
create procedure proc_takeMoney
@card char(19),--用户的卡号
@m money,--存入或支取的钱的数量
@type char(4), --支取还是存入
@inputPass char(6)='' --密码
as
print '交易正在进行，请稍候..'
if(@type='支取')
begin
declare @balance float --在交易前的余额
select @balance=balance from cardInfo where cardID=@card and pass=@inputPass
if(@balance-@m>=1)
begin
insert into transInfo(transType,cardID,transMoney) values(@type,@card,@m)
update cardInfo set balance=balance-@m where cardID=@card
print '支取交易成功！交易金额：'+convert(varchar,@m)
print '卡号'+@card+' 余额:'+convert(varchar,@balance-@m)
end
else
begin
raiserror('支取交易失败！余额不足',16,123)
print '卡号'+@card+' 余额:'+convert(varchar,@balance)
return 1
end
end
else if(@type='存入')
begin
declare @balance2 float --交易成功或者失败的余额
select @balance2=balance from cardInfo where cardID=@card
insert into transInfo(transType,cardID,transMoney) values(@type,@card,@m)
update cardInfo set balance=balance+@m where cardID=@card
print '存入交易成功！交易金额：'+convert(varchar,@m)
print '卡号'+@card+' 余额:'+convert(varchar,@balance2+@m)
end
else
begin
declare @balance3 float --交易成功或者失败的余额
select @balance3=balance from cardInfo where cardID=@card
raiserror('存入交易失败!',16,1)
print '卡号'+@card+' 余额:'+convert(varchar,@balance3)
end
go
--张三支取300元
declare @cardID varchar(19) --卡号
select @cardID=cardID from cardInfo
where customerID=(select customerID from userInfo where customerName='张三')
exec proc_takeMoney @cardID,300,'支取','123456'
go
--李四存入500元
declare @cardID varchar(19)
select @cardID=cardID from cardInfo
where customerID=(select customerID from userInfo where customerName='李四')
exec proc_takeMoney @cardID,500,'存入'
go
--产生随机卡号的存储过程
drop proc proc_randCardID
create procedure proc_randCardID @randCardID char(19) output
as
declare @r numeric(15,8)
declare @tempStr varchar(10)
select @r=rand(convert(int,(datepart(mm,getdate())*10000+datepart(ss,getdate())*1000+(datepart(ms,getdate())))))
set @tempStr=convert(varchar,@r)
print '生成的随机数:'+@tempStr
set @randCardID='1010 3576 '+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4)
go
declare @myCardID char(19)
exec proc_randCardID @myCardID output
print '产生的随机卡号为：'+@myCardID
--开户的存储过程
create procedure proc_openAccount @customerName char(8),@PID char(18),
@telephone char(13),@openMoney money,@savingType char(8),@address varchar(50)=' '
as
set nocount on
declare @mycardID char(19)
declare @cur_customerID int --根据身份证查出的客户的ID
exec proc_randCardID @mycardID output --随机获得卡号
while exists(select * from cardInfo where cardID=@mycardID)
exec proc_randCardID @mycardID output
insert into userInfo (customerName,PID,telephone,address)
values(@customerName,@PID,@telephone,@address)
print '尊敬的客户，开户成功！系统为您产生的随机卡号为：'+@mycardID
print '开户日期'+convert(varchar,getDate())+' 开户金额：'+convert(varchar,@openMoney)
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
exec proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡'
exec proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期'
--(10)创建事务
drop proc proc_transfer
create proc proc_transfer @card1 char(19),@card2 char(19),@outmoney money
as
declare @return int --接受返回的值
set @return=0
begin transaction
print '开始转账，请稍后...'
exec @return=proc_takeMoney @card1,@outmoney,'支取','123123'
if (@return=0)
exec proc_takeMoney @card2,@outmoney,'存入'
else
exec proc_takeMoney @card2,@outmoney,''
if(@return=0)
begin
print '转账成功！'
commit transaction
end
else
begin
print '转帐失败！'
rollback transaction
end
go
declare @cardID1 varchar(19) --李四卡号
declare @cardID2 varchar(19) --张三卡号
select @cardID1=cardID from cardInfo where customerID=(select customerID from userInfo where customerName='张三')
select @cardID2=cardID from cardInfo where customerID=(select customerID from userInfo where customerName='李四')
exec proc_transfer @cardID2,@cardID1,2000
--(11)创建登录账号和数据库用户
exec sp_addlogin 'sysAdmin2','1234' --添加SQL登录账号
go
exec sp_grantdbaccess 'sysAdmin2','sysAdminDBUser' --创建数据库用户
go
grant select,insert,update,delete on transInfo to sysAdminDBUser --数据库用户授权
go
