SQL语法之自定义函数UDF

UDF有以下两种类型:

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

返回标量值的UDF

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

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

　　与存储过程不同，用户自定义函数返回值的目的是提供有意义的数据；而对于存储过程来说，返回值只能说明成功或失败，如果失败，则会提供一些关于失败性质的特定信息。
可在查询中内联执行函数(如作为SELECT语句的一部分)，而是用存储过程则不行。

　　下面创建一个UDF如下：

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

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

　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'

再来看一个简单的查询：

　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 

返回表的UDF

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

SELECT * FROM dbo.fnContactName()

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('刘')