卡号出现异常！高手进来解决一下

 use master
go
/*--创建数据库(bank)--*/
--判断是否存在(bank)
if exists(select * from sysdatabases where name = 'bankDB')
 drop database bankDB
create database bankDB
on
(
 name = 'bankDB_data',
 filename = 'F:/bankDB_data.mdf',
 size = 5mb,
 maxsize = 10mb,
 filegrowth = 1mb
)
log on
(
 name = 'bankDB_log',
 filename = 'F:/bankDB_log.ldf',
 size = 2mb,
 filegrowth = 1mb
)
go
use bankDB
go
/*--创建用户信息表(userInfo)--*/
--判断用户表是否存在
if exists(select * from sysobjects where name = 'userInfo')
 drop table userInfo
create table userInfo
(
 customerID int identity(1,1) not null,--顾客编号，自动编号
 customerName nvarchar(10) not null,--开户姓名
 pID nvarchar(18) not null,--身份证好，只能是18位或者15位，身份证唯一
 telephone nvarchar(15) not null,--电话号码，格式xxxx-xxxxxxxx或者手机号11位
 address text--居住地
)
/*--为用户信息表(userInfo)添加约束条件--*/
--添加主键约束，要求顾客编号(customerID)作为主键
alter table userInfo
add constraint pk_customerID primary key(customerID)
--添加检查约束，要求身份证号(pID)18位或者15位
alter table userInfo
add constraint ck_pID check(len(pID) = 18 or len(pID) = 15)
--添加检查约束，要求电话号码(telephone)，格式xxxx-xxxxxxxx或者手机号11位
alter table userInfo
add constraint ck_telephone check(telephone like '0[1-9][1-9][1-9]-[1-9][1-9][1-9][1-9][1-9][1-9][1-9][1-9]' or telephone like '13[5-9][1-9][1-9][1-9][1-9][1-9][1-9][1-9][1-9]')
--添加唯一约束，要求身份证号(pID)唯一
alter table userInfo
add constraint uq_pID unique(pID)
--添加默认约束，要求居住地(address)默认为'地址不详'
alter table userInfo
add constraint df_address default('地址不详') for address
go
--判断银行卡信息表(cardInfo)是否存在
if exists(select * from sysobjects where name = 'cardInfo')
 drop table cardInfo
