ATM机小案例(库、表、约束、视图、存储过程和事务)

--创建数据库
use master
go
if exists (select * from sysdatabases where name = 'bankDB')
drop database bankDB
go
create database bankDB
on
(
name = 'bankDB_data',
filename = 'E:/SQL Server/项目/bankDB_data.mdf',
size = 3mb,
filegrowth = 15%
)
log on
(
name = 'bankDB_log',
filename = 'E:/SQL Server/项目/bankDB_log.ldf',
size = 2mb,
maxsize = 10mb,
filegrowth = 1mb
)
go

--创建表——userInfo
use bankDB
go
if exists (select * from sysobjects where name = 'userInfo')
drop table userInfo
go
create table userInfo
(
customerID int primary key identity(1,1) not null,    --顾客ID
customerName varchar(20) not null,    --顾客姓名
PID varchar(18) not null,    --身份证号
telephone varchar(13) not null,   --联系电话
address varchar(50) null   --地址
)
select * from userInfo
go

--创建表——cardInfo
use bankDB
go
if exists (select * from sysobjects where name = 'cardInfo')
drop table cardInfo
go
create table cardInfo
(
cardID varchar(19) primary key not null,    --卡号
pass varchar(6) not null,    --密码
curType varchar(10) not null,    --货币种类
savingType varchar(10) not null,    --存款类型
openDate datetime not null,   --开户日期
openMoney money not null,    --开户金额
balance money not null,   --余额
customerID int not null,   --顾客ID
    IsReportLoss bit not null     --是否挂失
)
select * from cardInfo
go

--创建表——transInfo
use bankDB
go
if exists (select * from sysobjects where name = 'transInfo')
drop table transInfo
go
create table transInfo
(
transDate datetime not null,    --交易日期
cardID varchar(19) not null,   --卡号
transType varchar(10) not null,   --交易类型，存入或支取
transMoney money not null,    --交易金额
remark varchar(50) null    --备注
)
select * from transInfo
go

--创建约束——userInfo
alter table userInfo add constraint CK_PID check (len(PID )=15 or len(PID )=18),   --18位或15位
       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 telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), --格式“xxxx-xxxxxxxx”或手机号11位
       constraint UQ_PID unique (PID),    --身份证号唯一
       constraint DF_addresss default('地址不详') for address    --地址默认为‘地址不详’
go

--创建约束——cardInfo
alter table cardInfo add 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 DF_pass default('888888') for pass,
       constraint CK_pass check (len(pass)=6 and pass like '[0-9][0-9][0-9][0-9][0-9][0-9]'),
       constraint FK_customerID foreign key (customerID) references userInfo(customerID)
go

--创建约束——transInfo
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

--插入测试数据——userInfo
insert into userInfo (customerName, PID, telephone, address)
values ('张三','123456789012345','010-67898978','北京海淀')
insert into userInfo (customerName, PID, telephone)
values ('李四','321245678912345678','0478-44443333')
select * from userInfo
go

--插入测试数据——cardInfo
insert into cardInfo (cardID, pass, curType, savingType, openDate, openMoney, balance, customerID, IsReportLoss)
values ('1010 3576 1234 5678','888888','RMB','活期',getDate(),1000,1000,1,0)
insert into cardInfo (cardID, pass, curType, savingType, openDate, openMoney, balance, customerID, IsReportLoss)
values ('1010 3576 1212 1134','888888','RMB','定期',getDate(),1,1,2,0)
select * from cardInfo
go

--插入交易信息
set nocount on
insert into transInfo (transDate, cardID, transType, transMoney)
values (getDate(),'1010 3576 1234 5678','支取',900)
insert into transInfo (transDate, cardID, transType, transMoney)
values (getDate(),'1010 3576 1212 1134','存入',5000)
update cardInfo set balance = balance - 900 where cardId = '1010 3576 1234 5678'
update cardInfo set balance = balance + 5000 where cardId = '1010 3576 1212 1134'
select * from transInfo
go

