atm.c

可以实现当前自动取款机的各功能

print '本月交易金额最高的卡号'
select distinct cardID from transInfo 
where transMoney = (select max(transMoney) from transInfo
where datediff(Month,transDate,getdate()) = 0)
and datediff(Month,transDate,getdate()) = 0

/*select cardID ,max(select sum(transMoney) from transInfo 
where datediff(Month,transDate,getdate()) = 0 group by cardID)
 from transInfo*/

------------------------6.6查询挂失帐号的客户信息 ----------------
--1用子查询方法
print '查询挂失帐号的客户信息 '
select 客户姓名=customerName ,联系电话=PID from  userInfo
where customerID in(select customerID from cardInfo where IsReportLoss=1)

--2内部连接方法
print '查询挂失帐号的客户信息 '
select 客户姓名=customerName ,联系电话=PID from  userInfo
inner join cardInfo on cardInfo.customerID =userInfo.customerID where IsReportLoss=1

-----------------------6.7催款提醒业务-----------------------------
print '每个月帐上余额少于200元的 客户 致电催款'
select 客户姓名=customerName ,联系电话=PID ,帐户余额=balance
from  userInfo inner join cardInfo on cardInfo.customerID =userInfo.customerID
where balance<200 and datediff(Month,openDate,getdate())=0

/*********************7创建索引和视图*****************************/

----------------------7.1.1创建 交易表cardID字段索引,加快查询速度------
if exists (select * from sysindexes where name='ix_transInfo_cardid')--判断索引是否存在
drop index transInfo.ix_transInfo_cardid--如果存在则删除
--drop index  ix_transInfo_cardid--错误
go
create NONCLUSTERED index  ix_transInfo_cardid--交易表
on transInfo(cardid)--按cardID字段索引
with fillfactor =70--索引因子
go
----------------------7.1.2测试 按索引查询---------------------------------
print '按索引查询'
select * from transInfo   (index=ix_transInfo_cardid)
where cardID=(select cardID from cardInfo where 
customerID =(select customerID from userInfo where customername='张三'))

select * from transInfo   (index=ix_transInfo_cardid)
where cardID='1010 3576 1212 1134'

----------------------7.2创建视图，向用户展示友好界面,创建中文字段视图------------
--7.2.1 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
select * from view_userInfo--查看视图

--7.2.2 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,余额=balance ,密码=pass,
是否挂失=IsReportLoss,客户编码=customerID
from cardInfo
go

select * from view_cardInfo--查看视图

--7.2.3 transInfo表的视图
if exists (select * from sysobjects where name='view_transInfo')
drop view view_transInfo
go
create view view_transInfo
as 
select 交易日期=transDate,交易类型=transType,
卡号=cardID,交易金额=transMoney,备注=remark
from transInfo 
go
select * from view_transInfo--查看视图

/**************************8创建触发器******************************/
if exists (select * from sysobjects where name='trig_transInfo_insert')
drop trigger trig_transInfo_insert
go
create trigger trig_transInfo_insert
on transInfo
for insert
as
--定义变量：@cardID 交易卡号,@transMoney  交易金额,@transType 交易类型,　@balance 交易前余额 @newbalance交易后余额
declare @cardID  char(19),@transMoney money,@transType char(8),@balance money,@newbalance money
--获取要交易的　交易卡号　交易金额　交易类型
select @cardID =cardID ,@transMoney=transMoney,@transType=transType from inserted
--获取交易前的余额
select @balance=balance from cardInfo where cardID=@cardID
 
 if  not exists (select * from cardInfo where cardID=@cardID)--???没起作用
 begin
 raiserror ('没有该用户',16,1)
 rollback tran
 return
 end 

--嵌套if 首先判断交易类型,如果是支取再判断余额是否足够交易
if (@transType='存入')
 begin
  print '交易正在进行中,请稍候....'
  update cardInfo set balance=@balance+@transMoney where cardID=@cardID
  print '存储成功,存储金额为'+convert(varchar(20),@transMoney)
  --方法1
  SELECT @newbalance=balance from cardInfo where cardID=@cardID
  PRINT '卡号'+convert(varchar(19),@cardID)+'目前余额'+convert (varchar(8),@newbalance)
  --方法2
  --print '卡号'+convert(varchar(19),@cardID)+'目前余额'+convert (varchar(8),@balance+@transMoney)--对的
  
 end
