SQL Server中怎样创建函数

发布时间：2023-12-30 13:17:06 所属栏目：MsSql教程来源：DaWei

导读： 这篇文章主要介绍“SQL Server中怎样创建函数，有哪些方法”的相关知识，下面会通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“SQL Server中怎样创

这篇文章主要介绍“SQL Server中怎样创建函数，有哪些方法”的相关知识，下面会通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“SQL Server中怎样创建函数，有哪些方法”文章能帮助大家解决问题。

一、UDF的定义
和存储过程很相似，用户自定义函数也是一组有序的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'

三、内联表值函数
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列表，就可以反复使用该函数，而不需要进行"剪切和粘贴"。

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

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中怎样创建函数，有哪些方法”的介绍了，感谢各位的阅读，如果大家想要了解更多相关的内容，小编每天都会为大家更新不同的知识。

（编辑：财气旺网 - 财气网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!