SQL Server怎么创建用户定义函数
本文小编为大家详细介绍"SQL Server怎么创建用户定义函数"，内容详细，步骤清晰，细节处理妥当，希望这篇"SQL Server怎么创建用户定义函数"文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。
一、UDF的定义
和存储过程很相似，用户自定义函数也是一组有序的T-SQL语句，UDF被预先优化和编译并且可以作为一个单元来进行调用。
UDF和存储过程的主要区别在于返回结果的方式：
使用UDF时可传入参数，但不可传出参数。输出参数的概念被更为健壮的返回值取代了。
和系统函数一样，可以返回标量值，这个值的好处是它并不像在存储过程中那样只限于整形数据类型，而是可以返回大多数SQL Server数据类型。
UDF有以下两种类型:
返回标量值的UDF。
返回表的UDF。
创建语法：
CREATE FUNCTION [.] ([ <@parameter name> [AS] [ .] [= [READONLY]] [,...n] ])RETURNS { | TABLE [( )] }[ WITH [ENCRYPTION] | [SCHEMABINDING] | [RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] |[EXECUTE AS {CALLER|SELF|OWNER|<'user name'>}][AS] { EXTERNAL NAME
|BEGIN[ ]{RETURN )}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 intAS BEGIN RETURN (SELECT AVG(Age) FROM Person) ENDGOCREATE FUNCTION dbo.AgeDifference(@Age int) RETURNS intAS 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 TABLEAS 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的列名)ASBEGIN --这里写sql语句并且将最终需要返回的结果集塞到@table_name 这张表里面 RETURN ENDGO这个函数通过传入一个十进制的数字，分别返回对应的二进制、八进制、十六进制。
Create FUNCTION F_TConversion( @NUM INT)RETURNS@t_table TABLE( [Binary] varchar(64), Octal varchar(16), Hexadecimal varchar(8))ASBEGIN 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 S16,1 [INDEX] UNION ALL SELECT D2/2 , D2%2,D8/8 , D8%8,D16/16 , D16,[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 ENDGO
五、理解确定性
用户自定义函数可以是确定性的也可以是非确定性的。如果给定了一组特定的有效输入，每次函数就都能返回相同的结果，那么就说该函数是确定性的。
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在执行查询，该函数就是确定性的了。
读到这里，这篇"SQL Server怎么创建用户定义函数"文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注行业资讯频道。
函数 确定性 用户 类型 存储 语句 过程 量值 参数 数据 结果 查询 就是 年龄 文章 时候 应用 有效 信息 内容 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 网络安全审查方法有哪些 手机游戏显示未连接到服务器 iphone11网络安全过低 美国第五空间网络安全评估 csgo如何重置服务器命令 西安法律咨询服务器 数据库主键的作用和特征 unity 服务器时间 网络安全普法竞赛 action对魔忍服务器错误 公务用车风险数据库 魔域物品数据库怎么设置叠加 做好网络安全的建议 1000万的软件开发项目 软件开发部工作标准 长沙营销软件开发公司 网络安全的手抄报素描纸画 麦积区法院开展网络安全宣传 海康威视的服务器怎么装 计算机网络安全知识培训 网络安全攻防大赛致辞 电脑双系统会有网络安全风险吗 如何判断服务器可以连接 数据库安全注意事项 现在汽车网络技术有哪些 违反国铁集团网络安全红线后果 深圳市服务器搬迁公司价格 主从服务器 英文 快期 服务器 期货公司 软件开发企业评估案例
相关文章
