查看 sql server自定义函数 sqlserver自定义函数教程

（《SQL Server 2005 编程入门经典》 第13章）

用户自定义函数和存储过程非常相似，但它们也有一些行为和能力的区别。

13.1 UDF的定义

用户自定义函数是有序的T-SQL语句集合，该语句集合能够预先优化和编译，并且可以作为一个单元来调用。它和存储过程的主要区别在于返回结果的方式。为了能支持多种不同的返回值，UDF比存储过程有更多地限制。

可以在使用存储过程的时候传入参数，也可以以参数的形式得到返回值。存储过程可以返回值，不过该值是为了指示成功或失败的，而非返回数据。

然而，可以在使用UDF的时候传入参数，但是可以不传出任何值。UDF还可以返回标量（scalar）值，这个值可以是大部分SQL Server的数据类型。UDF还可以返回表。

按照返回值的类型，UDF有两种类型：

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

创建UDF的基本语法：

CREATE FUNCTION [<schema name>.]<function name> 
    ([<@parameer name> [AS] [<schema name>.]<scalar data type> 
        [= <default value>] 
    [, ...n]]) 
RETURNS {<scalar type>|TABLE [(<table definition>)]} 
    [WITH [ENCRYPTION]|[SCHEMABINDING]| 
    [RETURNS NOLL ON NULL INPUT|CALLED ON NULL INPUT]| 
    [EXECUTE AS {CALLER|SELF|OWNER|<'user name'>}]] 
[AS] {EXTERNAL NAME <external method name> 
BEGIN 
    [<function statements>] 
    {RETURN <type as defined in RESURNS clause>|RETURN 
    (<select statement>)} 
END }[;]

这里一个很大的问题在于是返回一个标量数据类型还是表，是使用基于T-SQL的函数还是使用CLR和.NET。

13.2 返回标量值得UDF

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

UDF可以返回除了BLOB、CURSOR和TIMESTAMP以外的任何SQL Server中有效的数据类型（包含用户自定义类型）。如果想返回整数，UDF也和存储过程不同的是：

• UDF返回值的目的是提供有意义的数据，而不是说明成功或失败。
• 在查询中可以内联地执行函数，而使用存储过程则不行。

示例——返回去掉时分秒的日期：

CREATE FUNCTION DayOnly(@Date DATETIME) 
RETURNS VARCHAR(12) 
AS 
BEGIN 
    RETURN CONVERT(VARCHAR(12), @Date, 101) 
END 
函数的使用方法如下：
SELECT * 
FROM Orders 
WHERE DayOnly(OrderDate) = DayOnly(GETDATE()) 
在一个UDF中调用另一个UDF：
CREATE FUNCTION AveragePrice() 
RETURNS MONEY 
WITH SCHEMABINDING 
AS 
BEGIN 
    RETURN (SELECT AVG(Price) FROM Titles) 
END 
GO 
 
CREATE FUNCTION PriceDifference(@Price MONEY) 
RETURN MONEY 
AS 
BEGIN 
    RETURN @Price – AveragePrice() 
END 
使用UDF可以大大增加查询语句的可读性，并实现了代码重用：
USE pubs 
SELECT Title, 
    Price, 
    AveragePrice() AS Average, 
    PriceDifference(Price) AS Difference 
FROM Titles 
WHERE Type = 'popular_comp'

13.3 返回表的UDF

可以对UDF返回的表执行JOIN，甚至对结果应用WHERE条件。相对简单的函数示例如下：

USE pubs 
GO 
 
CREATE FUNCTION fnAuthorList() 
RETURN TABLE 
AS 
RETURN ( 
SELECT au_id, 
    au_lname + ', ' + au_fname AS au_name 
    address AS address1, 
    city + ', ' + state + ', ' + zip AS address2 
FROM authors 
) 
GO

这样的话，使用这个函数就像使用表一样：

SELECT * 
FROM fnAuthorList()

使用返回表的UDF比使用视图的好处在于可以在UDF中将条件参数化，而视图不得不包含不想要的数据，然后再通过WHERE子句过滤。例如：

CREATE VIEW vSalesCount 
AS 
SELECT au.au_id, 
    au.aulname + ', ' + au.au_fname AS au_name, 
    au.address AS address1, 
    city + ', ' + state + ', ' + zip AS address2, 
    SUM(s.qty) AS SalesCount 
FROM authors au 
INNER JOIN titleauthor ta 
    ON au.au_id = ta.au_id 
INNER JOIN sales s 
    ON ta.title_id = s.title_id 
GROUP BY au.au_id, 
    au.au_lname + ', ' + au.au_fname, 
    au.address, 
    au.city + ', ' + au.state + ', ' + zip 
GO 
 
