ACCP4.0项目实战源码之SQL ATM

/**/ /* 作者:花满天
   QQ：122273014
   
*/

-- 1 建库
  -- 1.1 在D盘创建bank文件夹
  use  master
  exec  xp_cmdshell  ' mkdir d:ank ' ,NO_OUTPUT 
go
 
  --  1.2 检测是否存在bankDB 
 
    if   exists ( select   *   from  sysdatabases  where  name = ' bankDB '  )
     begin
       drop   database  bankDB
     end
  go
  -- 1.3 建bankDB
  create   database  bankDB    --  数据库的逻辑名称
on   primary              -- 默认的组(可不写)
(
 name = ' bankDB ' ,           
 filename = ' d:ankankDB.mdf ' ,  -- 数据库的物理文件
 size = 5 ,
 maxsize = 100 ,
 filegrowth = 15 %
)
log   on                            -- 数据库的日志文件
(
 name = ' bankDB_log ' ,
 filename = ' d:ankankDB.ldf ' ,
 size = 2 ,
 filegrowth = 1
 
)
GO
-- 2 建表加约束 
  use  bankDB 

-- (因数据库是肯定是刚创好的，所以可以不写 drop table userInfo)
   -- 2.1 create table userInfo
    create   table  userInfo
 (
  customerID    int              identity ( 1 , 1 )  primary   key  ,
  customerName  varchar ( 20 )     not   null ,
  PID           varchar ( 18 )     not   null    unique   check  ( len (PID) = 18    or   len (PID) = 15 ),
  telephone     varchar ( 20 )     not   null    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   len (telephone) = 11 ),
  address       varchar ( 50 )     null
 )
  go   

  --  2.2 create table cardInfo
   create   table  cardInfo
  (
   cardID      varchar ( 20 )    primary   key   check ( cardId  like   ' 1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9] ' ),
   curType     varchar ( 10 )     not   null   default ( ' RMB ' ),
   savingType  varchar ( 10 )    check  (savingType  in ( ' 活期 ' , ' 定活两便 ' , ' 定期 ' )),
   openDate     datetime       not   null   default  ( getdate ()),
   openMoney    money          not   null   check  (openMoney >= 1 ),
   balance      money          not   null   check  (balance >= 1 ),
   pass  char ( 6 )  not   null      default   ' 888888 '   check (  len (pass) = 6 ),
   IsReportLoss  varchar ( 2 )   default   ' 否 '   check (IsReportLoss  in ( ' 是 ' , ' 否 ' )),
   customerID     int          foreign   key   references  userInfo(customerID)
  )
go
  --  2.3 create table transDate
  create   table  transInfo
(
   transDate    datetime       not   null   default ( getdate ()),
   cardID       varchar ( 20 )   not   null   foreign   key   references  cardInfo(cardID),
   transType    char ( 4 )       check (transType  in  ( ' 存入 ' , ' 支取 ' )),
   transMoney   money          not   null   check (transMoney  > 0 ),
   remark       varchar ( 40 )
)

go
-- 3 写入测试数据 (人工)


   -- 3.1 insert into UserInfo
insert   into  userInfo(customerName,PID,telephone,address)  values ( ' 张三 ' , ' 123456789012345 ' , ' 0010-67898978 ' , ' 北京海淀 ' )
insert   into  userInfo(customerName,PID,telephone)          values ( ' 李四 ' , ' 321245678912345678 ' , ' 0478-44443333 ' )
go
   -- 3.2 insert into cardInfo
insert   into  cardInfo(cardID,curType,savingType,openDate,openMoney,balance,customerID)  values ( ' 1010 3576 1212 1134 ' , default , ' 定期 ' , default , 1 , 1 , 2 )
insert   into  cardInfo(cardID,curType,savingType,openDate,openMoney,balance,customerID)  values ( ' 1010 3576 1234 5678 ' , default , ' 活期 ' , default , 1000 , 1000 , 1 )
go
   -- 3.3 insert into transInfo
insert   into  transInfo(transDate,transType,cardID,transMoney) values ( getDate (), ' 支取 ' , ' 1010 3576 1212 1134 ' , 900 )
insert   into  transInfo(transDate,transType,cardID,transMoney) values ( getDate (), ' 存入 ' , ' 1010 3576 1234 5678 ' , 5000 )
go
-- 显示
select   *   from  userInfo
select   *   from  cardInfo
go

-- 4 常规业务模似
   -- 4.1 修改用户密码
  update  cardinfo  set  pass = ' 123456 '   where  cardID = ' 1010 3576 1234 5678 '   --  张三
  update  cardinfo  set  pass = ' 123123 '   where  cardID = ' 1010 3576 1212 1134 '   --  李四
select   *   from  cardInfo
go   
  -- 4.2 挂失账号
  update  cardinfo  set  IsReportLoss = ' 是 '   where  cardID = ' 1010 3576 1212 1134 '   --  李四