/*--银行卡信息表(cardInfo)--*/
create table cardInfo
(
 cardID nvarchar(19) not null,--卡号，主键
 curType nvarchar(4) not null,--货币种类，默认为'RMB'
 savingType nvarchar(2) not null,--存款类型：活期/定活两期/定期
 openDate datetime not null,--卡户日期，默认为当前时间
 openMoney money not null,--开户金额，不能低于1元
 balance money not null,--余额，不能低于1元，否则将销户
 password int not null,--密码：6位数字，开户时默认为6个'8'
 IsReportLoss nvarchar(2) not null,--是否挂失，默认为'否'
 customerID int not null--顾客编号
)
/*--为银行卡信息表(cardInfo)添加约束条件--*/
--添加主键约束，要求卡号(cardID)作为主键
alter table cardInfo
add constraint pk_cardID primary key(cardID)
--添加默认约束，要求货币种类(curType)默认为'RMB'
alter table cardInfo
add constraint df_curType default('RMB') for curType
--添加默认约束，要求开户日期(openDate)默认为当前时间
alter table cardInfo
add constraint df_openDate default(getdate()) for openDate
--添加检查约束，要求开户金额(openMoney)不能低于1元
alter table cardInfo
add constraint ck_openMoney check(openMoney >= 1)
--添加检查约束，要求余额(balance)不能低于1元
alter table cardInfo
add constraint ck_balance check(balance >= 1)
--添加检查约束，要求密码(password)为6位数字
alter table cardInfo
add constraint ck_password check(password like '[0-9][0-9][0-9][0-9][0-9][0-9]')
--添加默认约束，要求密码(password)默认为6个8
alter table cardInfo
add constraint df_password default(888888) for password
--添加默认约束，要求是否挂失(IsReportLoss)默认为'否'
alter table cardInfo
add constraint df_IsReportLoss default('否') for IsReportLoss
--添加外键约束，
alter table cardInfo
add constraint fk_customerID foreign key(customerID) references userInfo(customerID)
go
/*--创建交易信息表(transInfo)--*/
create table transInfo
(
 transDate datetime not null,--交易日期，默认为当前日期
 cardID nvarchar(19) not null,--卡号，外键
 transType nvarchar(2) not null,--交易类型，只能是存入/支取
 transMoney money not null,--交易金额大于0
 remark text--备注
)
/*--为交易信息表(transInfo)添加约束条件--*/
--添加默认约束。要求交易日期(transDate)默认为当前日前
alter table transInfo
add constraint df_transDate default(getdate())for transDate
--添加检查约束。要求交易类型(transType)只能是存入/支取
alter table transInfo
add constraint ck_transType check(transType = '存入' or transType = '支取')
--添加检查约束。要求交易金额(transMoney)大于0
alter table transInfo
add constraint ck_transMoney check(transMoney > 0)
--添加外键约束，要求卡号(cardID)作为外键
alter table transInfo
add constraint fk_cardID foreign key(cardID) references cardInfo(cardID)
go
select * from userInfo
select * from cardInfo
select * from transInfo
/*--向用户信息表(userInfo)插入数据--*/
insert userInfo
select '张三','123456789012345','0832-67899876','成都锦江区' union
select '李四','511025198908276435','0831-86133199','成都青羊区'
/*--向银行卡信息表(cardInfo)插入数据--*/
insert cardInfo(cardID,savingType,openMoney,balance,customerID)
select '1010 3576 1234 5678','活期',1000,1000,1 union
select '1010 3576 1212 1134','定期',1,1,2
/*--插入交易信息表(transInfo)--*/
--要求张三的卡号(1010 3576 1234 5678)取款900元，李四的卡号(1010 3576 1212 1134)存款5000元
set nocount on--返回结果不显示行
--张三取款900元
--开始事务
begin tran
declare @tranCardID nvarchar(19)--交易卡号
set @tranCardID = '1010 3576 1234 5678'
declare @transMoney money--交易金额
set @transMoney = 900
declare @countError int--记录是否有错误
set @countError = 0
update cardInfo set balance = balance - @transMoney where cardID = @tranCardID--更新余额
set @countError = @countError + @@error
/*--向交易信息表中插入信息(transInfo)--*/
insert into transInfo (cardID,transType,transMoney) values (@tranCardID,'支取',@transMoney)
if @countError <> 0
 begin
  print '交易出现异常！交易失败！回滚事务！'
  rollback tran
 end
else
 begin
  print '交易成功！提交事务！'
  commit tran
 end
go
--李四存入5000元
begin tran
declare @tranCardID nvarchar(19)--交易卡号
set @tranCardID = '1010 3576 1212 1134'
declare @transMoney money--交易金额
set @transMoney = 5000
declare @countError int--记录是否有错误
set @countError = 0
update cardInfo set balance = balance + @transMoney where cardID = @tranCardID--更新银行卡余额的信息
/*--向交易信息表中插入信息(transInfo)--*/
insert into transInfo (cardID,transType,transMoney) values (@tranCardID,'存入',@transMoney)
set @countError = @countError + @@error
if @countError <> 0
 begin
  print '交易出现异常！交易失败！回滚事务！'
  rollback tran
 end
else
 begin
  print '交易成功！提交事务！'
  commit tran
 end
