sql脚本

-------------------------------------创建数据库----------------------------------------------
use master
go
if exists(select * from sysdatabases where name='bankDB')
 drop database bankDB
go
exec xp_cmdshell 'mkdir d:/bank',no_output
go
create database bankDB
on
(
 name=bank_mdf,
 filename='d:/bank/bankDB.mdf',
 size=1,
 maxsize=10,
 filegrowth=15%
)
go

use bankDB
go

-------------------------------------创建用户信息表----------------------------------------------

if exists(select * from sysobjects where name='userInfo')
 drop table userInfo
go
create table userInfo
(
 customerID int identity(1,1) primary key,
 customerName varchar(20) not null,
 PID varchar(20) not null unique(PID) check(len(PID)=18 or len(PID)=15),
 telephone varchar(20) not null check(len(telephone)=11),
 address varchar(100)
)
go

----------------------------------------------添加约束----------------------------------------------

alter table userInfo
add constraint CK_telephone check (telephone like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

-------------------------------------创建银行卡信息表----------------------------------------------

if exists(select * from sysobjects where name='cardInfo')
 drop table cardInfo
go
create table cardInfo
(
 cardID varchar(20) primary key,
 curType varchar(20)not null default 'RMB',
 savingType varchar(20) not null check(savingType in('定期','活期','定活两便')),
 openDate datetime not null default getDate(), 
 openMoney money not null check(openMoney>=1),
 balance money not null check(balance>=1),
 pass varchar(20) not null default'888888' check(len(pass)>=6),
 IsReportLoss varchar(10) not null default'否' check(IsReportLOss in('是','否')),
 customerID int not null foreign key references userInfo(customerID)
)
go

-------------------------------------创建交易信息表----------------------------------------------

if exists(select * from sysobjects where name='transInfo')
 drop table transInfo
go
create table transInfo
(
 transDate datetime not null default getDate(),
 cardID varchar(20) not null foreign key references cardInfo(cardID),
 tranType varchar(20) not null check(tranType in('存入','支取')),
 transMoney money check(transMoney>0),
 trmark varchar(100)
)
go

-------------------------------------插入测试数据----------------------------------------------

insert into userInfo values('张三','123456789123457','13658458898','北京')
insert into userInfo values('李四','440856954878789654','13669878898','湖南')
select * from userInfo

insert into cardInfo values('1010 3576 7028 6987',default,'定期',default,1000,1000,'123456',default,1)
insert into cardInfo values('1010 3576 7028 8888',default,'定期',default,1000,1000,'123456',default,2)
select * from cardInfo

insert into transInfo values(default,'1010 3576 7028 6987','支取',200,'')
select * from transInfo
update cardInfo set balance=balance-200 where cardID='1010 3576 7028 6987'

 

-------------------------------------创建索引----------------------------------------------

if exists(select * from sysindexes where name='IX_transInfo_cardID')
 drop index transInfo.IX_transInfo_cardID
go
create nonclustered index IX_transInfo_cardID
on transInfo(cardID) with fillfactor=70
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

 

-------------------------------------创建银行卡信息表视图----------------------------------------------

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

 

-------------------------------------创建交易信息表视图----------------------------------------------

if exists(select * from sysobjects where name='view_transInfo')
 drop view view_transInfo
go
create view view_transInfo
as
select 交易日期=transDate,卡号=cardID,交易类型=tranType,交易金额=transMoney,备注=trmark from transInfo
go

 

-------------------------------------为交易信息表创建触发器----------------------------------------------
if exists(select 1 from sysobjects where name = 'trigger_transInfo_insert')
drop trigger trigger_transInfo_insert
go

create trigger trigger_transInfo_insert
on transInfo for insert
as
/*--定义变量：用于临时存放卡号、交易类型、交易金额、余额等--*/
declare @trantype varchar(20),@transMoney money,@cardID varchar(20),@balance int
/*--从inserted临时表中获取插入的记录行信息：包括交易类型、卡号、交易金额--*/
select @trantype=trantype,@transMoney=transMoney,@cardID=cardID from inserted
/*--从银行卡表获取余额--*/
select @balance=balance from cardInfo where cardID=@cardID
if(@trantype='支取')
 begin
  if(@balance<=@transMoney+1)
   begin
    raiserror('你的余额不足！帐上余额%d元',16,1,@balance)
   end
  else
   update cardInfo set balance=balance-@transMoney where cardId=@cardID
   print '支取'+ convert(varchar(40),@transMoney)
 end
else
 begin
 update cardInfo set balance=balance+@transMoney where cardId=@cardID
 print '存入'+ convert(varchar(40),@transMoney)
 end

if(@@error<>0)
 begin
  print'交易失败!'
  rollback tran
 end
else
 print'交易成功!'

select @balance=balance from cardInfo where cardID=@cardID
print '卡号'+@cardID+' 余额'+convert(varchar(20),@balance)+'元'
print ''
go

----------------------------------插入测试数据-----------------------------------------
set nocount on
insert into transInfo values(default,'1010 3576 7028 6987','存入',300,'')
go

 

----------------------------------创建取款存储过程-----------------------------------

create procedure proc_transInfo
@cardID varchar(20),
@pass varchar(20),
@transMoney money,
@trantype varchar(20)='支取'
as
declare @cardPass varchar(20)
select @cardPass=pass from cardInfo
if(@cardPass=@pass)
 begin
  insert into transInfo values(default,@cardID,@trantype,@transMoney,'')
 end
else
 print'密码不正确'
go

-----------------------------------调用取款存储过程-------------------------------------
set nocount on
exec proc_transInfo '1010 3576 7028 6987','123456',200
go

 

----------------------------------创建存款存储过程-----------------------------------

create procedure proc_transInfo_2
@cardID varchar(20),
@transMoney money,
@trantype varchar(20)='存入'
as
insert into transInfo values(default,@cardID,@trantype,@transMoney,'')
go

-----------------------------------调用存款存储过程-------------------------------------
set nocount on
exec proc_transInfo_2 '1010 3576 7028 6987',600

 

----------------------------------------创建随机卡号的存储过程-----------------------------------
if exists(select * from sysobjects where name='proc_randCardID')
 drop proc proc_randCardID
go

create procedure proc_randCardID
@randCardID varchar(20) output
as
declare @rand numeric(15,8)
select @rand=Rand((datepart(mm,getdate())*100000)+(datepart(ss,getdate())*1000)+datepart(ms,getdate()))

declare @aa varchar(20),@bb varchar(20),@cardId varchar(20)
set @cardId=@rand
set @aa=substring(@cardId,3,4)
set @bb=substring(@cardId,7,9)
set @randCardID='1010 3576'+' '+@aa+' '+@bb
go

----------------------------------------创建开户存储过程-----------------------------------

create procedure proc_openAccount
@name varchar(20),
@pid varchar(20),
@tel varchar(20),
@money money,
@savType varchar(20),
@addr varchar(100)
as
declare @mycardID varchar(20),@cur_customerID varchar(20)       --定义变量
exec proc_randCardID @mycardID output                           --调用过程产生卡号
while exists(select * from cardInfo where cardID=@mycardID) --判断卡号是否重复
 exec proc_randCardID @mycardID output   --重新产生随机号
insert into userInfo values(@name,@pid,@tel,@addr)  --在用户表插入新用户
select @cur_customerID=customerID from userInfo where pid=@pid
insert into cardInfo values(@mycardID,default,@savType,default,@money,@money,default,default,@cur_customerID) --在银行表插入新用户
go

 

----------------------------------------调用开户存储过程-----------------------------------

exec proc_openAccount '王五','440698546585878741','12312312311',1200,'活期','广东'
go

----------------------------------------创建转帐存储过程-----------------------------------

create proc proc_cardID
@cardID1 varchar(20),
@cardID2 varchar(20),
@money money
as

begin transaction
declare @sumError int
set @sumError=0
update cardInfo set balance=balance-@money where cardID=@cardID1
set @sumError=@sumError+@@error
update cardInfo set balance=balance+@money where cardID=@cardID2
set @sumError=@sumError+@@error
if(@sumError<>0)
 begin
  print'转帐失败!回滚事务'
  rollback tran
 end
else
 begin
  print'转帐成功!'
  commit transaction
 end

--insert into transInfo values(default,@cardID1,'支取',@money,'')
--insert into transInfo values(default,@cardID2,'存入',@money,'')
go

 

----------------------------------------调用转帐存储过程-----------------------------------

set nocount on
exec proc_cardID '1010 3576 7028 8888','1010 3576 7028 6987',200

 

----------------------------------------查看视图----------------------------------------
select * from view_userInfo
select * from view_cardInfo
select * from view_transInfo
go