ATM

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