go
select * from cardInfo
select * from transInfo
/*--修改密码--*/
--修改张三的密码为123456
declare @upCardID nvarchar(19)--卡号
set @upCardID = '1010 3576 1234 5678'
update cardInfo set password = 123456 where cardID = @upCardID
--修改李四的密码为123123
set @upCardID = '1010 3576 1212 1134'
update cardInfo set password = 123123 where cardID = @upCardID
go
/*--银行卡挂失--*/
--李四的卡挂失
declare @lossCardID nvarchar(19)--挂失的卡号
set @lossCardID = '1010 3576 1212 1134'
update cardInfo set IsReportLoss = '是' where cardID = @lossCardID
/*--统计银行的资金流通余额和盈利结算--*/
declare @inSumMoney money--总存入量
declare @outSumMoney money--总支出量
declare @passSumMoney money--流通资金
declare @getMoney money--盈利
select @inSumMoney = sum(transMoney) from transInfo where transType = '存入'
select @outSumMoney = sum(transMoney) from transInfo where transType = '支取'
set @passSumMoney = @inSumMoney - @outSumMoney
set @getMoney = @outSumMoney * 0.008 - @inSumMoney * 0.003
print '资金流通余额总计为：' + convert(nvarchar(20),@passSumMoney) + 'RMB'
print '盈利结算为：' + convert(nvarchar(20),@getMoney) + 'RMB'
/*--获取本周开户的卡号，和卡的相关信息--*/
select * from cardInfo where (select datediff(mm,openDate,getdate())) < (select datepart(dw,getdate()))
/*--查询本月交易金额最高的卡号--*/
select cardID from transInfo where transMoney = (select max(transMoney) from transInfo where (select datepart(mm,transDate)) = (select datepart(mm,getdate())))
/*--查询挂失账号的客户信息--*/
select userInfo.customerID as 顾客编号,userInfo.customerName as 顾客姓名,userInfo.pID as 身份证号,userInfo.telephone as 联系电话,userInfo.address as 居住地址
from userInfo
left join cardInfo on userInfo.customerID = cardInfo.customerID
where cardInfo.IsReportLoss = '是'
/*--催款提醒业务--*/
select userInfo.customerName as 客户姓名,userInfo.telephone as 联系电话,cardInfo.balance as 账上余额
from userInfo,cardInfo
where userInfo.customerID = cardInfo.customerID and cardInfo.customerID in(select customerID from cardInfo where balance < 200)
use bankDB
go
/*--为cardInfo表中字段cardID创建索引--*/
--检测是否已经存在该索引'ix_cardInfo_cardID'
if exists(select * from sysindexes where name = 'ix_cardInfo_cardID')
 drop index cardInfo.ix_cardInfo_cardID
/*--卡号列创建非聚集索引--*/
create nonclustered index ix_cardInfo_cardID
 on cardInfo(cardID)
  with fillfactor = 30
go
/*--查找张三(1010 3576 1234 5678)的交易记录--*/
declare @cardID nvarchar(19)
select @cardID = cardID from cardInfo with(index = ix_cardInfo_cardID) where cardID = '1010 3576 1234 5678'
select * from transInfo where cardID = @cardID
/*--创建视图view_userInfo--*/
--检测是否存在该视图
if exists(select * from sysobjects where name = 'view_userInfo')
 drop view view_userInfo
go
/*--创建视图--*/
create view view_userInfo
 as
  select customerID as 客户编号,customerName as 开户姓名,telephone as 电话号码,address as 居住地址  from userInfo
go
select * from view_userInfo
/*--创建视图view_cardInfo--*/
if exists(select * from sysobjects where name = 'view_cardInfo')
 drop view view_cardInfo
go
/*--开始创建视图--*/
create view view_cardInfo
 as
  select cardID as 卡号,curType as 货币种类,savingType as 存款类型,openDate as 开户日期,openMoney as 开户金额
  ,balance as 余额,password as 密码,IsReportLoss as 是否挂失,customerID as 顾客编号 from cardInfo
go
select * from view_cardInfo
/*--创建视图view_transInfo--*/
if exists(select * from sysobjects where name = 'view_transInfo')
 drop view view_transInfo