select   *   from  cardInfo  where  cardID = ' 1010 3576 1212 1134 '
go
  -- 4.3 资金流通余额和盈利结算
  declare   @inMoney   money ,   -- 总存入
         @outMoney   money    -- 总支出
  select   @inmoney = sum (transmoney)  from  transinfo  where  transtype = ' 存入 '
  select   @outmoney = sum (transmoney)  from  transinfo  where  transtype = ' 支取 '
  print   ' 银行流通余额总计为： ' + convert ( varchar ( 20 ), @inmoney - @outmoney ) + ' RMB '
  print   ' 盈利结算为： ' + convert ( varchar ( 20 ), @outmoney * 0.008 - @inmoney * 0.003 ) + ' RMB '
go
  -- 4.4 查询本周开户的Card
  select   *   from  cardInfo  where   DATEDIFF  (ww,OpenDate, getDate ()) = 0
  -- 4.4 查询本月交易最高的卡号
   select   top   1  cardId, sum (transmoney)  as  交易总金额  from  transinfo
   where   datediff (mm, getDate (),transdate) = 0
   group   by  cardId 
   order   by   sum (transmoney)  desc

  -- 4.5 挂失
    select  customername  as  客户姓名,telephone  as  联系电话  from  userinfo u  inner   join  cardinfo c  on  u.customerid = c.customerid  where  isreportloss = ' 是 '  

  -- 4.6  提醒业务
   select  customername  as  客户姓名,telephone  as  联系电话,balance  as  卡上余额
   from  userinfo u  inner   join  cardinfo c  on  u.customerid = c.customerid
   where  balance <= 200

-- 5 创建索引和视图
-- 5.1 创建索引

   CREATE   INDEX  index_card
      ON  transinfo (cardid)
    WITH   FILLFACTOR   =   70
go

   select   *   from  transinfo  where  cardid = ' 1010 3576 1212 1134 '  
go
-- 5.2创建视图
   -- 5.21 v_userinfo
   create   view  v_userinfo
   as
   select  customerID  as  客户编号,customername  as  开户名,pid  as  身份证号,telephone  as  电话号码,address  as  居住地址  from  userinfo
go
   select   *   from  v_userinfo
go
  -- 5.22 v_cardinfo
   create   view  v_cardinfo
    as
   select  cardid  as  卡号,curtype  as  货币种类,savingtype  as  存款类型,opendate  as  开户日期,balance  as  余额,pass  as  密码,isreportloss  as  是否挂失,customerid  as  客户编号  from  cardinfo
go
   select   *   from  v_cardinfo
go
  -- 5.23  v_transInfo
  create   view  v_transInfo
     as
    select  transdate  as  交易日期,transtype  as  交易类型,cardid  as  卡号,transmoney  as  交易金额,remark  as  备注  from  transInfo
go  
    select   *   from  v_transInfo
go

-- 6 创建触发器
   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   char ( 4 ), @outMoney   money , @myCardID   varchar ( 30 ), @zhi_balance   money
   select   @myTransType = transType, @outMoney = transMoney, @myCardID = cardID  from  inserted
         if ( @myTranstype = ' 存入 ' )
          begin
                      update  cardinfo  set  balance  = balance + @outMoney   where  cardid = @mycardid
                      print   ' 交易成功,存入 ' + convert ( varchar ( 15 ), @outMoney ) + ' RMB '
                  end
         else
              begin
          select   @zhi_balance = balance - @outMoney   from  cardinfo  where  cardid = @mycardid
           if ( @zhi_balance <= 0 )
                    begin
                       raiserror ( ' 余额不足！ ' , 16 , 1 )
                       rollback   tran
                    end
               else
          begin
                      update  cardinfo  set  balance  = balance - @outMoney   where  cardid = @mycardid
                      print   ' 交易成功,支取 ' + convert ( varchar ( 15 ), @outMoney ) + ' RMB '
                  end
              end

--
go

-- 7：测试触发器
--  7.1.1 Error
   insert   into  transInfo(transDate,transType,cardID,transMoney) values ( getDate (), ' 支取 ' , ' 1010 3576 1234 5678 ' , 1000 )
go
--  7.1.2 NotError
   insert   into  transInfo(transDate,transType,cardID,transMoney) values ( ' 2005-9-1 ' , ' 存入 ' , ' 1010 3576 1212 1134 ' , 200 )
go


-- 8：创建存储过程

IF   EXISTS  ( SELECT  name  FROM  sysobjects 
          WHERE  name  =   ' proc_takeMoney '   AND  type  =   ' P ' )
    DROP   PROCEDURE  proc_takeMoney
GO

create   proc  proc_takeMoney
     @card   char ( 19 ), @m   money , @type   char ( 4 ), @inputpass   char ( 6 ) = ''
