ATM

 ATM取款机系统
create database bankDB
on
(
name='sa',
filename='E:/bank/sa',
filegrowth=15%
)
log on
(
name='as',
filename='E:/bank/as',
filegrowth=2mb
)
go

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

create database bankDB
on
(
name='sa',
filename='E:/bank/sa',
filegrowth=15%
)
log on
(
name='as',
filename='E:/bank/as',
filegrowth=2mb
)
go

/*----创建用户信息表---*/
create table userInfo
(
customerID varchar(15) not null,
customerName varchar(15) not null,
PID varchar(20) not null,
telephone varchar(15) not null,
address varchar(15)
)
go

use bankDB
go
if exists(select*from sysobjects where name='userInfo')
   drop table userInfo

select*from userInfo

/*----创建银行卡信息表----*/
create table cardInfo
(
cardID varchar(20) not null,
curType varchar(15) not null,
savingType varchar(15) not null,
openDate Datetime not null,
openMoney money not null,
balance money not null,
pass varchar(15) not null,
IsReportLoss varchar(10) not null,
customerID varchar(15) not null
)
go

use bankDB
go
if exists(select*from sysobjects where name='cardInfo')
    drop table cardInfo

select*from cardInfo

/*----创建交易信息表----*/
create table transInfo
(
transDate datetime not null,
cardID varchar(20) not null,
transType varchar(15) not null,
transMoney money not null,
remark varchar(80)
)
go

use bankDB
go
if exists(select*from sysobjects where name='transInfo')
   drop table transInfo

select*from transInfo

/*----添加用户信息表的约束----*/
alter table userInfo
add constraint pk_customerID primary key(customerID),
     constraint ck_PID check(len(PID)=18 or len(PID)=15),
     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)
go

alter table userInfo
drop constraint ck_telephone

/*----添加银行卡信息表的约束----*/
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 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_IsReportLoss default(0)for IsReportLoss,
      constraint fk_customerID foreign key(customerID)references userInfo(customerID)
go

alter table cardInfo
drop constraint ck_pass

/*---添加交易信息表的约束---*/
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('1','张三','123456789012345','010-67898978','北京海淀')

insert into userInfo
values('2','李四','321245678912345678','0478-74249914','null')

delete from userInfo

select*from userInfo

insert into cardInfo
values('1010 3576 1212 1134','RMB','定期','2005-10-1 09:10:53',1.0000,1.0000,'888888','0','2')
insert into cardInfo
values('1010 3576 1234 5678' ,'RMB','活期','2005-10-1 22:20:15',1000.00,1000.00,'888888','0','1')

delete from cardInfo

select*from cardInfo

/*-------交易信息表插入交易记录-------*/
insert into transInfo(transDate,transType,cardID,transMoney)
      values('2005-10-1 09:11:15','支取','1010 3576 1234 5678',900)
/*-------更新银行卡信息表中的现有余额---------*/
update cardInfo set balance=balance-900 where cardID='1010 3576 1234 5678'

/*------交易信息表插入交易记录--------*/
insert into transInfo(transDate,transType,cardID,transMoney)
      values('2005-10-1 22:20:55','存入','1010 3576 1212 1134',5000)
/*-------更新银行卡信息表中的现有余额---------*/
update cardInfo set balance=balance+5000 where cardID='1010 3576 1212 1134'
GO
    
select*from transInfo

delete from transInfo

/*----------常规业务模拟-----------*/
/*---修改密码---*/
update cardInfo set pass='123456' where cardID='1010 3576 1212 1134'
update cardInfo set pass='123123' where cardID='1010 3576 1234 5678'
/*---挂失帐号---*/
update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'

select*from cardInfo

/*----统计银行的资金流通余额和盈利结算----*/
declare @inMoney money
declare @outMoney money
declare @jieMoney money
declare @liuMoney money
select @inMoney=sum(transMoney) from transInfo where (transType='存入')
select @outMoney=sum(transMoney)from transInfo where (transType='支取')
set @liuMoney=@inMoney-@outMoney
print '银行流通余额总计为:'+ convert(varchar(20),@liuMoney)+'RMB'
set @jieMoney=@outMoney*0.008-@inMoney*0.003
print '盈利结算为:'+ convert(varchar(20),@jieMoney)+'RMB'

select*from transInfo

/*----查询本周开户的卡号,显示卡的相关信息----*/
print '不知道'

/*----查询挂失帐号的客户信息----*/
select * from userInfo where customerID in(select customerID from cardInfo where IsReportLoss=1)

/*---查询本月交易最高的卡号---*/

select distinct cardID from transInfo where transMoney=(select max(transMoney) from transInfo)

/*---催款提醒业务---*/
select customerName as '姓名',telephone as '联系电话',balance as '账上余额'from userInfo
inner join cardInfo on userInfo.customerID=cardInfo.customerID where balance<200
go

/*----创建索引和视图----*/
/*---创建重复索引---*/
create nonclustered index index_cardID on transInfo(cardID)with fillfactor=70
go
/*---按指定索引查询张三的交易记录---*/
select * from transInfo (index=index_cardID) where cardID='1010 3576 1234 5678'
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 密码,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