else if (@transType='支取')
 begin  
  if(@balance-@transMoney<1)
   begin 
    raiserror('支取失败，余额不足',16,1)
    print '卡号'+convert(varchar(19),@cardID)+'目前余额'+convert (varchar(8),@balance)
    rollback  tran--回滚事务，取消交易
    
   end 
  else 
   begin 
    print '交易正在进行中,请稍候....'
    update cardInfo set balance=@balance-@transMoney where cardID=@cardID
    print '支取成功,支取金额为:'+convert(varchar(20),@transMoney)
    --方法1
    SELECT @newbalance=balance from cardInfo where cardID=@cardID
    PRINT '卡号'+convert(varchar(19),@cardID)+'目前余额'+convert (varchar(8),@newbalance)
    --方法2
    --print '卡号'+convert(varchar(19),@cardID)+'目前余额'+convert (varchar(8),@balance-@transMoney)--对的
   end  
 end
go

--8.2.1测试触发器：存入
declare @cardID2 char (19)
select @cardID2=cardID from cardInfo 
where customerID=(select customerID from userInfo where customername='李四')
if  not exists (select * from cardInfo where cardID=@cardID2)
 begin
  raiserror ('没有该用户',16,1)
  return
 end 
insert into transInfo(cardID,transType, transMoney)
values (@cardID2,'存入',100)

--8.2.2测试触发器：支取
declare @card char(19)
select @card=cardID from cardInfo Inner join userinfo on 
cardInfo.customerID  =userInfo.customerID   where customername='张三'
if  not exists (select * from cardInfo where cardID=@card)
 begin
  raiserror ('没有该用户',16,1)
  return
 end 
insert into transInfo(cardID,transType, transMoney  )
values (@card,'支取',200) 

/*************************10创建存储过程*****************************/
--------------------------10.1取钱或存钱的存储过程-------------------
if exists (select * from sysobjects where name='proc_takeMoney')
drop procedure proc_takeMoney
go
create procedure proc_takeMoney
 @IDcard char(19),--交易卡号
 @m money,--交易金额
 @type char(8),--交易类型
 @inputpass char(6)=' '--取款密码
as

if (@inputpass<>(select pass from  cardInfo where cardID=@IDcard))--判断密码是否相符
 begin 
  raiserror ('密码错误,请核实',16,1)
  
  return 
 end 
if  not exists (select * from cardInfo where cardID=@IDcard)--判断用户信息表中是否存在取钱或存钱的用户
 begin
  raiserror ('没有该用户',16,1)
  return
 end 

insert transInfo (cardID,transType,transMoney) values (@IDcard,@type,@m)
insert into transInfo values(default,@IDcard,@type,@m,default)

go

--测试：调用取钱或存钱的存储过程
declare @card char(19)
select @card=cardID from cardInfo
where customerID =(select customerID from userInfo where customername='张三')
exec proc_takemoney @card,300,'支取','123456'--调用过程，执行取钱
go

declare @card2 char(19)
select @card2=cardID from cardInfo
where customerID =(select customerID from userInfo where customername='李四')
exec proc_takemoney @card2,300,'存入','123123'--调用过程，执行存钱
go

--------------------10.2产生随机卡号的存储过程proc_randCardID----------------
--说明*银行卡号共１９位（４位移组，中间用空格隔开），
--对于某个银行，前８个数字是固定的,前８位固定数字设置为1010 3576
--后面８个数字是固定的，后面的８个数字要求随机的，并且唯一的，
--随机种子＝当前月份数*10000＋当前的秒数*1000+当前的毫秒数
--产生了０～１的随机数后，取小数点后８位，即: 0.xxxxxxxx
if exists (select * from sysobjects where name='proc_randCardID')
drop procedure proc_randCardID
go

create procedure proc_randCardID
@randCardID char(19)  output,
@id char(10)='1010 3576' --前8位
as 
declare @r numeric(15,8)--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)--随机数　转换类型
set @randCardID=@id+''+substring(@tempStr,3,4)+' '+substring(@tempStr,7,4) 
go
--测试：调用随机卡号的存储过程
declare @mycardID char(19)
execute proc_randCardID @mycardID output--执行存储过程
print '产生的随机卡号为:'+@mycardID
go

------------------10.3开户的存储过程proc_openAccount--------------------
if exists (select * from sysobjects where name='proc_openAccount')
drop procedure proc_openAccount--如果开户存储过程则删除
go

create procedure proc_openAccount--创建开户存储过程
--输入参数
@customerName char(8),--客户姓名
@PID char(18),--身份证
@telephone char(13),--电话号码
@openMoney money,--开户金额
@savingType char(8),--开户类型
@address varchar(50)=''--地址

