一个ATM系统数据库设计 很全

/****************2创建数据库*******************/
use master
go

if exists (select * from sysdatabases where name='bankDB')--检查系统中是否已存在数据库atm
drop database bankDB --如果存在则删除

go
exec xp_cmdshell 'mkdir D:\bank' ,no_output--调用DOS命令创建文件夹
create database bankDB --新建数据库
on primary  --主文件
(
name = 'atm_data', --逻辑名称
filename='D:\bank\bankDB_data.mdf', --物理名称
size=10 , --初始大小
filegrowth=15%  --增长率
)
log on --日志文件
(
name='atm_log', --逻辑名称
filename='D:\bank\bankDB_log.ldf',--物理名称
size=1 , --初始大小
filegrowth=10% --增长率
)
go

/**********************3建表***********************/
use bankDB
go
-------------------1用户信息表userInfo------------------
if exists (select * from sysobjects where name='userInfo')
drop table userInfo
go
create table userInfo
(
customerID  int identity(1,1) not null,--顾客编号，自动编号（标识列),从1开始,主键
customerName varchar (8) not null ,--开户名
PID varchar(18) not null,--身份证号,必填，只能是18位或15位，身份证号唯一约束
telephone varchar(18) not null,--联系电话,必填，格式为xxxx-xxxxxxxx或手机号13位
address varchar(20)--居住地址,可选输入
)

--------------------2银行卡信息表cardInfo------------------
if exists (select * from sysobjects where name='cardInfo')
drop table  cardInfo
go

create table cardInfo
(
cardID char (19) not null,--卡号，必填，主健,银行的卡号规则和电话号码一样，一般前8位代表特殊含义 假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始,每4位号码后有空格，卡号一般是随机产生.
curType  char(4) not null, --货币种类
savingType char(8), --存款类型 活期/定活两便/定期
openDate datetime not null,--开户日期
openMoney money not null, --开户金额
balance money not null, --余额 
pass int  not null ,--密码 
IsReportLoss bit  not null, --是否挂失 
customerID int not null --顾客编号
)
go
------------------3交易信息表transInfo -------------------
if exists (select * from sysobjects where name='transInfo')
drop table  transInfo
go

create table transInfo
(
transDate  datetime not null,--交易日期，必填，默认为系统当前日期
cardID char (19) not null,--卡号，必填，外健，可重复索引
transType  char(8) not null,--交易类型，必填，只能是存入/支取
transMoney money not null,--交易金额，必填，大于0
remark text--备注，可选输入，其他说明
)
go

/************************4添加约束*****************************/

-----------------------1用户信息表userInfo 添加约束--------------

--主键约束customerID 顾客编号 自动编号（标识列）
if exists (select * from sysobjects where name='pr_customerID')
alter table userInfo
drop constraint pr_customerID
alter table userInfo
add constraint pr_customerID primary key (customerID)

--检查约束customerID 身份证号 只能是18位或15位
if exists (select * from sysobjects where name='ck_PID')
alter table userInfo
drop constraint ck_PID
alter table userInfo
add constraint ck_PID check (
PID 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][0-9][0-9][0-9]'
or PID 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][0-9][0-9][0-9][0-9][0-9][0-9,x]'
)

--唯一约束customerID身份证号
if exists (select * from sysobjects where name='uq_PID')
alter table userInfo
drop constraint uq_PID
alter table userInfo
add constraint uq_PID  unique(customerID)

--检查约束telephone电话号码格式为xxxx-xxxxxxxx或手机号13位
if exists (select * from sysobjects where name='ck_telephone')
alter table userInfo
drop constraint ck_telephone
alter table userInfo
add constraint ck_telephone check
(telephone like '1[3,5][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][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][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )
--(len(telephone)-13 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][0-9])

 

-----------------2银行卡信息表cardInfo 添加约束------------------
--主键约束 cardID 卡号
if exists (select * from sysobjects where name='pr_cardID')
alter table cardInfo
drop constraint pr_cardID
alter table cardInfo
add constraint pr_cardID primary key (cardID)

--检查约束 cardID 卡号格式为：1010 3576 xxxx xxx开始
if exists (select * from sysobjects where name='ck_cardID')
alter table cardInfo
drop constraint ck_cardID
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]')

