/****************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
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
