SQL Server—函数

1、函数的定义：是指将一些独立的、具有重用性的功能进行模块化和封装。

2、函数的分类：系统函数、自定义函数。

（1）系统函数

max、min、avg、sum等聚合函数都是系统函数。

（2）自定义函数：标量值函数（返回单个值），表值函数（返回查询结果）

3、函数应用实例

举例1：编写一个函数求该银行的金额总和（没有参数，返回标量值）（标量值函数应用实例）

--创建函数
create function GetSumMoney() returns money  --1、()内可以写参数，这里没有参数，所以不写；2、函数声明returns后面跟的是声明的类型，这里是金额类型
as
begin
    declare @sum money
    select @sum = (select sum(CardMoney) from BankCard)  --这里直接查询的结果没办法展示，要通过定义一个变量来返回展示
    return @sum  --函数返回用的是return
end
--注意：函数声明用的是returns，函数返回用的是return

--函数调用
select dbo.GetSumMoney()  --调用函数时，记得函数前面要加 dbo.

/*
function：['fʌŋ(k)ʃ(ə)n] 功能 / 目的 / 函数关系
*/

举例2：传入账户编号，返回账户真实姓名 （标量值函数应用实例）

--创建函数
create function GetRealNameById(@accid int) returns varchar(30) 
as
begin
    declare @name varchar(30)
    select @name = (select RealName from AccountInfo where AccountId = @accid)
    return @name
end

--函数调用
select dbo.GetRealNameById(1)  --这里括号里的数字1是指传入的账户编号
--调用函数时，记得函数前面要加 dbo.

举例3：传递开始时间和结束时间，返回交易记录( 存钱取钱)，交易记录中包含真实姓名，卡号，存钱金额，取钱金额，交易时间

方案一：可处理复杂逻辑，函数体除了sql查询之外还可以有其他的逻辑代码

--创建函数
create function GetRecordByTime(@start varchar(30),@end varchar(30)) 
returns @result table  --@result为定义的返回的表名
(
    RealName varchar(20), --真实姓名
    CardNo varchar(30)，  --银行卡号(与银行卡表形成主外键关系)
    MoneyInBank money,    --存钱金额
    MoneyOutBank money,   --取钱金额
    ExchangeTime smalldatetime --交易时间
)
as
begin
    insert into @result  --这里的意思是把下面查出来的表值插入到@result表里，后面用return结尾
    select RealName 姓名,CardExchange.CardNo 卡号,MoneyInBank 存钱金额,MoneyOutBank 取钱金额,ExchangeTime 交易时间
    from CardExchange
    inner join BankCard on CardExchange.CardNo = BankCard.CardNo
    inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
    where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59'
    return  --return为返回插入表里的值
end

--函数调用
select * from GetRecordByTime('2020-1-1','2020-12-12')  --因为这里是表值函数，最终展示的结果是表记录的形式，所以把函数GetRecordByTime当成是一张表来看待

方案二：函数体内只能有 return + sql查询结果，不能有如变量之类的参数

--创建函数
create function GetRecordByTime(@start varchar(30),@end varchar(30)) 
returns table
as
    return  --返回下面sql语句查询的结果
    select RealName 姓名,CardExchange.CardNo 卡号,MoneyInBank 存钱金额,MoneyOutBank 取钱金额,ExchangeTime 交易时间
    from CardExchange
    inner join BankCard on CardExchange.CardNo = BankCard.CardNo
    inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
    where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59'
go

--函数调用
select * from GetRecordByTime('2020-1-1','2020-12-12')  --因为这里是表值函数，最终展示的结果是表记录的形式，所以把函数GetRecordByTime当成是一张表来看待

注意：通过两种方案的对比，各有优劣。在实际应用场景中，需要结合实际业务需求，选择合适的方案。如果是检查的查询处理，可选择方案二；如果需要通过复杂的处理，如定义多个变量之类的，应选择方案一，复杂归复杂，但实现的功能有很多。

举例4：查询银行卡信息，将银行卡状态1,2,3,4分别转换为汉字“正常、挂失、冻结、注销”；根据银行卡余额显示银行卡等级：30万以下为“普通用户”,30万及以上为“VIP用户”；分别显示卡号，身份证，姓名，余额，用户等级，银行卡状态。

--创建用户等级函数
create function GetGrade(@cardmoney money) returns varchar(30)
as
begin
    declare @result varchar(30)
    if @cardmoney >= 300000
        set @result = 'VIP用户'
    else
        set @result ='普通用户'
    return @result
end

--创建银行卡状态函数
create function GetState(@state int) returns varchar(30)
as
begin
    declare @result varchar(30)
    if @state = 1
        set @result = '正常'
    else if @state = 2
        set @result = '挂失'
    else if @state = 3
        set @result = '冻结'
    else if @state = 4
        set @result = '注销'
    else
        set @result = '异常'
    return @result
end

--函数调用
select CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额,dbo.GetGrade(CardMoney) 用户等级,dbo.GetState(CardState) 银行卡状态
from BankCard inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId

当然，如果是单纯的求出题目要求的结果，直接用查询语句也没有多麻烦；如果是实际应用当中经常要用到用户等级和银行卡状态的查询，此时创建两个函数进行调用是非常方便的。