--修改密码
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

--银行卡挂失
update cardInfo set IsReportLoss=1 where cardID='1010 3576 1212 1134'
select * from cardInfo
go

--统计银行的资金流通余额和盈利结算
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

--查询本周开户的卡号并显示相应信息
select * from cardInfo where datediff(dd,openDate,getDate())<datepart(dw,getDate())

--查询本月金额交易最高的卡号
select cardID from transInfo where transMoney=(select max(transMoney) from transInfo)
and datepart(mm,getDate())=datepart(mm,transDate) and datediff(dd,transdate,getDate())<=31
go

--查询挂失账号的客户信息
select customerName as 客户姓名,PID as 身份证号,telephone as 联系电话,address as 地址 from userInfo
where customerID in (select customerID from cardInfo where IsReportLoss=1)
go

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

--为transInfo表的卡号cardID创建重复索引并查询张三的交易记录
use bankDB
go
if exists (select name from sysindexes where name='index_transInfo_cardID')
drop index transInfo.index_transInfo_cardID
create nonclustered index index_transInfo_cardID
on transInfo(cardID) with fillfactor = 70
go
select * from transInfo with (index=index_transInfo_cardID) where cardID = '1010 3576 1234 5678'

--创建视图(用户表，银行卡表，交易表)
--用户表
use bankDB
go
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
go
--银行卡表
use bankDB
go
if exists (select * from sysobjects where name='view_cardInfo')
drop view view_cardInfo
go
create view view_cardInfo
as
   select 卡号=cardID,货币种类=curType,存款类型=savingType,开户日期=openDate,开户金额=openMoney,
   余额=balance,密码=pass,是否挂失=IsReportLoss,客户编号=customerID from cardInfo
go
select * from view_cardInfo
go
--交易表
use bankDB
go
if exists (select * from sysobjects where name='view_transInfo')
drop view view_transInfo
go
create view view_transInfo
as
   select 交易日期=transDate,卡号=cardID,交易类型=transType,交易金额=transMoney,备注=remark from transInfo
go
select * from view_transInfo
go

--创建取钱或存钱的存储过程
use bankDB
go
if exists (select * from sysobjects where name = 'proc_takeMoney')
drop procedure proc_takeMoney
go
create procedure proc_takeMoney
@cardID varchar(19),     --卡号
@money money,    --交易金额
@type varchar(4),    --交易类型
@inputPass varchar(6)=''    --密码（默认为空）
as
   declare @balance money     --余额
   if (@type='存入')
    begin
     print '交易正进行，请稍候……'
     select @balance=balance from cardInfo where @cardID=cardID
     if (@money<=0)
      begin
       raiserror('交易失败！存款数必须大于为0！',16,1)
       print '卡号'+@cardID+' 余额：'+convert(varchar(10),@balance)
       return
      end
     else
      begin
       insert into transInfo (transDate, cardID, transType, transMoney)
       values (getDate(),@cardID,@type,@money)    --插入交易信息
       update cardInfo set balance = balance + @money where cardID=@cardID    --更新卡中余额
       select @balance=balance from cardInfo where @cardID=cardID
       print '交易成功！交易金额：'+convert(varchar(10),@money)
       print '卡号'+@cardID+' 余额：'+convert(varchar(10),@balance)
      end
    end
   else
    begin
     if(@inputPass=(select pass from cardInfo where cardID=@cardID))--判断密码是否正确
      begin
       print '交易正进行，请稍候……'
       select @balance=balance from cardInfo where @cardID=cardID
       if (@balance<@money)     --余额小于取款数
        begin
         raiserror('交易失败！余额不足！',16,1)
         print '卡号'+@cardID+' 余额：'+convert(varchar(10),@balance)
         return
        end
       else if (@balance=@money)     --余额等于取款数
        begin
         raiserror('交易失败！余额能为0！',16,1)
         return
        end
       else     --余额大于取款数
        begin
         insert into transInfo (transDate, cardID, transType, transMoney)
         values (getDate(),@cardID,@type,@money)    --插入交易信息
         update cardInfo set balance=balance-@money where cardID=@cardID    --更新卡中余额
         select @balance=balance from cardInfo where @cardID=cardID
         print '交易成功！交易金额：'+convert(varchar(10),@money)
         print '卡号'+@cardID+' 余额：'+convert(varchar(10),@balance)
        end
      end
     else
      print '密码错误！'
    end
