函数之SQLServer

• 编写一个函数求该银行的金额总和(没有参数，返回标量值)

create function GetSumMoney() returns money
as 
begin
declare @sum money;
set @sum=(select sum (CardMoney) from BankCard);
return @sum;
end;

• 传入账号编号，返回账号真实姓名

create function GetRealNameById(@accid int) returns varchar(30)
as
begin
declare @name varchar(30);
set @name=(select RealName from AccountInfo where AccountId=@accid);
return @name;
end;
--调用函数
select dbo.GetRealNameById(1);

• 传递开始时间和结束时间，返回交易记录（存钱取钱）交易记录中包含真实姓名、卡号、存钱金额、取钱金额、交易时间

--方案一
drop function GetRecordByTime1;
create function GetRecordByTime1(@st varchar(20),@et varchar(20))
returns @result table
	(
		RealName varchar(20),--真实姓名
		CardNo varchar(20),--银行卡号
		MoneyInBank money,--存钱金额
		MoneyOutBank money,--取钱金额
		ExchangeTime smalldatetime --交易时间
	)
as 
	begin
	insert into @result
		select AccountInfo.RealName, BankCard.CardNo,CardExchange.MoneyInBank,CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchange 
		inner join BankCard on CardExchange.CardNo=BankCard.CardNo
		inner join AccountInfo on AccountInfo.AccountId=BankCard.AccountId
		where CardExchange.ExchangeTime between CONVERT(smalldatetime,@st+' 00:00:00')  and  CONVERT(smalldatetime,@et+' 23:59:59');
		return;
	end;
--调用函数
select *from dbo.GetRecordByTime1('2023-7-1','2023-8-1');

--方案二:函数体只能有return+sql
drop function GetRecordByTime2;
create function GetRecordByTime2(@st varchar(30),@et varchar(30))
returns table
as 
return
select AccountInfo.RealName, BankCard.CardNo,CardExchange.MoneyInBank,CardExchange.MoneyOutBank,CardExchange.ExchangeTime from CardExchange 
		inner join BankCard on CardExchange.CardNo=BankCard.CardNo
		inner join AccountInfo on AccountInfo.AccountId=BankCard.AccountId
		where CardExchange.ExchangeTime between CONVERT(smalldatetime,@st+' 00:00:00')  and  CONVERT(smalldatetime,@et+' 23:59:59');
go
--调用函数
select *from dbo.GetRecordByTime2('2023-7-1','2023-8-1');

• 返回银行卡状态

create function GetState(@state int) returns varchar(10)
as
begin
declare @result varchar(10);
set @result=case
when @state=1 then '正常'
else '异常'
end
return @result;
end;
--调用函数
select CardNo,AccountId,dbo.GetState(CardSate)State from BankCard;