存储过程之SQLServer

没有输入参数，没有输出参数的存储过程

--定义存储过程实现查询账号余额最低的银行卡账号信息，显示银行卡号，姓名，账户余额
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;