T-SQL语句(建库,建表,事务,存储过程,触发器等...)
drop database bankDB
use master
exec sp_attach_db @dbname='bankDB',@filename1='f:\bank\bankDB_data.mdf' --附加数据库,参数1:物理名(数据库名),参数2:文件路径名
exec sp_detach_db 'bankDB',null --分离数据库 参数1:数据库名,参数2:类型值 ???
go
use master
go
if exists(select * from sysdatabases where name = 'bankDB') --判断系统是否存在bankDB数据库,存在就删除
drop database bankDB
go
exec xp_cmdshell 'mkdir F:\bank' --新建文件夹
go
create database bankDB --新建数据库名
on primary
(
name = bankDB_data, --逻辑名
filename = 'F:\bank\bankDB_data.mdf', --主数据库名
size = 10mb, --文件大小
filegrowth = 15% --增长率
)
log on
(
name = bankDB_log, --逻辑名
filename = 'F:\bank\bankDB_log.ldf', --日志文件
size = 1mb, --文件大小
maxsize = 20mb, --文件最大容量
filegrowth = 10% --增长率
)
go
exec xp_cmdshell 'rmdir F:\bank' --删除数据库
use bankDB
go
if exists(select * from sysobjects where name = 'userInfo') --判断系统是否存在该表名,存在删除
drop table userInfo
go
create table userInfo --新建表名
(
UID int not null identity(1,1), --顾客编号,自动增长
Uname varchar(8) not null, --开户名,必填
PID varchar(18) not null, --身份证号,必填
telephone varchar(13) not null, --电话号码,必填
address varchar(32) --地址,可选输入
)
alter table userInfo
add constraint PK_UID primary key (UID), --主键约束
constraint UQ_PID unique (PID), --唯一约束
constraint CK_PID check (len(PID)=18 or len(PID)=15), --检查约束
constraint CK_telephone check (telephone like '0[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 len(telephone)=11)
create table cardInfo
(
CUID int not null, --顾客编号,外键
CID varchar(19) not null, --卡号,必填
curType varchar(10) not null, --货币种类,必填
savingType varchar(10) not null, --存款类型,必填
openDate datetime not null, --开户日期,必填
openMoney Money not null, --开户金额,必填
balance Money not null, --余额,必填
pass varchar(8) not null, --密码,必填
IsReportLoss bit not null --是否挂失,必填
)
alter table cardInfo
add constraint PK_CID primary key (CID), --主键约束,CID为主键
constraint CK_CID check (CID 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 CK_pass check(len(pass)>=6),
constraint DF_pass default('888888') for pass,
constraint DF_IsReportLoss default(0) for IsReportLoss,
constraint FK_CUID foreign key(CUID) references userInfo(UID) --主外键约束,CUID为外键
create table transInfo
(
transDate datetime not null, --交易日期,必填
TCID varchar(19) not null, --卡号,外键
transType varchar(10) not null, --交易类型,必填
transMoney Money not null, --交易金额,必填
remark varchar(32) --备注,可选输入
)
alter table transInfo
add constraint DF_date default(getDate()) for transDate,
constraint FK_TCIK foreign key(TCID) references cardInfo(CID),
constraint CK_transType check(transType='存入' or transType='支取'),
constraint CK_transMoney check(transMoney>0)
dbcc checkident (transInfo,reseed,0) --将标识列重置
select * from userinfo
select * from cardinfo
select * from transinfo
/*$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 产生随机卡号和开户的存储过程 */
--生产随机卡号的存储过程
if exists (select * from sysobjects where name='pr_cid')
drop procedure pr_cid
go
create procedure pr_cid @randcardid
char(19) output
as
declare @r numeric(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 ='1010 3576 '+substring(@tempstr,3,4)+' '+substring(@tempstr,7,4)
--测试
declare @mycardid varchar(28)
execute pr_cid @mycardid output
print '产生的随机卡号为:'+@mycardid
--开户的存储过程
if exists (select * from sysobjects where name='pr_user')
drop procedure pr_user
go
create procedure pr_user
@cname varchar(8),
@pid varchar(18),
@phone varchar(13),
@address varchar(50),
@openmoney money,
@savtype varchar(8)
as
declare @mycardid char(19)
execute pr_cid @mycardid output --得到随机卡号
while exists (select * from cardinfo where cid=@mycardid) --判断是否存在相同卡号
execute pr_cid @mycardid output --如果存在,再得到随机卡号
if not exists (select * from userinfo where pid=@pid) --判断是否存在相同身份证
begin
print '您生成的卡号为:'+convert(varchar(19),@mycardid)
insert into userinfo (uname,pid,telephone,address)
values (@cname,@pid,@phone,@address)
declare @cuid int
select @cuid=uid from userinfo where pid=@pid
insert into cardinfo (cid,savingtype,openmoney,balance,cuid)
values (@mycardid,@savtype,@openmoney,@openmoney,@cuid)
end
else
begin
print '您的信息已存在,生成的卡号为:'+convert(varchar(19),@mycardid)
declare @cuid2 int
select @cuid2=uid from userinfo where pid=@pid
insert into cardinfo (cid,savingtype,openmoney,balance,cuid)
values (@mycardid,@savtype,@openmoney,@openmoney,@cuid2)
end
go
--测试
exec pr_user '张三','123456789012345','010-67898978','北京海淀',1000,'活期'
exec pr_user '李四','213445678912342222','0760-44446666','',1,'定期'
/*$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 创建transinfo表中插入数据时触发器(包含帐户状态的支取情况) */
--创建触发器
if exists (select * from sysobjects where name='tr_insert')
drop trigger tr_insert
go
create trigger tr_insert on transinfo for insert
as
declare @tcid varchar(19),@tr_type varchar(4),@tr_money money
select @tcid=tcid,@tr_type=transtype,@tr_money=transmoney from inserted --得到临时表里的值
if exists(select * from cardinfo where cid=@tcid)
begin
if(@tr_type='支取')
begin
declare @ca_money money,@ca_num int
select @ca_money=balance,@ca_num=isreportloss from cardinfo where cid=@tcid --得到帐户的金额和状态
if(@ca_money>@tr_money+1 and @ca_num=0) --判断金额足够和状态不为挂失
begin
update cardinfo set balance=balance-@tr_money where cid=@tcid
print '您成功的支取'+convert(varchar(8),@tr_money)+'元!'
end
else
begin
print '对不起!你的支取失败,可能原因:帐户金额不够或帐户被挂失!'
rollback tran --回滚
end
end
else
begin
update cardinfo set balance=balance+@tr_money where cid=@tcid
print '您成功的存入'+convert(varchar(8),@tr_money)+'元!'
end
end
else
begin
print '对不起'+ @tcid +'该帐户不存在!'
rollback tran --回滚
end
go
--测试
declare @card char(19)
select @card=cid from cardinfo as c inner join userinfo as u on c.cuid=u.uid where uname='张三' --1000
insert into transinfo (tcid,transtype,transmoney)
values (@card,'支取',100)
update cardinfo set isreportloss=0 where cuid=1 --改变帐户状态测试
declare @card char(19)
select @card=cid from cardinfo as c inner join userinfo as u on c.cuid=u.uid where uname='李四' --1
insert into transinfo (tcid,transtype,transmoney)
values (@card,'存入',200)
/*$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 创建插入交易信息的存储过程(验证密码) */
if exists(select * from sysobjects where name='pr_pass')
drop procedure pr_pass
go
create procedure pr_pass
@tcid varchar(19),
@tr_type varchar(4),
@tr_money money,
@pass varchar(8),
@one int output
as
if(@tr_type='支取')
begin
declare @ca_pass varchar(8)
select @ca_pass=pass from cardinfo where cid=@tcid
if(@ca_pass=@pass)
begin
insert into transinfo (tcid,transtype,transmoney)
values (@tcid,@tr_type,@tr_money)
end
else
begin
print '对不起,输入的密码不正确!'
set @one=1
return
end
end
else
begin
insert into transinfo (tcid,transtype,transmoney)
values (@tcid,@tr_type,@tr_money)
end
go
--测试
declare @card1 char(19),@one1 int
select @card1=cid from cardinfo as c inner join userinfo as u on c.cuid=u.uid where uname='张三'
exec pr_pass @card1,'支取',300,'123456',@one1 output
declare @card2 char(19),@one int
select @card2=cid from cardinfo as c inner join userinfo as u on c.cuid=u.uid where uname='李四'
exec pr_pass @card2,'存入',300,'888888',@one output
/*$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 创建转账的存储过程(包含事务) */
if exists(select * from sysobjects where name='pr_trfer')
drop procedure pr_trfer
go
create procedure pr_trfer
@card1 varchar(19),
@pass varchar(8),
@tr_money money,
@card2 varchar(19)
as
begin tran
declare @one int
exec pr_pass @card1,'支取',@tr_money,@pass,@one output
exec pr_pass @card2,'存入',@tr_money,@pass,@one output
if(@one!=0)
begin
print '操作失败!'
rollback tran
end
else
begin
print '操作成功!'
commit tran
end
go
--测试
declare @card1 varchar(19),@card2 varchar(19)
select @card1=cid from cardinfo as c inner join userinfo as u on c.cuid=u.uid where uname='张三'
select @card2=cid from cardinfo as c inner join userinfo as u on c.cuid=u.uid where uname='李四'
exec pr_trfer @card1,'123456',100,@card2
转载：http://kongjian.baidu.com/foible/blog/item/244ab7f9d85c075a252df2db.html
