如何在SQL Server中构建并利用UDF表格

网络整理 - 07-27

在SQL Server 2000和2005中，UDF（用户定义函数）表格是我最喜欢的特性之一。如果你还没有用过它们，可以试一下，它会带给你很多惊喜的。

UDF表格和返回一个记录集的存储过程类似。你可以通过如下简单的示例代码，得到一个两列无行或数行的记录集。

CREATE PROCEDURE simple_ap
( @cityID integer )
AS
      SELECT CustomerID, CompanyName
      FROM Customers
      WHERE Customers.CityID = @cityID

要重复利用这样一个存储过程的结果没有简单的方法。例如，你不能将记录集应用于其他的程序，或者将其添加到一个表格、视图或另一个记录集中。如果可以做到这一点的话，那么你重复利用代码的机会就会大大增加，并且，你还可以据此自己设计代码。

进入UDF表格，这种构造使你能够像处理一个真正的表格或视图那样对待结果集——你可以将它加入到其他的对象中，对它添加一个WHERE字句，甚至更多。

以下的代码中包括了在SQL Server 2000中创建UDF表格的句法：

CREATE FUNCTION [owner].[function_name]
(
      @parm1 <datatpe> = <default>
)
RETURNS TABLE
AS
      RETURN
      (
      SELECT <column1, column2, ...>
      FROM <table, view, etc.>
      WHERE <some condition applies>      -- optional clauses
)

要将以上的存储过程转换成为一个UDF表格，只需用适当的块替换UDF句法中的位置标记就行了，就像下面这样：

CREATE FUNCTION [dbo].[Customers_By_City_Select]    -- denotes table UDF
(
      @city nvarchar(15)
)
RETURNS TABLE
AS
      RETURN
      (
      SELECT CustomerID, CompanyName
      FROM dbo.Customers
      WHERE Customers.City = @city
      )

结果集是相同的，但是它却有一些很好的新优点。首先，利用过程的话，调用方法如下：

EXEC simple_ap 12345

而对UDF的调用是这样的：

SELECT * FROM Customers_By_City_Select ('Berlin')

我们注意到，如果利用UDF的话，我们可以向调用添加DISTINCT命令，用它来减少该示例代码中的结果集：

ALFKIAlfredsFutterkiste
ANATRAna Trujillo Emparedados y helados
ANTONAntonio Moreno Taquería
AROUTAround the Horn

这不仅证明一个表格函数就是一个真实的表格，而且也表明允许这种灵活性的过程将肯定会更加复杂。

乐趣才刚刚开始。我们可以选中函数所返回的列中的任一子集：

SELECT CustomerID FROM Customers_By_City_Select ('Berlin')

表格函数最好的一个方面就是它们的表格性：在将其添加到表格、查看和其他表格函数中时，要引用UDF，把它当作表格来处理：

SELECT Customers_By_City_Select.CustomerID, OrderID
FROM Customers_By_City_Select ('Berlin')
INNER JOIN Orders ON Orders.CustomerID = Customers_By_City_Select.CustomerID

返回以下结果集：

ALFKI10643
ALFKI10692
ALFKI10702
ALFKI10835
ALFKI10952
ALFKI11011

这种能力使得我喜欢称之为的原子查询变得更简易。原子查询从表格（或视图）中返回结果。通过结合几个原子查询，你可以创建一个（包括几个原子查询的）分子查询。UDF表格使你能够达到一个存储过程不可能完成的高细粒性和重复利用性水平。