as
declare @mycardID_2  char(19)--卡号
,@openDate datetime--开户时间
execute proc_randCardID @mycardID_2 output--调用随机卡号存储过程产生卡号
while exists (select * from cardINfo where cardID=@mycardID_2)--如果存在则重新产生新号
execute proc_randCardID @mycardID_2 output--产生随机卡号
--向用户表　userInfo　插入开户　客户姓名，身份证，电话号码，地址
insert into userInfo  values (@customerName,@PID,@telephone,@address)

declare @cur_customerId int--顾客编号
select @cur_customerId=customerID from userInfo where PID=@PID --获取新开户顾客编号
--向银行卡信息表 cardInfo 插入数据 卡号，存款类型，余额，顾客编号
insert into cardInfo(cardID,savingType,openMoney,balance,customerID)
values (@mycardID_2,@savingType,@openMoney,@openMoney,@cur_customerId)


--方法１：子查询　得到　开户用户的开户时间
select distinct @openDate=openDate from cardinfo 
where  customerID = (select customerID from userinfo where customername=@customerName and customerID=@@identity)
--方法２:用内连接查询　得到　开户用户的开户时间
select distinct @openDate=openDate from cardinfo 
inner join userinfo on userinfo.customerID =cardinfo.customerID  where  customername=@customerName
--方法３：用变量 @cur_customerId 得到 开户用户的开户时间
select distinct @openDate=openDate from cardinfo 
where  customerID = @cur_customerId
--开户成功后-显示 信息
print ''
print '尊敬的客户 ：'+@customerName
print '恭喜您开户成功!'
print '系统为您产生的随机卡号为'+convert(varchar(19),@mycardID_2)
print '开户日期'+convert(varchar(10),@openDate,111)+
'  开户金额:'+convert(varchar(10),@openMoney)+'元'

go

--测试调用开户的存储过程proc_openAccount
execute proc_openAccount '王','334456889012678','0000-63598978',100,'活期','河南新乡'

execute proc_openAccount @customername='赵',@PID ='213445789123422222',
@telephone='0760-44446666',@openMoney=1,@savingType='定期'

/**********************11创建事务 利用事务,模拟银行转账功能*************/
if exists (select * from sysobjects where name='proc_transfer')--判断存储过程是否存在
drop procedure proc_transfer--如果存在则删除
go
create procedure proc_transfer--创建存储过程
--输入参数
@card1 char(19),--支取卡号
@card2 char(19),--存入卡号
@transMoney money--交易金额
as
 begin tran --开始事务

if  not exists (select * from cardInfo where cardID=@card1) or
  not exists (select * from cardInfo where cardID=@card2)--判断用户是否存在
 begin
 raiserror ('没有该用户，请核实',16,1)
 rollback tran--回滚事务
 return
 end 
declare @errors int
     set @errors=0
declare @balance money
select @balance=balance from cardInfo where cardID=@card1--从银行信息表获取支取客户的余额
insert into transInfo (transType,cardID,transMoney )values ('支取',@card1,@transMoney)--存在insert触发器
set @errors=@errors+@@error
insert into transInfo (transType,cardID,transMoney )values ('存入',@card2,@transMoney)
set @errors=@errors+@@error


if (@errors>0 or @balance-@transMoney<1)--判断支取用户余额是否大于 将要 转出的金额
 begin
  print'转账失败，回滚事务'--此句没有作用了
  rollback tran--回滚事务
 end
else 
 begin
  commit tran--提交事务
  print '转账成功' 
 end
go
 
--测试转账事务存储过程 模拟从李四帐上转2000元到张三帐上
print '开始转账，请稍候...'
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 --从用户信息表 查询张三的卡号
where customerID =(select customerID from userInfo where customername='张三')

exec proc_transfer @card1,@card2,2000--调用执行转账事务存储过程

select * from view_cardInfo--查看银行信息视图
select * from view_transinfo--查看交易信息视图

/*******************12创建登陆账号和数据用户*************************/
--1添加sql登录账号
if not exists(select * from master.dbo.syslogins where loginname='fifille')
    begin
      exec sp_addlogin 'fifille','841023'    --添加SQL登录帐号       
    end
  go

--2创建数据库用户
exec sp_revokedbaccess  'fifilledbuser'--删除
exec sp_grantdbaccess 'fifille','fifilledbuser'--添加
--3增加权限  为 fifilledbuser (增删改查的权限)
grant select,insert,delete on transinfo to fifilledbuser
 
select * from userInfo--1用户信息表userInfo
select * from cardInfo--2银行卡信息表：cardInfo 
select * from transInfo--3交易信息表 transInfo

 

--临时表
SELECT  *   INTO  #用户信息表临时表  FROM  userInfo

GO

SELECT  *  FROM  #用户信息表临时表
select * from systypes