SQL Server UDF用户自定义函数

meikkiie

　　UDF的定义
　　和存储过程很相似，用户自定义函数也是一组有序的T-SQL语句，UDF被预先优化和编译并且尅作为一个单元爱进行调用。UDF和存储过程的主要区别在于返回结果的方式。
　　使用UDF时可传入参数，但不可传出参数。输出参数的概念被更为健壮的返回值取代了。和系统函数一样，可以返回标量值，这个值的好处是它并不像在存储过程中那样只限于整形数据类型，而是可以返回大多数SQL Server数据类型。
　　UDF有以下两种类型:

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

　　创建语法：
　　CREATEFUNCTION[.]([  [AS][.][=  [READONLY]] [,...n] ])RETURNS { |TABLE[()] }[ WITH [ENCRYPTION]|[SCHEMABINDING]|[RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]|[EXECUTE AS {CALLER|SELF|OWNER|}][AS] { EXTERNAL NAME |BEGIN[]{RETURN　　然后我们就可以像表一样地用UDF了。
　　SELECT*FROM dbo.fnContactName()
　　输出结果如下：

http://note.youdao.com/yws/res/4188/8DAC7B59C7ED43DABC365774DD266ED8

　　现在再来看看一个简单的用法，定义UDF如下：

　　CREATEFUNCTION dbo.fnNameLike(@LNamevarchar(20))　　RETURNSTABLEASRETURN (        　　SELECT>　　然后查询的时候可以这样用：
　　SELECT*FROM dbo.fnNameLike('刘')
　　显示结果如下：

http://note.youdao.com/yws/res/4189/D975AEAC60AF44C29836A8052A8E244C

　　没有WHERE子句，没有过滤SELECT列表，就可以反复使用该函数，而不需要进行"剪切和粘贴"。而且本例做得不好，其实完全可以先连接一次其他表，然后再查询，这是存储过程所做不到的。
　　四、理解确定性
　　用户自定义函数可以是确定性的也可以是非确定性的。确定性并不是根据任何参数类型定义的，而是根据函数的功能定义的。如果给定了一组特定的有效输入，每次函数就都能返回相同的结果，那么就说该函数是确定性的。SUM()就是一个确定性的内置函数。3、5、10的总合永远都是18，而GETDATE()的值就是非确定性的，因为每次调用它的时候GETDATE()都会改变。
　　为了达到确定性的要求，函数必须满足以下4个条件。

• 　　函数必须是模式绑定的。这意味着函数所依赖的任何对象会有一个依赖记录，并且在没有删除这个依赖的函数之前都不允许改变这些对象。
  
• 　　函数引用的所有其他函数，无论是用户定义的，还是系统定义的，都必须是确定性的。
  
• 　　不能引用在函数外部定义的表(可以使用表变量和临时表，只要它们是在函数作用域内定义就行)。
  
• 　　不能使用扩展存储过程。
  

　　确定性的重要性在于它显示了是否要在视图或计算列上建立索引。如果可以可靠地确定视图或计算列的结果，那么才允许在视图或计算列上建立索引。这意味着，如果视图或计算列引用非确定性函数，则在该视图或列上将不允许建立任何索引。
　　如果判定函数是否是确定性的？除了上面描述的规则外，这些信息存储在对象的IsDeterministic属性中，可以利用OBJECTPROPERTY属性检查。
　　SELECTOBJECTPROPERTY(OBJECT_ID('DateOnly'),'IsDeterministic');　　--只是刚才的那个自定义函数
　　输出结果如下：

http://note.youdao.com/yws/res/4190/09A7C381E9F34C69B4CCB373555EB503

　　居然是非确定性的。原因在于之前在定义该函数的时候，并没有加上这个"WITH SCHEMABINDING"。
　　ALTERFUNCTION dbo.DateOnly(@Date date)　　RETURNS date　　WITH SCHEMABINDING　　--当我们加上这一句之后ASBEGINRETURN@DateEND
　　在执行查询，该函数就是确定性的了。

http://note.youdao.com/yws/res/4191/CB9C16B615F0449C8D87CFAFDA430C21