go
/*--开始创建视图--*/
create view view_transInfo
 as
  select transDate as 交易日期,cardID as 卡号,transType as 交易类型,transMoney as 交易金额,remark as 备注 from transInfo
go
select * from view_transInfo
/*--创建取钱或者存钱的储存过程--*/
/*--检测该储存过程是否存在--*/
if exists(select * from sysobjects where name = 'proc_outMoney')
 drop proc proc_outMoney
go
--张三从卡号(1010 3576 1234 5678)上支取300元
/*--开始创建(proc_outMoney)--*/
create proc proc_outMoney
 @cardID nvarchar(19),--卡号
 @password int,--密码
 @outMoney money,--支取金额
 @type nvarchar(2)--交易类型
 as
  print '交易正在进行，请稍后.....'
  declare @psw int--获得密码
  declare @balance money
  select @psw = password from cardInfo where cardID = @cardID
   if @password <> @psw
    begin
     raiserror('输入的密码与用户密码不匹配！取款结束！',16,1)
     return--立即返回
    end
   else
    begin
     if @outMoney >= @balance
      begin
       select @balance = balance from cardInfo where cardID = @cardID
       raiserror('交易失败！余额不足！',16,1)
       print '卡号：' + @cardID + ' ' + '余额:' + convert(nvarchar(20),@balance)
       return--立即返回
      end
     else
      begin
       update cardInfo set balance = balance - @outMoney where cardID = @cardID
       select @balance = balance from cardInfo where cardID = @cardID
       print '交易成功!交易金额：' + convert(nvarchar(20),@outMoney)
       print '卡号：' + @cardID + ' ' + '余额:' + convert(nvarchar(20),@balance)
       /*--向交易信息表中插入数据--*/
       insert into transInfo (cardID,transType,transMoney) values(@cardID,@type,@outMoney)
       return--立即返回
      end
    end
go
exec proc_outMoney '1010 3576 1234 5678',123456,200000,'支取'
--创建存钱的储存过程
if exists(select * from sysobjects where name = 'proc_InMoney')
 drop proc proc_InMoney
go
--卡号1010 3576 1212 1134存钱
create proc proc_InMoney
 @cardID nvarchar(19),--存钱账号
 @type nvarchar(2),
 @InMoney money--存入金额
 as
  declare @balance money
  print '交易正在进行！请稍后.....'
  if @InMoney < 1
   begin
    raiserror('交易金额不能少于1RMB!交易失败！',16,1)
    return
   end
  else
   begin
    /*--向交易信息表中插入数据--*/
    insert into transInfo (cardID,transType,transMoney) values(@cardID,@type,@InMoney)
    print '交易成功！交易金额:' + convert(nvarchar(20),@InMoney)
    --更新余额
    update cardInfo set balance = balance + @InMoney where cardID = @cardID
    select @balance = balance from cardInfo where cardID = @cardID
    print '卡号：' + @cardID + ' ' + '余额:' + convert(nvarchar(20),@balance)
   end
go
exec proc_InMoney'1010 3576 1212 1134','存入',500
/*--产生随机卡号(1010 3576 + ....)--*/
--创建输出参数的储存过程
--检测是否存在
if exists(select * from sysobjects where name = 'proc_randCardID')
 drop proc proc_randCardID
go
/*--开始创建储存过程--*/
create proc proc_randCardID
 @tempCardID nvarchar(19) output
 as
  declare @tempRand nvarchar(16)
  declare @middleRand nvarchar(4)
  declare @lastRand nvarchar(4)
  declare @rand numeric(16,8)--15位，保留8位
  select @rand = rand((datepart(mm,getdate())) * 1000000) + ((datepart(ss,getdate())) * 1000) + datepart(ms,getdate())
  set @tempRand = convert(nvarchar(16),@rand)
  set @middleRand = substring(@tempRand,7,10)
  set @lastRand = substring(@tempRand,11,14)
  set @tempCardID = '1010 3576' + ' ' + @middleRand + ' ' + @lastRand
