bank.sql

这是一个用数据库做的小小的ATM银行取款机。用C＃做的

/*------催款提醒：例如某种业务的需要，每个月末，如果发现用户帐上余额少于200元，将致电催款。---*/
SELECT customerName as 客户姓名,telephone as 联系电话,balance as 帐上余额 
      FROM userInfo INNER JOIN cardInfo ON  userInfo.customerID=cardInfo.customerID WHERE balance<200
/*$$$$$$$$$$$$$索引和视图$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/
--1.创建索引：给交易表的卡号cardID字段创建重复索引
create NONCLUSTERED INDEX index_cardID ON transInfo(cardID)WITH FILLFACTOR=70
--2.按指定索引查询 张三（卡号为1010 3576 1212 1134）的交易记录
GO
SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 1234 5678'
GO
--3.创建视图：为了向客户显示信息友好,查询各表要求字段全为中文字段名。
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
 
/*$$$$$$$$$$$$$触发器$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/
--改进上述的存款或取款语句，当存钱或取钱（如500元）时候，会往交易信息表transInfo中添加一条交易记录，同时会自动更新用户信息表：userInfo中的现有金额的变化（如增加/减少500元）
  --drop trigger trig_trans
CREATE TRIGGER trig_trans ON transInfo FOR INSERT
  AS
    DECLARE @myTransType char(4),@outMoney MONEY,@myCardID char(19)
    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
--测试触发器：张三的卡号支取1000，李四的卡号存入200
 --现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟
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,1000)
GO
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,200)
GO

/*$$$$$$$$$$$$$$$$$$$$$$存储过程$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/
--1.取钱或存钱的存储过程
  --drop proc proc_takeMoney
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
   INSERT INTO transInfo(transType,cardID,transMoney) VALUES(@type,@card,@m)
GO
--2.调用存储过程取钱或存钱 张三取300，李四存500
 --现实中的取款机依靠读卡器读出张三的卡号,这里根据张三的名字查出考号来模拟
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
--3.产生随机卡号的存储过程(一般用当前月份数\当前秒数\当前毫秒数乘以一定的系数作为随机种子)
  --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
--4.测试产生随机卡号
DECLARE @mycardID char(19) 
EXECUTE proc_randCardID @mycardID OUTPUT
print '产生的随机卡号为：'+@mycardID
GO
--5.开户的存储过程
   --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 
     --调用产生随机卡号的存储过程获得随机卡号
     EXECUTE proc_randCardID @mycardID OUTPUT
     while  exists(SELECT * FROM cardInfo WHERE cardID=@mycardID) 
        EXECUTE 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',1000,'活期','河南新乡' 
EXEC proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期' 
select * from view_userInfo
select * from view_cardInfo
GO

/*$$$$$$$$$$$$$$$$$$$$$$事   务$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/
--1.转帐的事务存储过程
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

--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 view_userInfo
select * from view_cardInfo
select * from view_transInfo
GO
/*$$$$$$$$$$$$$$$$$$$$$$安    全$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$*/
--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