SQL Server 自定义函数FUNCTION的使用

一、定义：
用户自定义函数的类型：

sql server 自定义函数分为三种类型：标量函数（Scalar Function）、内嵌表值函数（Inline Function）、多声明表值函数（Multi-Statement Function）

1、标量函数：标量函数是对单一值操作，返回单一值。

2、内嵌表值函数：内嵌表值函数的功能相当于一个参数化的视图。它返回的是一个表，内联表值型函数没有由BEGIN-END 语句括起来的函数体。

3、多声明表值函数：它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。

它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值型函数的不足。

二、三种自定函数的异同点
 1、同点：

      创建定义相同：  CREATE FUNCTION F_NAME(传入的参数名称    传入参数的类型)                                
                            RETURNS         返回值类型                                 
                            AS 

 2、异点：

             a.标量值函数返回的是一个数据类型值，内联表值函数返回的是一个table，而多语句表值函数返回的是一个table的变量             （类似前面两个的结合）；  
              b.语法的结构：标量值函数和多语句表值函数都是要有begin.........................end，内联表值函数就没有；      
              c.调用：标量函数要写成在dbo.function_name；

三、函数参数
参数可以是常量、表中的某个列、表达式或其他类型的值。在函数中有三种类型的参数。

1、输入：指必须输入一个值。

2、可选值：在执行该参数时，可以选择不输入参数。

3、默认值：函数中默认有值存在，调用时可以不指定该值。

四、举例说明：
1、标量值函数定义格式：

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS date_type   --返回返回值的数据类型
--[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
  function_body --函数体(即 Transact-SQL 语句)
  RETURN  表达式;
end

--编写一个函数，该函数，可以通过输入借书时间来判断是否到期，当借阅时间大于30天，返回已经过期；否则返回还未到期。
 
CREATE FUNCTION IsDateout(@BDate datetime)
returns nvarchar(20)
AS
BEGIN
    DECLARE @myresult nvarchar(20)
    IF (datediff(day,@BDate,getdate())>30)
    BEGIN
    SET @myresult='已过期'
    end
    else    
    begin
    set @myresult='未到期'
    end
    RETURN (@myresult)
END
 
SELECT dbo.IsDateout(cast('2018-01-01' AS datetime))--结果已过期
SELECT dbo.IsDateout(cast('2018-08-01' AS datetime))--结果未到期

PS:创建函数时指定了函数所有者，那么调用的时候也必须指定函数的所有者。（一般都为 dbo）

调用自定义函数时如果想不传入参数而使用默认值，那么必须使用 default 关键字。如果自定义函数的参数没有默认值，那么会返回 null。

2、内联表格值函数定义格式：

CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS table    --返回一个表
--[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
RETURN   （一条SQL语句）

下面自定义一个根据传入参数（即学生学号）来查询学生信息的 Function，示例如下：

use Test
go

create function dbo.SearchStuInfo(@Stu_No varchar(50)='001')
returns table
as
return(
select S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate
from Student where S_StuNo=@Stu_No)
go

执行用户自定义内嵌表值函数

1 select * from dbo.SearchStuInfo(default);    --返回学号为 001 的学生信息
2 
3 select * from dbo.SearchStuInfo('008');        --返回学号为 008 的学生信息

创建多声明表值函数的语法：

create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
returns @表变量 table 表的定义(即列的定义和约束)
[with {Encryption | Schemabinding }]
[as]
begin
    函数体(即 Transact-SQL 语句)
    return
end

下面自定义一个根据传入参数（即学生学号）来查询学生信息和课程，并计算出该学生的身高是否大于所有学生的平均身高的 Function，示例如下：

use Test
go

create function dbo.SearchStuCou(@StuN varchar(10)='001')
returns @StuInfo table (
    StuNo varchar(50) not null primary key,
    StuName varchar(10) not null,
    StuSex  varchar(10) not null,
    StuHeight varchar(10) null,
    StuAvgHeight decimal(10,2) null,
    StuConAvgHeight varchar(30) null,
    StuCou varchar(10) null
)
as
begin
    declare @Height decimal(10,2)
    declare @AvgHeight decimal(10,2)
    declare @ConAvgHeight varchar(30)

    select @AvgHeight=AVG(Convert(decimal(10,2),S_Height))        --查询所有同学的平均身高
    from Student

    select @Height=Convert(decimal(10,2),S_Height)        --查询指定学号的学生身高
    from Student
    where S_StuNo=@StuN

    if((@Height-@AvgHeight) > Convert(decimal(10,2),0))
    begin
        set @ConAvgHeight='大于平均身高'
    end
    else if((@Height-@AvgHeight) = Convert(decimal(10,2),0))
    begin
        set @ConAvgHeight='等于平均身高'
    end
    else
    begin
        set @ConAvgHeight='小于平均身高'
    end

    insert into @StuInfo(StuNo,StuName,StuSex,StuHeight,StuAvgHeight,StuConAvgHeight,StuCou)
    select s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,@AvgHeight,@ConAvgHeight,c.C_Name from Student s
    left join Course c on s.C_S_Id=c.C_Id
    where S_StuNo=@StuN

    return
end
go

执行用户自定义多声明表值函数：

1 select * from dbo.SearchStuCou(default);
2 
3 select * from dbo.SearchStuCou('015');
4 
5 select * from dbo.SearchStuCou('009');

适用范围

1.      只查询，不修改数据库的状态（修改、删除表中记录等）

2.      结果集需要通过递归等方法得到时，可以使用函数，函数比较灵活

3.      结果集需要直接被引用时，可以使用函数。需要对结果集进行再加工（指放在select语句中等），可以使用函数，函数可以嵌在select等sql语句中。

注意事项：

用户自定义函数不能用于执行一系列改变数据库状态的操作

在编写自定义函数时需要注意的：

对于标量函数：

1.      所有的入参前都必须加@

2.      create后的返回，单词是returns，而不是return

3.      returns后面的跟的不是变量，而是返回值的类型，如：int，char等。

4.      在begin/end语句块中，是return。

内嵌表值函数：

1.      只能返回table，所以returns后面一定是TABLE

2.      AS后没有begin/end，只有一个return语句来返回特定的记录。

多语句表值函数：

1.      returns后面直接定义返回的表类型，首先是定义表名，表明前面要加@，然后是关键字TABLE，最后是表的结构。

2.      在begin/end语句块中，直接将需要返回的结果insert到returns定义的表中就可以了，在最后return时，会将结果返回。

3.      最后只需要return，return后面不跟任何变量。