没有输入参数,没有输出参数的存储过程
--定义存储过程实现查询账号余额最低的银行卡账号信息,显示银行卡号,姓名,账户余额
create proc proc_CardInfo
as
begin
select *from BankCard left join AccountInfo on BankCard.AccountId=AccountInfo.AccountId;
end;
go
exec proc_CardInfo;
有输入参数,没有输出参数的存储过程
--模拟银行卡存钱操作,传入银行卡号,存钱金额,实现存钱操作
select *from BankCard;
select *from CardExchange;
create proc proc_BankCardExchange
@cardNo varchar(20),
@moneyInBnak money
as
begin
update BankCard set CardMoney=CardMoney+@moneyInBnak where CardNo=@cardNo;
if @@ERROR=0
begin
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values(@cardNo,@moneyInBnak,0,GETDATE());
end;
end;
exec proc_BankCardExchange '6225125478544588',200;
有输入参数,没有输出参数,但是有返回值的存储过程(返回值必须整数)
--模拟银行卡取钱操作,传入银行卡号,取钱金额,实现取钱操作
--取钱成功,返回1,取钱失败返回-1
create proc proc_DrawMoney
@cardNo varchar(20),
@drawMoney money
as
begin
if exists(select *from BankCard where CardNo=@cardNo and CardMoney>=@drawMoney)
begin
update BankCard set CardMoney=CardMoney-@drawMoney where CardNo=@cardNo;
if @@ERROR<>0 return -1;
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime) values
(@cardNo,0,@drawMoney,GETDATE());
return 1;
end
else
return -1;
end;
declare @result int;
exec @result= proc_DrawMoney '6225125478544588',100;
select @result;
有输入参数,有输出参数的存储过程
--查询出某时间段的银行存钱信息以及存款总金额,取款总金额
--传入开始时间、结束时间、显示存取款交易信息的同时,返回存款总金额,取款总金额
create proc proc_DrawMoneySumMoneyOfInOut
@st varchar(20),
@et varchar(20),
@moneyInBank money output,
@moneyOutBank money output
as
begin
select *from CardExchange where ExchangeTime
between convert(smalldatetime,@st+' 00:00:00')
and convert(smalldatetime,@et+' 23:59:59');
set @moneyInBank=(select SUM(MoneyInBank) from CardExchange where ExchangeTime
between convert(smalldatetime,@st+' 00:00:00')
and convert(smalldatetime,@et+' 23:59:59'));
set @moneyOutBank=(select SUM(MoneyOutBank) from CardExchange where ExchangeTime
between convert(smalldatetime,@st+' 00:00:00')
and convert(smalldatetime,@et+' 23:59:59'));
end;
declare @moneyInSum money;
declare @moneyOutSum money;
exec proc_DrawMoneySumMoneyOfInOut '2023-1-1','2024-1-1',@moneyInSum output,@moneyOutSum output;
select @moneyInSum;
select @moneyOutSum;