--检查约束  存款类型 活期/定活两便/定期
if exists (select * from sysobjects where name='ck_savingType')
alter table cardInfo
drop constraint ck_savingType
alter table cardInfo
add constraint ck_savingType
check (savingType in('活期','定活两便','定期'))

--默认约束 curType货币种类 默认RMB
if exists (select * from sysobjects where name='df_curType')
alter table cardInfo
drop constraint df_curType
alter table cardInfo
add constraint df_curType
default ('RMB') for curType

--默认约束 openDate开户日期 默认为系统当前日期
if exists (select * from sysobjects where name='df_openDate')
alter table cardInfo
drop constraint df_openDate
alter table cardInfo
add constraint df_openDate
default (getdate()) for openDate

--检查约束 openMoney开户金额 不低于1元
if exists (select * from sysobjects where name='ck_openMoney')
alter table cardInfo
drop constraint ck_openMoney
alter table cardInfo
add constraint ck_openMoney
check (openMoney>=1)

--检查约束 balance余额 不低于1元,否则将销户
if exists (select * from sysobjects where name='ck_balance')
alter table cardInfo
drop constraint ck_balance
alter table cardInfo
add constraint ck_balance
check (balance>=1)

--检查约束 pass密码为6位
if exists (select * from sysobjects where name='ck_pass')
alter table cardInfo
drop constraint ck_pass
alter table cardInfo
add constraint ck_pass
check (len(pass)=6)

--默认约束 pass密码默认888888
if exists (select * from sysobjects where name='df_pass')
alter table cardInfo
drop constraint df_pass
alter table cardInfo
add constraint df_pass
default (888888) for pass

--默认约束 IsReportLoss 是否挂失  是/否值，默认为”否”
if exists (select * from sysobjects where name='df_IsReportLoss')
alter table cardInfo
drop constraint df_IsReportLoss
alter table cardInfo
add constraint df_IsReportLoss
default (0) for IsReportLoss

--检查约束  IsReportLoss 是否挂失  只能为是/否值
if exists (select * from sysobjects where name='ck_IsReportLoss')
alter table cardInfo
drop constraint ck_IsReportLoss
alter table cardInfo
add constraint ck_IsReportLoss
check (IsReportLoss in (1,0))

--外键约束 customerID 表示该卡对应的顾客编号,一位顾客允许办理多张卡号
if exists (select * from sysobjects where name='fr_customerID')
alter table cardInfo
drop constraint fr_customerID
alter table cardInfo
add constraint fr_customerID
foreign key (customerID) references userInfo(customerID)

 

------------------3交易信息表transInfo 添加约束---------------

--默认约束 transDate交易日期，默认为系统当前日期
if exists (select * from sysobjects where name='df_transDate')
alter table transInfo
drop constraint df_transDate
alter table transInfo
add constraint df_transDate
default (getdate()) for transDate

--外键约束 cardID 卡号
if exists (select * from sysobjects where name='pr_cardID2')
alter table transInfo
drop constraint pr_cardID2
alter table transInfo
add constraint pr_cardID2
foreign key (cardID) references  cardInfo(cardID)

--检查约束 transType 交易类型 只能是存入/支取
if exists (select * from sysobjects where name='ck_transType')
alter table transInfo
drop constraint ck_transType
alter table transInfo
add constraint ck_transType
check (transType in('存入','支取'))

--检查约束 transMoney 交易金额 大于0
if exists (select * from sysobjects where name='ck_transMoney')
alter table transInfo
drop constraint ck_transMoney
alter table transInfo
add constraint ck_transMoney
check (transMoney>0)

/*********************5插入数据*******************************/
---------------1测试 插入 userInfo 用户信息表 和 cardInfo银行卡信息表 数据------
insert userInfo (customerName, PID ,telephone, address)
values  ( '张三','123456789012345','010-67898978','北京海淀' )
insert cardInfo (cardID ,savingType ,openMoney,balance,customerID)
values ('1010 3576 1234 5678','活期',1000,1000,@@identity)

insert userInfo (customerName, PID ,telephone)
values('李四','321245678912345678','0478-44443333')
insert cardInfo (cardID ,savingType ,openMoney,balance,customerID)
values ('1010 3576 1212 1134','定期',1 ,1 ,@@identity)

-- delete  from cardInfo  delete  from userInfo

-----------------2测试 手动取款交易 插入交易信息表transInfo数据--------------
--测试1 手动添加交易表信息,更新卡号信息表中的数据
declare @cardid  char(19)--定义变量 @cardid 卡号

