SQL Server（00）：用户自定义函数（UDF）

一、UDF的定义

原文：创建用户定义函数（数据库引擎） - SQL Server | Microsoft Docs

和存储过程很相似，用户自定义函数也是一组有序的T-SQL语句，UDF被预先优化和编译并且可以作为一个单元来进行调用。

UDF和存储过程的主要区别在于返回结果的方式：

• 使用UDF时可传入参数，但不可传出参数。输出参数的概念被更为健壮的返回值取代了。
• 和系统函数一样，可以返回标量值，这个值的好处是它并不像在存储过程中那样只限于整形数据类型，而是可以返回大多数SQL Server数据类型。

UDF有以下两种类型:

• 返回标量值的UDF。
• 返回表的UDF。

 

创建语法：

CREATE FUNCTION [<schema name>.]<function name>
(
[ <@parameter name> [AS] [<schema name>.]<data type> [= <default value> [READONLY]] [,...n] ]
)
RETURNS { <scalar type> | TABLE [(<table definition>)] }
[ WITH [ENCRYPTION] | [SCHEMABINDING] | [RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] |
[EXECUTE AS {CALLER|SELF|OWNER|<'user name'>}]
[AS] { EXTERNAL NAME <externam method> |
BEGIN
[<function statements>]
{RETURN <type as defined in RETURNS clause | RETURN (<SELECT statement>)}
END}[;]

二、标量值函数：

这种类型的UDF和大多数SQL Server内置函数一样，会向调用脚本或存储过程返回标量值，像GETDATE()或USER()函数就会返回标量值。

UDF的返回值并不限于整数，而是可以返回除了BLOB、游标(cursor)和时间戳以外的任何有效的SQL Server数据类型(包括用户自定义类型)。

与存储过程不同，用户自定义函数返回值的目的是提供有意义的数据（而对于存储过程来说，返回值只能说明成功或失败，如果失败，则会提供一些关于失败性质的特定信息。）

可在查询中内联执行函数(如作为SELECT语句的一部分)，而用存储过程则不行。

例1：应用在where语句中

CREATE FUNCTION DateOnly(@Date DateTime)
　　RETURNS varchar(12)
AS
　　BEGIN
    　　RETURN CONVERT(varchar(12),@Date,102)
　　END

然后试着，运用一下：

SELECT * FROM Nx_comment 
　　WHERE dbo.DateOnly(com_posttime) = '2012.04.28'　　--注意前面的dbo是必须的。

其实以上SQL语句相当于：

SELECT * FROM Nx_comment 
　　WHERE CONVERT(varchar(12),com_posttime,102) = '2012.04.28'

例2：应用在select语句中

SELECT Name,Age,
    　　(SELECT AVG(Age) FROM Person) AS AvgAge,
     　　Age - (SELECT AVG(Age) FROM Person) AS Difference 
　　FROM Person

这里要说明一下，列的意思分别是，姓名，年龄，平均年龄以及与平均年龄的差值。

下面我们用UDF来实现，先定义两个UDF如下：

CREATE FUNCTION dbo.AvgAge()
　　RETURNS int
AS
　　BEGIN
    　　RETURN (SELECT AVG(Age) FROM Person)
　　END
GO

CREATE FUNCTION dbo.AgeDifference(@Age int)
　　RETURNS int
AS
　　BEGIN
    　　RETURN @Age - dbo.AvgAge();        --在一个UDF内引用另外一个UDF，好华丽的说
　　END

然后执行查询：

SELECT Name,Age,dbo.AvgAge() AS AvgAge,dbo.AgeDifference(Age) as Difference 
　　FROM Person

三、内联表值函数

SQL Server中的用户自定义函数并不只限于返回标量值，也可以返回表。返回的表在很大程度上和其他表是一样的。

可以对返回 表的UDF执行JOIN，甚至对结果应用WHERE条件。

改为用表作为返回值并不难，对于UDF来说，表就像任何其他SQL Server数据类型一样。

例1：像表一样地用UDF

CREATE FUNCTION dbo.fnContactName()
　　RETURNS TABLE
AS
　　RETURN (
        　　SELECT Id,LastName + ',' + FirstName AS Name 　FROM Man
        　 )

然后我们就可以像表一样地用UDF了。

SELECT * FROM dbo.fnContactName()

例2：带参数返回表

CREATE FUNCTION dbo.fnNameLike(@LName varchar(20))
　　RETURNS TABLE
　　AS
　　RETURN (
        　　SELECT Id,LastName + ',' + FirstName AS Name FROM Man　WHERE LastName Like @LName + '%'
        　　)

然后查询的时候可以这样用：

SELECT * FROM dbo.fnNameLike('刘')

没有WHERE子句，没有过滤SELECT列表，就可以反复使用该函数，而不需要进行"剪切和粘贴"。

四、多语句表值函数

语法:

CREATE FUNCTION Funtion_name
(
    --这里定义传入参数及类型
)
RETURNS
@table_name TABLE
(
    --这里定义@table_name的列名
)
AS
BEGIN
    --这里写sql语句并且将最终需要返回的结果集塞到@table_name 这张表里面
    RETURN 
END
GO

这个函数通过传入一个十进制的数字，分别返回对应的二进制、八进制、十六进制。

Create FUNCTION F_TConversion
(
    @NUM INT
)
RETURNS
@t_table TABLE
(
    [Binary] varchar(64),
    Octal varchar(16),
    Hexadecimal varchar(8)
)
AS
BEGIN
    DECLARE @RESULT2 VARCHAR(500)='',@RESULT8 VARCHAR(500)='',@RESULT16 VARCHAR(500)=''; 
    WITH CTE AS( 
        SELECT @NUM/2 D2,@NUM%2 S2,@NUM/8 D8,@NUM%8 S8,@NUM/16 D16,@NUM%16 S16,1 [INDEX] 
        UNION ALL 
        SELECT D2/2 , D2%2,D8/8 , D8%8,D16/16 , D16%16,[INDEX]+1 FROM CTE WHERE D2>0 
    ) 
    SELECT @RESULT2+=CAST(S2 AS VARCHAR(1))
          ,@RESULT8+=CASE WHEN D8=0 AND S8=0 THEN '' ELSE CAST(S8 AS VARCHAR(1)) END
          ,@RESULT16+=CASE WHEN D16=0 AND S16=0 THEN ''
                           ELSE CASE CAST(S16 AS VARCHAR(5))
                                WHEN '10' THEN 'A' 
                                WHEN '11' THEN 'B' 
                                WHEN '12' THEN 'C' 
                                WHEN '13' THEN 'D' 
                                WHEN '14' THEN 'E' 
                                WHEN '15' THEN 'F' 
                                ELSE CAST(S16 AS VARCHAR(5))
                            END
                        END
    FROM CTE ORDER BY [INDEX] DESC
    INSERT INTO @t_table
    SELECT @RESULT2,@RESULT8,@RESULT16
    RETURN 
END
GO

五、理解确定性

用户自定义函数可以是确定性的也可以是非确定性的。如果给定了一组特定的有效输入，每次函数就都能返回相同的结果，那么就说该函数是确定性的。

SUM()就是一个确定性的内置函数。3、5、10的总合永远都是18，而GETDATE()的值就是非确定性的，因为每次调用它的时候GETDATE()都会改变。

如果视图或计算列引用非确定性函数，则在该视图或列上将不允许建立任何索引。

如果判定函数是否是确定性的？除了上面描述的规则外，这些信息存储在对象的IsDeterministic属性中，可以利用OBJECTPROPERTY属性检查。

SELECT OBJECTPROPERTY(OBJECT_ID('DateOnly'),'IsDeterministic');　　--只是刚才的那个自定义函数

输出结果如下：

居然是非确定性的。原因在于之前在定义该函数的时候，并没有加上这个"WITH SCHEMABINDING"。

ALTER FUNCTION dbo.DateOnly(@Date date)
　　RETURNS date
　　WITH SCHEMABINDING　　--当我们加上这一句之后
　　AS
　　BEGIN
　　　　RETURN @Date
　　END

在执行查询，该函数就是确定性的了。