go
declare @randCardID nvarchar(19) 
exec proc_randCardID @randCardID output
print '产生的随机卡号为：' + @randCardID
go
/*--开户的储存过程proc_openAccount--*/
if exists(select * from sysobjects where name = 'proc_openAccount')
 drop proc proc_openAccount
go
/*--开始创建--*/
create proc proc_openAccount
 @openDateOut datetime output,--开户时间
 @randCardID nvarchar(19) output,
 @openMoneyOut money output,
 @customerName nvarchar(10),--账户名
 @pID nvarchar(18),--身份号
 @telephone nvarchar(15),--电话号码
 @address text = ' ',--居住地址
 @openMoney money,--开户金额
 @savingType nvarchar(2)--存款类型
 as
  --调用产生卡号的储存过程
  declare @customerID int--顾客编号
  declare @myCardID nvarchar(19)
  exec proc_randCardID @myCardID output
  while exists(select * from cardInfo where cardID = @myCardID)
   exec proc_randCardID @myCardID output
  set @randCardID = @myCardID
  set @openMoneyOut = @openMoney
  /*--向客户信息表中插入数据--*/
  insert into userInfo values(@customerName,@pID,@telephone,@address)
  --获得顾客编号
  select @customerID = customerID from userInfo where pID = @pID
  /*--向卡号信息表中插入数据--*/
  insert into cardInfo (cardID,savingType,openMoney,balance,customerID) values(@myCardID,@savingType,@openMOney,@openMOney,@customerID)
  select @openDateOut = openDate from cardInfo where customerID = @customerID
go
declare @openDateOut datetime
declare @randCardIDOut nvarchar(19)
declare @openMoneyOut money
exec proc_openAccount @openDateOut output,@randCardIDOut output,@openMoneyOut output,
       @customerName = '王五',@pID = '334456889012678',@telephone = '0844-63598978',
       @openMoney = 1000,@savingType = '活期',
       @address = '河南新乡'
print '尊敬的客户，开户成功！，系统为您产生的随机卡号为：' + @randCardIDOut
print '开户日期' + CONVERT(varchar(100), @openDateOut, 111) + '开户金额为' + convert(nvarchar(20),@openMoneyOut)
go
declare @openDateOut datetime
declare @randCardIDOut nvarchar(19)
declare @openMoneyOut money
exec proc_openAccount @openDateOut output,@randCardIDOut output,@openMoneyOut output,
       @customerName = '赵二',@pID = '213445678912342222',@telephone = '0855-63598978',
       @openMoney = 1,@savingType = '定期'
print '尊敬的客户，开户成功！，系统为您产生的随机卡号为：' + @randCardIDOut
print '开户日期' + CONVERT(varchar(100), @openDateOut, 111) + '开户金额为' + convert(nvarchar(20),@openMoneyOut)
go
select * from view_userInfo
select * from view_cardInfo
if exists(select * from sysobjects where name = 'proc_trans')
 drop proc proc_trans
go
create proc proc_trans
 @cardIDOut nvarchar(19),--李四的账号
 @cardIDIn nvarchar(19),--张三的账号
 @changMoney money--交易资金
 as
  begin tran--开始事务
  print '开始转账，请稍后....'
  declare @countError int--记录是否有错
  set @countError = 0
  exec proc_outMoney @cardIDOut,123123,@changMoney,'支取'
  set @countError = @countError + @@error
  exec proc_InMoney @cardIDIn,'存入',@changMoney
  if @countError <> 0
   begin
    rollback tran
   end
  else
   begin
    print '转账成功！'
    commit tran
   end
go
exec proc_trans @cardIDOut = '1010 3576 1212 1134',@cardIDIn = '1010 3576 1234 5678',@changMoney = 200