SELECT au_name, address1, address2 
FROM vSalesCount 
WHERE SalesCount > 25 
为了使得代码更简单，把所有的代码都封装到一个函数中：
USE pubs 
GO 
 
CREATE FUNCTION fnSalesCount(@SalesQty BIGINT) 
RETURNS TABLE 
AS 
RETURN ( 
SELECT au.au_id, 
    au.aulname + ', ' + au.au_fname AS au_name, 
    au.address AS address1, 
    city + ', ' + state + ', ' + zip AS address2, 
    SUM(s.qty) AS SalesCount 
FROM authors au 
INNER JOIN titleauthor ta 
    ON au.au_id = ta.au_id 
INNER JOIN sales s 
    ON ta.title_id = s.title_id 
GROUP BY au.au_id, 
    au.au_lname + ', ' + au.au_fname, 
    au.address, 
    au.city + ', ' + au.state + ', ' + zip 
HAVING SUM(qty) > @SalesQty 
) 
GO 
为了执行该函数，只需要调用它并提供参数：
SELECT * 
FROM fnSalesCount(25) 
再进一步，如果需要查询每一个销售超过25本书以上的作者和出版社的信息，这需要连接UDF返回的表：
SELECT DISTINCT p.pub_name, a.au_name 
FROM dbo.fnSalesCount(25) AS a 
INNER JOIN titleauthor AS ta 
    ON a.au_id = ta.au_id 
INNER JOIN titles AS t 
    ON ta.title_id = t.title_id 
INNER JOIN publishers AS p 
    ON t.pub_id = p.pub_id 
这里对函数进行了连接，就好像它是表或视图一样。唯一的区别在于可以对它进行参数化。
再进一步，UDF也可以递归调用，并同样存在最深32层的限制。例如，现在需要查询向Andrew Fuller报告的报告链的所有人员：
CREATE FUNCTION dbo.fnGetReports(@EmployeeID AS INT) 
RETURNS @Reports TABLE ( 
    EmployeeID INT NOT NULL, 
    ReportsToID INT NULL 
) 
AS 
BEGIN 
    DECLARE @Employee AS INT 
     
    INERT INTO @Reports 
    SELECT EmployeeID, ReportsTo 
    FROM Employees 
    WHERE EmployeeID = @EmployeeID 
     
    SELECT @Employee = MIN(EmployeeID) 
    FROM Employees 
    WHERE ReportsTo = @EmployeeID 
     
    WHILE @Employee IS NOT NULL 
    BEGIN 
        INSERT INTO @Reports 
        SELECT * 
        FROM fnGetReports(@Employee) 
         
        SELECT @Employee = MIN(EmployeeID) 
        FROM Employees 
        WHERE EmployeeID > @Employee AND ReportsTo = @EmployeeID 
    END 
     
    RETURN 
END 
GO 
现在如果需要查询员工ID为2的员工的报告链：
SELECT * FROM fnGetReports(2)

理解决定性

如果SQL Server需要建立一个索引的话，则它必须能决定性地定义（明确定义）对哪个项目进行索引。用户自定义函数可以是决定性的也可以是非决定性的。决定性并不是根据任何参数类型来定义的，而是根据函数的功能来定义的。如果给定了一个特有的有效输入集的话，那么每次函数就都能返回相同的结果，我们就说该函数是决定性的。内建函数是决定性的一个示例是SUM()。3、5、10的总和永远是18。但是GETDATE()的值就是非决定性的，因为每次调用它的时候它都会改变。

为了达到决定性的要求，函数必须满足4个标准：

• 函数必须是限于模式的。这意味着函数所依赖的任何对象会有一个依赖记录，并且在没有删除这个依赖的函数之前都不允许改变这些对象。
• 函数引用到的所有其他函数，都必须是决定性的。
• 不能引用定义在函数本身之外的表（可以使用表变量和临时表）。
• 不能在函数内部使用扩展的存储过程。

决定性的重要性在于它揭示了是否要在视图或计算列上建立索引。这意味着，如果视图或计算列引用非决定性函数的话，则在该视图或列上将不允许建立任何索引。

可以利用OBJECTPROPERTY函数检查一个函数是不是决定性的——它检验存储在对象的IsDeterministic属性来得到结论。例如，我们可以检验DayOnly函数的决定性：

SELECT OBJECTPROPERTY(OBJECT_ID('DayOnly'), 'IsDeterministic') 
给DayOnly()函数添加上WITH SCHEMABINDING选项后，该函数将是决定性的：
ALTER FUNCTION DayOnly(@Date DATETIME) 
RETURNS VARCHAR(12) 
WITH SCHEMABINDING 
AS 
BEGIN 
    RETURN CONVERT(VARCHAR(12), @Date, 101) 
END