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

posted @ 2008-10-20 14:04  zmxmiss  阅读(997)  评论(0编辑  收藏  举报