T-SQL编程,存储过程编写和调用

发布网友发布时间：2022-04-08 00:37

共1个回答

热心网友时间：2022-04-08 02:07

一个完整的。看看吧 use master
go
--创建数据库
if exists(select * from sysdatabases where name = 'bankDB')
drop database bankDB
go
create database bankDB
on
(
name = 'bankDB',
filename = 'e:\bankDB.mdf',
size = 3mb,
filegrowth = 20%
)
gouse bankDB
go
--创建表UserInfo
if exists(select * from sysobjects where name = 'UserInfo')
drop table UserInfo
go
create table UserInfo
(
customerID int identity(1,1) not null,
customerName varchar(16) not null,
PID varchar(20) not null,
telephone varchar(15) not null,
address varchar(225)
)
goalter table UserInfo
add constraint PK_customerID primary key(customerID),
constraint UQ_PID unique (PID),
constraint CK_PID check((len(PID)=15) or (len(PID)=18)),
constraint CK_telephone check(len(telephone) between 11 and 13)--(telephone like '[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-9][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-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
goinsert into UserInfo(customerName,PID,telephone,address)
select '张三','123456789012345','010-67898978','北京海淀' union
select '李四','321245678912345678','0478-44443333',null
go--创建表cardInfo
if exists(select * from sysobjects where name = 'cardInfo')
drop table cardInfo
go
create table cardInfo
(
cardID 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(12) not null,
IsReportLoss varchar(2) not null,
customerID int not null
)
goalter table cardInfo
add constraint PK_cardID primary key (cardID),
constraint CK_cardID check(cardID 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 DF_pass default('888888') for pass,
constraint CK_IsReportLoss check(IsReportLoss in('是','否')),
constraint DF_IsReportLoss default('否') for IsReportLoss,
constraint FK_customerID foreign key(customerID) references UserInfo(customerID)
goinsert into cardInfo(cardID,savingType,openMoney,balance,customerID)
select '1010 3576 1234 5678','活期',1000,1000,1 union
select '1010 3576 1212 1134','定期',1,1,2
go--创建表transInfo
if exists(select * from sysobjects where name = 'transInfo')
drop table transInfo
go
create table transInfo
(
transDate datetime not null,
cardID varchar(19) not null,
transType varchar(4) not null,
transMoney money not null,
remark varchar(225)
)
goalter table transInfo
add constraint DF_transDate default(getDate()) for transDate,
constraint FK_cardID foreign key(cardID) references cardInfo(cardID),
constraint CK_transType check(transType in('存入','支取')),
constraint CK_transMoney check(transMoney>0)
goinsert into transInfo(cardID,transType,transMoney)
values('1010 3576 1234 5678','支取',900)
update cardInfo set balance=balance-900 where cardID='1010 3576 1234 5678'
insert into transInfo(cardID,transType,transMoney)
values('1010 3576 1212 1134','存入',5000)
update cardInfo set balance=balance+5000 where cardID='1010 3576 1212 1134'
go/*----------------常规业务模拟--------------*/
--张三修改卡号密码
update cardInfo set pass='123456' where cardID='1010 3576 1234 5678'
update cardInfo set pass='123123' where cardID='1010 3576 1212 1134'
go
--李四银行卡挂失
update cardInfo set IsReportLoss='是' where cardID='1010 3576 1212 1134'
go
--统计银行的资金流通余额和盈利结算
declare @inMoney money,@outMoney money
select @inMoney=sum(transMoney) from transInfo where transType='存入'
select @outMoney=sum(transMoney) from transInfo where transType='支取'
print '流通金额为：'+convert(varchar(10),@inMoney)+'RMB'
print '取出结算为：'+convert(varchar(10),@outMoney)+'RMB'
print '银行流通金额总计为：'+convert(varchar(10),(@inMoney-@outMoney))+'RMB'
go
--查询本月开户的用户，并显示该卡的相关信息
select 卡号=cardInfo.cardID,
卡号密码=pass,
客户姓名=customerName,
客户身份证号=PID,
电话=telephone,
地址=address,
存款类型=savingType,
开户金额=openMoney,
余额=balance,
币种=curType,
开户日期=openDate
from UserInfo
inner join cardInfo on UserInfo.customerID=cardInfo.customerID
inner join transInfo on cardInfo.cardID=transInfo.cardID
--查询本周
where openDate between (select dateadd(dd,-datepart(dw,getdate())+1,getdate())) and (select getdate())
go
--select getdate()
--select dateadd(dd,-datepart(dw,getdate())+1,getdate())
--select datepart(dw,getdate())
--select datediff(dd,(select dateadd(dd,-datepart(dw,getdate())+1,getdate())),getdate())
--查询挂失账号的客户信息
select 客户姓名=customerName,
电话=telephone
from UserInfo
where customerID=(select customerID from cardInfo where IsReportLoss='是')
go
--催款提醒服务
select 客户姓名=customerName,
联系电话=telephone,
账上余额=balance
from UserInfo inner join cardInfo on UserInfo.customerID=cardInfo.customerID
where balance<=200
go
--创建储存过程
--张三取款
set nocount on
if exists(select * from sysobjects where name ='proc_takeMoney')
drop procere proc_takeMoney
go
create procere proc_takeMoney
@card varchar(19),
@m money,
@type varchar(4),
@inputPass varchar(6)=''
as
begin transaction
declare @errorsum int --定义错误累加量
set @errorsum=0
print'交易正在进行，请稍后……'
declare @balance money
select @balance=balance from cardInfo where cardID=@card
update cardInfo set @balance=@balance-@m
where cardID=@card and pass=@inputPass
set @errorsum=@errorsum+@@error
if (@errorsum<>0)
begin
rollback transaction
print'取款失败！取款后卡上的余额必须大于1元，余额不足……'
print'卡号：'+@card+' '+'余额:'+convert(varchar(10),(@balance+@m))
end
else
begin
if @balance>=1 --如果取款后的余额仍大于1，则取款成功
begin
commit transaction
print'取款成功！'
insert into transInfo(cardID,transType,transMoney)
values(@card,@type,@m)
print'卡号：'+@card+' '+'余额:'+convert(varchar(10),(@balance+@m))
end
else
begin
rollback transaction
print'取款失败！取款后卡上的余额必须大于1元，余额不足……'
print'卡号：'+@card+' '+'余额:'+convert(varchar(10),(@balance+@m))
end
end
go
--创建存储过程
--李四存款
set nocount on
if exists(select * from sysobjects where name ='proc_inputMoney')
drop procere proc_inputMoney
go
create procere proc_inputMoney
@card varchar(19),
@m money,
@type varchar(4)
as
begin transaction