as
if   exists ( select   *   from  cardinfo  where  cardid = @card   and  pass = @inputpass )
    begin
       if ( @type = ' 支取 ' )
         begin
            insert   into  transInfo(transType,cardID,transMoney) values ( ' 支取 ' , @card , @m )
            print   ' 成功支取 ' + convert ( varchar ( 12 ), @m ) + ' RMB '
         end
        
       else
         begin
            insert   into  transInfo(transType,cardID,transMoney) values ( ' 存入 ' , @card , @m )
            print   ' 成功存入 ' + convert ( varchar ( 12 ), @m ) + ' RMB '
         end
    end
else
     begin
       -- rollback tran
       print   ' 密码错误 '
     end
go
exec  proc_takeMoney  ' 1010 3576 1234 5678 ' , 300 , ' 支取 ' , ' 34323 '   -- 密码错误
exec  proc_takeMoney  ' 1010 3576 1234 5678 ' , 300 , ' 支取 ' , ' 888888 '
go


-- 9：产生随机卡号存储过程
  IF   EXISTS  ( SELECT  name  FROM  sysobjects  WHERE  name  =   ' proc_randCardID ' ) 
    DROP   PROCEDURE  proc_randCardID
   GO

   create   proc  proc_randCardID 
      @randCardID   char ( 19 ) output
    as
     declare   @r   char ( 15 ),
             @index   int ,
             @tempStr   char ( 9 )


      -- 产生随机数
    select   @r = convert (numeric( 15 , 8 ), datepart (ms, getdate ()) * rand ()) 
    select   @index = charindex ( ' . ' , @r )
    select   @tempStr = substring ( @r , @index + 1 , 4 ) + '   ' + substring ( @r , @index + 5 , 4 )   -- 获得后面8位随机数
  
     -- 随机卡号
    set   @randCardID   = ' 1010 3576 ' + '   ' + @tempStr

go
-- ----

--  测试随机号
declare   @randcardID   char ( 19 )
exec  proc_randCardID  @randcardID  output
print   ' 产生的卡号为： ' + @randcardID


-- 10 ;开户开户存储过程
  IF   EXISTS  ( SELECT  name  FROM  sysobjects  WHERE  name  =   ' proc_openAccount ' )
      DROP   PROCEDURE  proc_openAccount
GO

   create   proc  proc_openAccount
     @customername   varchar ( 20 ), @pid   char ( 20 ), @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

   insert   into  userinfo(customerName,PID,telephone,address) values ( @customername , @pid , @telephone , @address ) -- 写入数据userinfo

   select   @cur_customerID = customerID  from  userinfo  where  pid = @pid   -- 得到当前的ID号

   insert   into  cardinfo(cardid,savingtype,openmoney,balance,customerid)   values  ( @mycardid , @savingtype , @openMoney , @openMoney , @cur_customerID ) -- 写入cardinfo
    print   ' 尊敬的客户，开户成功！您的卡号为： ' + @mycardid + ' 开户金额: ' + convert ( varchar ( 12 ), @openMoney ) + ' 开户日期： ' + convert ( varchar ( 10 ), getdate ())
go

-- 测验10
exec  proc_openAccount  ' 王五 ' , ' 334456889012678 ' , ' 2222-63598978 ' , 1000 , ' 活期 ' , ' 河南新乡 '
exec  proc_openAccount  ' 赵二 ' , ' 213345678912342222 ' , ' 0760-44446666 ' , 1 , ' 定期 '

-- 11创建事务
go
  create   proc  proc_transfer  
     @card1   char ( 19 ), 
     @card2   char ( 19 ),
     @outmoney   money
  as
  begin   tran
   declare    @ErrorSum   int
   set   @ErrorSum = 0
  insert   into  transinfo(transtype,cardid,transmoney)  values ( ' 支取 ' , @card1 , @outmoney )
   set   @ErrorSum =   @ErrorSum + @@ERROR
  insert   into  transinfo(transtype,cardid,transmoney)  values ( ' 存入 ' , @card2 , @outmoney )
   set   @ErrorSum =   @ErrorSum + @@ERROR
  if (  @ErrorSum <> 0 )
    rollback   tran
  else
    commit   tran
-- 测试
go
declare   @card_1   char ( 19 ), @card_2   char ( 19 )
   select   @card_1 = cardid  from  cardinfo c  inner   join  userinfo u  on
  c.customerid = u.customerid  where  customername = ' 李四 '

    select   @card_2 = cardid  from  cardinfo c  inner   join  userinfo u  on
   c.customerid = u.customerid  where  customername = ' 张三 '
    exec  proc_transfer  @card_1 , @card_2 , 200
   select   *   from  v_cardinfo
   select   *   from  v_transinfo
go


-- 12 创建账号和数据用户服务
exec  sp_addlogin  ' sysAdmin ' , ' 1234 ' -- SQL 登陆帐号

exec  sp_grantdbaccess  ' sysAdmin ' , ' sysAdminDBUser ' -- 创建数据库用户

grant   select , insert , update , delete   on  transinfo  to  sysAdminDBUser  -- 给权力

/**/ /* 作者:花满天
   QQ：122273014
   如有更好的编写方法,请和我联系
*/