use master
go
--★★★★★★★★★★★★在E盘创建文件夹bank★★★★★★★★★★★★★
exec xp_cmdshell 'mkdir e:/bank',no_output
go
--★★★★★★★★★★★★★创建数据库bankDB★★★★★★★★★★★★★
if exists(select * from sysdatabases where name = 'bankDB')
drop database bankDB
go
create database bankDB
on
(
name = 'bankDB_data',
filename = 'e:/bank/bankDB_data.mdf',
size = 10MB,
filegrowth = 10%
)
log on
(
name = 'bankDB_log',
filename = 'e:/bank/bankDB_log.ldf',
size = 10MB,
filegrowth = 10%
)
go
use bankDB
go
--★★★★★★★★★★★★★创建数据库表★★★★★★★★★★★★★
if exists(select * from sysobjects where name = 'userInfo')
drop table userInfo
if exists(select * from sysobjects where name = 'cardInfo')
drop table cardInfo
if exists(select * from sysobjects where name = 'transInfo')
drop table transInfo
go
--※※※※※※※※※※※※※※userInfo表※※※※※※※※※※※※※※
create table userInfo
(
customerID int identity not null,
customerName varchar(10) not null,
PID varchar(18) not null,
telephone varchar(15) not null,
address text
)
go
--约束
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]'
or telephone like '[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
--插入测试数据
insert into userInfo(customerName,PID,telephone,address)
values('张三','430321198548758762','0731-6789798','长沙识字岭')
insert into userInfo(customerName,PID,telephone,address)
values('李四','49875124414787458X','12547896582','北京海淀')
go
select * from userInfo
go
--※※※※※※※※※※※※※※cardInfo表※※※※※※※※※※※※※※
create table cardInfo
(
cardID varchar(20) not null,
curType varchar(10) not null,
savingType varchar(10) not null,
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 PK_cardID primary key(cardID),
constraint CK_cardID check(cardID like '1010 3576 %'),
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(pass like'[0-9][0-9][0-9][0-9][0-9][0-9]'),
constraint DF_pass default('888888') for pass,
constraint DF_isReportLoss default(0) for isReportLoss,
constraint FK_customerID foreign key(customerID) references userInfo(customerID)
go
--插入测试数据
insert into cardInfo values('1010 3576 1212 1134',default,'定期',default,1,1,default,default,1)
insert into cardInfo values('1010 3576 1234 5678',default,'活期',default,1000,1000,default,default,2)
go
select * from cardInfo
go
--※※※※※※※※※※※※※※transInfo表※※※※※※※※※※※※※※
create table transInfo
(
transDate datetime not null,
cardID varchar(20) not null,
transType char(4) not null,
transMoney money not null,
remark text
)
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 = '存入' or transType = '支取'),
constraint CK_transMoney check(transMoney>0)
go
--插入测试数据
insert into transInfo values(default,'1010 3576 1234 5678','支取',900,null)
insert into transInfo values(default,'1010 3576 1212 1134','存入',5000,null)
go
select * from transInfo
go
--★★★查看所有测试数据★★★
select * from userInfo
select * from cardInfo
select * from transInfo
go
-- ★★★★★★★★★★★★★★★★★★★★★★业务:修改密码★★★★★★★★★★★★★★★★★★★★★★
if exists (select * from sysobjects where name = 'proc_pass')
drop procedure proc_pass
go
create procedure proc_pass
@cardID varchar(20) , @oldpwd int , @newpwd int
as
begin transaction --开启事物
declare @passError int --定义变量检查错误
set @passError = 0
if exists(select * from cardInfo where cardID = @cardID)
begin
declare @pass int
select @pass = pass from cardInfo where cardID = @cardID
if(@oldpwd = @pass)
begin
update cardInfo set pass = @newpwd where cardID = @cardID
set @passError = @passError+@@error
end
else
begin
raiserror('旧密码不正确',11,22)
rollback
return
end
end
else
begin
raiserror('没有此卡',13,65)
rollback
return
end
if(@passError<>0)
begin
print'修改密码失败'
rollback
end
else
begin
print '修改密码成功'
commit
end
go
-- ★★★★★★★★★★★★★★★★★★★★★★业务:挂失账号★★★★★★★★★★★★★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_guashi')
drop procedure proc_guashi
go
create procedure proc_guashi
@cardID varchar(20) , @pass int
as
begin transaction --开启事物
declare @lossError int
set @lossError = 0
if exists(select * from cardInfo where cardID = @cardID)
begin
declare @pwd int
select @pwd = pass from cardInfo where cardID = @cardID
if(@pass = @pwd)
begin
update cardInfo set isReportLoss = 1 where cardID = @cardID
set @lossError = @lossError+@@error
end
else
begin
raiserror('密码不正确',11,22)
rollback
return
end
end
else
begin
raiserror('没有此账号',13,65)
rollback
return
end
if(@lossError<>0)
begin
print '挂失帐号失败'
rollback
end
else
begin
print '挂失帐号成功'
commit
end
go
--★★★★★★★★★★★★★★★★★业务,统计资金流通余额和盈利结算★★★★★★★★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_count')
drop procedure proc_count
go
create procedure proc_count
as
declare @inMoney money , @outMoney money , @countError int
select @inMoney = sum(transMoney) from transInfo where transType = '存入'
select @outMoney = sum(transMoney)from transInfo where transType = '支取'
print '银行流通余额统计为:'+convert(varchar(20),(@inMoney - @outMoney))+'RMB'
set @countError = @countError+@@error
print '盈利结算为:'+convert(varchar(20),(@outMoney*0.008) - (@inMoney*0.003))+'RMB'
set @countError = @countError+@@error
if(@countError<>0)
print '统计失败'
go
--★★★★★★★★★★业务,查询本周开户的卡号,显示有关信息★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_search')
drop procedure proc_search
go
create procedure proc_search
as
print '本周开卡账号信息'
select * from cardInfo where openDate in
(select openDate from cardInfo where datediff(dd,getdate(),openDate)<datepart(dw,getdate()))
go
--★★★★★★★★★★业务,本月交易金额最高的卡号★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_heigh')
drop procedure proc_heigh
go
create procedure proc_heigh
as
print '本月交易金额最高的卡号'
select distinct(transInfo.cardID) from transInfo where
transMoney = (select max(transMoney) from transInfo
where datepart(mm,transDate)=datepart(mm,getdate()))
go
--★★★★★★★★★★★★★★★★★业务,查询挂失账号的客户信息★★★★★★★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_lossMsg')
drop procedure proc_lossMsg
go
create procedure proc_lossMsg
as
print '挂失账号的客户信息'
select 客户姓名 = customerName,客户身份证号 = PID,客户联系电话 = telephone ,客户地址 = address
from userInfo where customerID = (select customerID from cardInfo where isReportLoss = 1)
go
-- ★★★★★★★★★★★★★★★★★★★★★★业务,催款提醒★★★★★★★★★★★★★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_giveMsg')
drop procedure proc_giveMsg
go
create procedure proc_giveMsg
as
print '需要催款的用户:'
select 客户姓名 = userInfo.customerName,客户联系电话 = userInfo.telephone, 账上余额 = cardInfo.balance
from userInfo inner join cardInfo on userInfo.customerID = cardInfo.customerID and cardInfo.balance<200
go
--★★★★★★★★★★给transInfo表的cardID列创建重复索引★★★★★★★★★★
if exists(select * from sysobjects where name = 'ix_transInfo_cardID')
drop index transInfo.ix_transInfo_cardID
go
create index ix_transInfo_cardID
on transInfo(cardID)
with fillfactor = 70
go
--★★★★★★★★★★★★★★★★★★★★★★为三个表创建三个视图★★★★★★★★★★★★★★★★★★★★★★
--/userInfo表
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
--///cardInfo表
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
--//transInfo表
if exists(select * from sysobjects where name = 'view_transInfo')
drop view view_transInfo
go
create view view_transInfo
as
select 交易日期 = transDate,卡号 = cardID,交易类型 = transType,交易金额 = transMoney,
备注 = remark from transInfo
go
--★★★★★★★★★★★★★★★★★★★★★★在transInfo表中创建触发器★★★★★★★★★★★★★★★★★★★★★★
if exists(select name from sysobjects where name = 'trig_trans')
drop trigger trig_trans
go
create trigger trig_trans
on transInfo
for insert
as
declare @ID varchar(20),@Type char(4),@Money money
declare @transError int
set @transError = 0
select @ID = cardID,@Type = transType,@Money = transMoney from inserted
if(@Type = '支取')
begin
declare @mybalance money
select @mybalance = balance from cardInfo where cardID = @ID
if((@mybalance - @money)>1)
begin
update cardInfo set balance = @mybalance - @Money where cardID = @ID
set @transError = @transError+@@error
end
else
begin
raiserror('余额不足',13,57)
rollback
return
end
end
else if(@Type = '存入')
begin
update cardInfo set balance = balance + @Money where cardID = @ID
set @transError = @transError+@@error
end
if(@transError<>0)
begin
print '交易失败'
rollback
return
end
else
begin
print '交易成功'
print '交易金额:'+convert(varchar(20),@Money)
return
end
go
--★★★★★★★★★★★★★★★★★★★★★★业务,取钱或存钱的存储过程★★★★★★★★★★★★★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_takeMoney')
drop procedure proc_takeMoney
go
create procedure proc_takeMoney
@ID varchar(20),
@type char(4),
@money money,
@mark text
as
begin transaction
declare @mybalance money
declare @procError int
set @procError = 0
if exists(select * from cardInfo where cardID = @ID)
begin
if(@type<>'存入' and @Type<>'支取')
begin
raiserror('不支持此操作',13,48)
rollback
return
end
else if(@type = '支取')
begin
select @mybalance = balance from cardInfo where cardID = @ID
if((@mybalance - @money)>1)
begin
insert into transInfo values(default,@ID,@type,@money,@mark)
set @procError = @procError+@@error
end
else
begin
raiserror('余额不足',13,57)
rollback
return
end
end
else if(@type = '存入')
begin
insert into transInfo values(default,@ID,@type,@money,@mark)
set @procError = @procError+@@error
end
end
else
begin
raiserror('没有此卡',12,54)
rollback
return
end
if(@procError<>0)
begin
print '交易失败'
rollback
end
else
begin
print '交易成功'
print '余额:'
select balance from cardInfo where cardID = @ID
commit
end
go
--★★★★★★★★★★★★★★★★★★★★★★产生随机卡号的存储过程★★★★★★★★★★★★★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_randCardID')
drop procedure proc_randCardID
go
create procedure proc_randCardID
@randCardID char(19) output
as
declare @rand numeric(15,8)
declare @temStr char(10)
select @rand = rand((datepart(mm,getdate())*100000)+(datepart(ss,getdate())*1000)+datepart(ms,getdate()) )
set @temStr = convert(varchar(20),@rand)
set @randCardID = '1010 3576'+' '+substring(@temStr,3,4)+' '+substring(@temStr,7,4)
go
--★★★★★★★★★★★★★★★★★★★★★★开户的存储过程★★★★★★★★★★★★★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_openAcount')
drop procedure proc_openAcount
go
create procedure proc_openAcount
@customerName varchar(10),@PID varchar(18),@telephone varchar(15),
@openMoney money,@savingType varchar(10),@address text = ' '
as
begin transaction
declare @openCardID varchar(20)
declare @openError int
set @openError = 0
exec proc_randCardID @openCardID output
while exists(select * from cardInfo where cardID = @openCardID)
begin
exec proc_randCardID @openCardID output
continue
end
insert into userInfo (customerName,PID,telephone,address) values(@customerName,@PID,@telephone,@address)
set @openError = @openError+@@error
declare @card_customerID int
select @card_customerID = customerID from userInfo where PID = @PID
insert into cardInfo (CardID,curType,savingType,openDate,openMoney,balance,pass,isReportLoss,customerID)
values(@openCardID,default,@savingType,default,@openMoney,@openMoney,default,default,@card_customerID)
set @openError = @openError+@@error
if(@openError<>0)
begin
print '开户失败'
rollback
end
else
begin
print '尊敬的客户,开户成功!'
print '系统为您提供的随机卡号为:'+@openCardID
print '开户日期:'+convert (varchar(20),getdate())
print '开户金额:'+convert(varchar(10),@openMoney)
commit
end
go
--★★★★★★★★★★★★★★★★★★★★★★业务,转帐★★★★★★★★★★★★★★★★★★★★★★
if exists(select * from sysobjects where name = 'proc_transfer')
drop procedure proc_transfer
go
create procedure proc_transfer
@outID varchar(20),
@inID varchar(20),
@transMoney money
as
begin transaction
declare @transError int
set @transError = 0
declare @balance1 money,@balance2 money
set @balance1 = 0
set @balance2 = 0
if exists(select * from cardInfo where cardID = @outID)
begin
if exists(select * from cardInfo where cardID = @inID)
begin
declare @mybalance money
select @mybalance = balance from cardInfo where cardID = @outID
if((@mybalance - @transMoney) <1)
begin
raiserror('转帐方卡内余额不足',13,57)
rollback
return
end
else
begin
insert into transInfo values(default,@outID,'支取',@transMoney,null)
set @transError = @transError+@@error
insert into transInfo values(default,@inID,'存入',@transMoney,null)
set @transError = @transError+@@error
select @balance1 = balance from cardInfo where cardID = @outID
select @balance2 = balance from cardInfo where cardID = @inID
end
end
else
begin
raiserror('接收方卡号不存在',15,23)
rollback
return
end
end
else
begin
raiserror('转帐方卡号不存在',15,23)
rollback
return
end
if(@transError<>0)
begin
print '转账失败'
rollback
end
else
begin
print '开始转帐,请稍后……'
print '交易成功!交易金额:'+convert(varchar(20),@transMoney)
print '卡号:'+@outID+' '+'余额:'+convert(varchar(20),@balance1)
print '卡号:'+@inID+' '+'余额:'+convert(varchar(20),@balance2)
print '转帐成功'
commit
end
go
--★★★★★★★★★★创建登陆帐号和数据库用户★★★★★★★★★★
exec sp_addlogin 'dhy','dhypassword'
exec sp_grantdbaccess 'dhy','dhyDBuser'
grant select,insert,update,delete on userInfo to dhyDBuser
grant select,insert,update,delete on cardInfo to dhyDBuser
grant select,insert,update,delete on transInfo to dhyDBuser
go
--★★★★★★★★★★★★★★★★★★测试★★★★★★★★★★★★★★★★★★
exec proc_pass '1010 3576 1212 1134','888888','123456' --修改密码
go
exec proc_guashi '1010 3576 1212 1134','123456' --挂失帐号
go
exec proc_count --统计资金流通余额和盈利结算
go
exec proc_search --查询本周开户的卡号
go
exec proc_heigh --本月交易金额最高的卡号
go
select * from transInfo (index = ix_transInfo_cardID) where cardID = '1010 3576 1212 1134'
go --索引
insert into transInfo values(default,'1010 3576 1234 5678','存入',1000,null)
insert into transInfo values(default,'1010 3576 1234 5678','支取',1000,null)
go --触发器
exec proc_takeMoney '1010 3576 1212 1134','存入',1000,null
go --存钱取钱的存储过程
declare @myCardID char(19)
exec proc_randCardID @myCardID output
print '产生的随机号为:'+ @myCardID
go --产生随机数
exec proc_openAcount '王五','430214125407658420','2222-4587458',1000,'活期','候家岭'
exec proc_openAcount '赵六','126548521456212301','5848-1254879',2100,'定期',''
go --开户
exec proc_transfer '1010 3576 1234 5678','1010 3576 1212 1134',100
go --转帐
--★★★★★★★★★★三个视图★★★★★★★★★★
select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo
go
--★★★★★★★★★★查看所有数据★★★★★★★★★★
select * from userInfo
select * from cardInfo
select * from transInfo
go