select @cardid=cardid from cardInfo --从userInfo用户表中获取客户为 张三 的卡号cardid
where customerID=(select customerID from userInfo where customername='张三')

insert into transinfo (transtype,cardid,transmoney)
values ('支取',@cardid,900)--向交易信息表transInfo插入数据：张三 支取 900元 卡号为@cardid

update cardinfo set balance =balance-900 --更改 cardInfo银行卡信息表 张三 余额balance 减去900
where customerID =(select customerID from userInfo where customername='张三')

--测试2  手动添加交易表信息,更新卡号信息表中的数据
declare @cardid2  char(19)--定义变量 @cardid 卡号

select @cardid2=cardid from cardInfo  --从userInfo用户表中获取客户为 李四 的卡号cardid
where customerID=(select customerID from userInfo where customername='李四')

insert into transinfo (transtype,cardid,transmoney)
values ('存入',@cardid2,5000)--向交易信息表transInfo插入数据：李四 存入 5000元 卡号为@cardid2

update cardinfo set balance =balance+5000 --更改 cardInfo银行卡信息表 李四 余额balance 增加5000
where customerID =(select customerID from userInfo where customername='李四')

 

/******************************6常规业务模拟***************************/

----------------------6.1修改密码--------------------------
--说明*修改张三(卡号为 1010 3576 1234 5678) 银行卡号密码为123456
update  cardInfo set pass=123456
where customerid=(select customerid from userInfo where customername='张三')

--说明*修改李四(卡号为 1010 3567 1212 1134) 银行卡号密码为123123
update  cardInfo set pass=123123
where customerid=(select customerid from userInfo where customername='李四')
 
select * from cardInfo--2银行卡信息表：cardInfo

------------------------6.2挂失帐号----------------------------
--说明*李四 (卡号为1010 3567 1212 1134) 因银行卡丢失,申请挂失
--说明*修改张三(卡号为 1010 3576 1234 5678) 银行卡号密码为123456
update  cardInfo set pass=123456
where customerid=(select customerid from userInfo where customername='张三')

--说明*修改李四(卡号为 1010 3567 1212 1134) 银行卡号密码为123123
update  cardInfo set pass=123123
where customerid=(select customerid from userInfo where customername='李四')select * from cardInfo--2银行卡信息表：cardInfo

------------------------6.3统计银行的支金流通余额和盈利结算----------
--说明*存入 代表资金流入,支取 代表资金流出

--declare @inMoney money
--select @inMoney- sum(transMoney) from transInfo where (transType-'存入')-sum(transMoney) from transinfo where transType-'支取')--错误不要了

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'
--盈利结算-总支取量*0.008-总存入量*0.003
print '盈利结算为'+left(convert(varchar(10),@outMoney*0.008-@inMoney*0.003),4)+'RMB'
 

--------------------------6.4查询本周开户的卡号-----------------------
/*declare @xtday datetime
set @xtday- datepart(dw,getdate())

declare @a datetime
select  @a-openDate from cardInfo where cardID-'1010 3576 1212 1134'
print datediff (weekday,@a,getdate())
print datediff (dd,@a,getdate())
print @xtday
print datepart(dw,getdate())-1*/

--print '本周开户的卡号信息如下:'--错误的
--select cardID from cardInfo
--where datediff(day,datepart(weekday,opendate),datepart(weekday,getdate()))<7--错了
--where datename(dw,opendate)-datename(dw,getdate()) and month(openDate)-month(getdate())and year(openDate)-year(getdate())--同一天的

--显示样式1
print '本周开户的卡号信息如下:'
select cardID from cardInfo
where datediff (dd,opendate,getdate())<-(datepart(dw,getdate())-1)
select * from cardInfo
--显示样式2
print '本周开户的卡号信息如下:'
declare @temp int
select 客户姓名=customerName,联系电话=telephone ,开户金额=openMoney,
开户日期=opendate from userInfo inner
join cardinfo on  userinfo.customerID=cardinfo.customerID
where datediff(dd,opendate,getdate())<=(datepart(dw,getdate())-1)
select * from cardinfo

select datepart(dw,getdate())-1  --求出当前周几
select datediff(dd,opendate,getdate()) from cardinfo
--函数第1个参数放天数,第2个参数填写列中字段,第3个是当前日期(求出2个日期之间的差值)

 

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

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