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
   如有更好的编写方法,请和我联系
*/