SQL基础：SQL Server创建用户定义函数
一、UDF的定义
和存储过程很相似，用户自定义函数也是一组有序的T-SQL语句，UDF被预先优化和编译并且可以作为一个单元来进行调用。
UDF和存储过程的主要区别在于返回结果的方式：
- 使用UDF时可传入参数，但不可传出参数。输出参数的概念被更为健壮的返回值取代了。
- 和系统函数一样，可以返回标量值，这个值的好处是它并不像在存储过程中那样只限于整形数据类型，而是可以返回大多数SQL Server数据类型。
UDF有以下两种类型:
- 返回标量值的UDF。
- 返回表的UDF。
创建语法：
(
[ <@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语句中
RETURNS varchar(12)
AS
BEGIN
RETURN CONVERT(varchar(12),@Date,102)
END
然后试着，运用一下：
WHERE dbo.DateOnly(com_posttime) = ‘2012.04.28’ –注意前面的dbo是必须的。
其实以上SQL语句相当于：
WHERE CONVERT(varchar(12),com_posttime,102) = ‘2012.04.28’
例2：应用在select语句中
(SELECT AVG(Age) FROM Person) AS AvgAge,
Age – (SELECT AVG(Age) FROM Person) AS Difference
FROM Person
这里要说明一下，列的意思分别是，姓名，年龄，平均年龄以及与平均年龄的差值。
下面我们用UDF来实现，先定义两个UDF如下：
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
然后执行查询：
FROM Person
三、内联表值函数
SQL Server中的用户自定义函数并不只限于返回标量值，也可以返回表。返回的表在很大程度上和其他表是一样的。
可以对返回 表的UDF执行JOIN，甚至对结果应用WHERE条件。
改为用表作为返回值并不难，对于UDF来说，表就像任何其他SQL Server数据类型一样。
例1：像表一样地用UDF
RETURNS TABLE
AS
RETURN (
SELECT Id,LastName + ‘,’ + FirstName AS Name FROM Man
)
然后我们就可以像表一样地用UDF了。
例2：带参数返回表
RETURNS TABLE
AS
RETURN (
SELECT Id,LastName + ‘,’ + FirstName AS Name FROM Man WHERE LastName Like @LName + ‘%’
)
然后查询的时候可以这样用：
没有WHERE子句，没有过滤SELECT列表，就可以反复使用该函数，而不需要进行"剪切和粘贴"。
四、多语句表值函数
语法:
(
–这里定义传入参数及类型
)
RETURNS
@table_name TABLE
(
–这里定义@table_name的列名
)
AS
BEGIN
–这里写sql语句并且将最终需要返回的结果集塞到@table_name 这张表里面
RETURN
END
GO
这个函数通过传入一个十进制的数字，分别返回对应的二进制、八进制、十六进制。
(
@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属性检查。
输出结果如下：
居然是非确定性的。原因在于之前在定义该函数的时候，并没有加上这个"WITH SCHEMABINDING"。
RETURNS date
WITH SCHEMABINDING –当我们加上这一句之后
AS
BEGIN
RETURN @Date
END
在执行查询，该函数就是确定性的了。
到此这篇关于SQL Server用户自定义函数的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。