go
set nocount on
execute proc_takeMoney '1010 3576 1234 5678',300,'支取','123456'
execute proc_takeMoney '1010 3576 1212 1134',500,'存入'

--创建产生随机卡号的存储过程
use bankDB
go
if exists (select * from sysobjects where name = 'proc_randCardID')
drop procedure proc_randCardID
go
create procedure proc_randCardID
@randCardID varchar(19) output     --卡号
as
   declare @r numeric(15,8)    --15位数，保留8位小数
   select @r=rand((datepart(mm,getDate())*100000)+(datepart(ss,getDate())*1000)+(datepart(ms,getDate())))
   set @randCardID='1010 3576 '+subString(convert(varchar(15),@r),3,4)+' '+subString(convert(varchar(15),@r),7,4)
go
declare @myCardID varchar(19)
execute proc_randCardID @myCardID output
print '产生的随机卡号为：'+@myCardID

--创建开户的存储过程
use bankDB
go
if exists (select * from sysobjects where name = 'proc_openAccount')
drop procedure proc_openAccount
go
create procedure proc_openAccount
@customerName varchar(8),     --用户名
@PID varchar(18),      --身份证号
@telephone varchar(13),    --联系电话
@openMoney money,     --开户金额
@savingType varchar(8),    --存款类型
@address varchar(50) = '地址不详'    --地址
as
   declare @myCardID varchar(19),@customerID int,@openDate datetime,@sumError int
   set @sumError = 0
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)
set @sumError=@sumError+@@error   --累计是否有错
select @customerID=customerID from userInfo where PID=@PID
set @sumError=@sumError+@@error   --累计是否有错
insert into cardInfo(cardID,savingType,openMoney,balance,customerID,IsReportLoss) values (@mycardID,@savingType,@openMoney,@openMoney,@customerID,0)
set @sumError=@sumError+@@error   --累计是否有错
select @openDate=openDate from cardInfo where customerID=@customerID
set @sumError=@sumError+@@error   --累计是否有错
if (@sumError<>0)
begin
   raiserror('开户失败',16,1)
   return
end
else
print '尊敬的客户，开户成功！系统为您产生的随机卡号为：'+@myCardID+'开户日期:'+convert(varchar(8),@openDate,111)+'开户金额：'+convert(varchar(10),@openMoney)
go
execute proc_openAccount '王五','334456889012678','2222-63598978',1000,'活期','河南新乡'
execute proc_openAccount '赵二','213445678912342222','0760-44446666',1,'定期'

--创建事务
set nocount on
if exists (select * from sysobjects where name='proc_transfer')
drop procedure proc_transfer
go
create procedure proc_transfer
@card1 char(19),
@card2 char(19),
@outmoney money
as
   begin transaction
    execute proc_takeMoney @card1,@outmoney,'支取','123123'
    if((select balance from cardInfo where cardId=@card1)-@outmoney<0)
     rollback transaction
    else
    begin
     execute proc_takeMoney @card2,@outmoney,'存入'
     commit transaction
    end
go
execute proc_transfer '1010 3576 1212 1134','1010 3576 1234 5678',100
execute sp_addlogin 'sysAdmin','1234'
execute sp_grantdbaccess 'sysAdmin','sysAdminDBUser'
grant select,insert,update,delete,select on transInfo to sysAdminDBUser
go