/*-----创建触发器，使交易的数据自动更新-------*/
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 @mytransType varchar(15),@outMoney money,@myCardID varchar(20)
select @mytransType=transType,@outMoney=transMoney,
       @myCardID=cardID from inserted
      declare @mybalance money
    select @mybalance=balance from cardInfo where cardID=@mycardID
    if(@mytransType='支取')
       if(@mybalance>=@outMoney+1)
           update cardInfo set balance=balance-@outMoney where cardID=@myCardID
       else
          begin
              raiserror('交易失败！余额不足',16,1)
              rollback tran
              print '卡号'+@myCardID+' 余额：'+convert(varchar(20),@mybalance)  
            return
          end
      else
          update cardInfo set balance=balance+@outMoney where cardID=@myCardID
              print '交易成功！交易金额：'+convert(varchar(20),@outMoney)
              select @mybalance=balance from cardInfo where cardID=@myCardID
              print '卡号'+@myCardID+' 余额：'+convert(varchar(20),@mybalance)  
go

/*------测试触发器------*/
set nocount on
declare @card char(19)
select @card=cardID from cardInfo inner join userInfo on
         cardInfo.customerID=userInfo.customerID where customerName='张三'----测试张三的触发过程！！！
   insert into transInfo (transType, cardID,transMoney)
   values('支取',@card,10000)

declare @card char(19)
select @card=cardID from cardInfo inner join userInfo on
         cardInfo.customerID=userInfo.customerID where customerName='李四'----测试李四的触发过程！！
   insert into transInfo (transType, cardID,transMoney)
   values('支取',@card,100)

declare @card char(19)
select @card=cardID from cardInfo inner join userInfo on
         cardInfo.customerID=userInfo.customerID where customerName='张三'----测试张三的触发过程！！！
   insert into transInfo (transType, cardID,transMoney)
   values('存入',@card,10000)

go

            
select*from view_userInfo
select*from view_cardInfo
select*from view_transInfo

/*--------创建取钱的相关存储过程--------*/
if exists(select*from sysobjects where name='pro_takeMoney')
   drop procedure pro_takeMoney
go
create procedure proc_takeMoney
@card char(19),
@m money,@type char(4),
@inputpass char(6)=''
as
    print '准备交易,请稍后.....'
    if(@type='支取')
        if((select pass from cardInfo where cardID =@card ) <>@inputpass)
          begin
              raiserror('密码错误！无法继续',16,1)
              return
          end

set nocount on
insert into transInfo(transType,cardID,transMoney)
values('存入','1010 3576 1212 1134',5100)
insert into transInfo(transType,cardID, transMoney)
values('支取','1010 3576 1234 5678',100)

--------------调用存储过程取钱或存钱

declare @card char(19)
select @card=cardID from cardInfo inner join userInfo ON
   cardInfo.customerID=userInfo.customerID where customerName='张三'
exec proc_takeMoney @card,300 ,'支取','123456'
go

declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo ON
   cardInfo.customerID=userInfo.customerID where customerName='李四'
exec proc_takeMoney @card,500 ,'存入'
select * from view_cardInfo
select * from view_transInfo
go
---------------产生随机卡号的存储过程
--drop proc proc_randCardID
create procedure proc_randCardID @randCardID char(19) output
as
    declare @r numeric(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) --产生0.xxxxxxxx的数字,我们需要小数点后的八位数字
    set @randCardID='1010 3576 '+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4) --组合为规定格式的卡号
go
--------------测试产生随机卡号
declare @mycardID char(19)
exec proc_randCardID @mycardID output
print '产生的随机卡号为：'+@mycardID
go
-------------开户的存储过程
   --drop proc proc_openAccount
create procedure proc_openAccount @customerName char(8),@PID char(18),@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

--------调用存储过程重新开户
exec proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡'
exec proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期'
select * from view_userInfo
select * from view_cardInfo
go

-------转帐的事务存储过程
create procedure proc_transfer @card1 char(19),@card2 char(19),@outmoney money
as
   begin tran
     print '开始转帐,请稍后......'
     declare @errors int
     set @errors=0
   insert into transInfo(transType,cardID,transMoney) values('支取',@card1,@outmoney)
     set @errors=@errors+@@error
    insert into transInfo(transType,cardID,transMoney) values('存入',@card2,@outmoney)
     set @errors=@errors+@@error
     if (@errors>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_transfer @card1,@card2,2000

select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo
go

---------添加SQL登录帐号
If not exists(SELECT * FROM master.dbo.syslogins WHERE loginname='sysAdmin')
    begin
     exec sp_addlogin 'sysAdmin', '1234'   
      exec   sp_defaultdb 'sysAdmin' , 'bankDB'
    end
go
--------创建数据库用户
exec sp_grantdbaccess 'sysAdmin', 'sysAdminDBUser'
go
-------给数据库用户授权
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