6.3 表值UDF

表值UDF是一个可以返回表的UDF，它一般用于外部查询的FROM子句。这一节将描述内联表值UDF（inline table-valued UDF）、多语句表值UDF（multistatement table-valued UDF）和CLR表值UDF。

内联表值UDF

内联表值UDF和视图返回的表都是由一个查询来定义的，从这个意义来讲，它们很相似。然而，UDF的查询可以有输入参数，而视图没有。所以，你可以把内联UDF看作是参数化的视图。实际上，SQL Server对内联UDF的处理与视图非常相似。查询处理器（query processor）用内联UDF的定义替换其引用。换句话说，查询处理器展开UDF的定义并生成一个访问基表的执行计划。

不同于标量UDF和多语句表值UDF，不能在内联UDF主体内使用BEGIN/END块。只能指定一个RETURN 子句和一个查询。在函数头，只需声明它返回一个表即可。下面的示例代码在Northwind中创建fn_GetCustOrders函数，该函数接收一个消费者ID作为输入，并返回输入消费者的订单。

SET NOCOUNT ON;

USE Northwind;

IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL

DROP FUNCTION dbo.fn_GetCustOrders;

CREATE FUNCTION dbo.fn_GetCustOrders

(@cid AS NCHAR(5)) RETURNS TABLE

RETURN

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,

ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,

ShipRegion, ShipPostalCode, ShipCountry

FROM dbo.Orders

WHERE CustomerID = @cid;

运行下面的查询以匹配消费者ALFKI的订单和订单明细，生成的输出如表6-4所示：

SELECT O.OrderID, O.CustomerID, OD.ProductID, OD.Quantity

FROM dbo.fn_GetCustOrders(N'ALFKI') AS O

JOIN [Order Details] AS OD

ON O.OrderID = OD.OrderID;

表6-4 消费者ALFKI的订单和订单明细

OrderID	CustomerID	ProductID	Quantity
10643	ALFKI	28	15
10643	ALFKI	39	21
10643	ALFKI	46	2
10692	ALFKI	63	20
10702	ALFKI	3	6
10702	ALFKI	76	15
10835	ALFKI	59	15
10835	ALFKI	77	2
10952	ALFKI	6	16
10952	ALFKI	28	2
11011	ALFKI	58	40
11011	ALFKI	71	20

与视图一样，内联UDF也可以作为修改语句的目标。你可以为用户授予该函数的任何DML权限。当然，最终修改的还是基表。例如，下面的代码把ALFKI的所有订单中的ShipVia（供应商ID）设为2，并在更新前后显示订单的状态。

BEGIN TRAN

SELECT OrderID, ShipVia FROM fn_GetCustOrders(N'ALFKI') AS O;

UPDATE fn_GetCustOrders(N'ALFKI') SET ShipVia = 2;

SELECT OrderID, ShipVia FROM fn_GetCustOrders(N'ALFKI') AS O;

ROLLBACK

代码在事务被调用，然后回滚事务，这样做只是为了演示，以避免在Northwind示例数据库应用永久性更改。表6-5和6-6分别展示了在更新前和更新后ALFKI的订单状态。

表6-5 更新之前消费者 ALFKI的订单

OrderID	ShipVia
10643	1
10692	2
10702	1
10835	3
10952	1
11011	1

表6-6 更新之后消费者ALFKI的订单

OrderID	ShipVia
10643	2
10692	2
10702	2
10835	2
10952	2
11011	2

同样，如果有权限的话，你也可以通过该函数删除数据。例如，下面的代码（不要运行它）将删除ALFKI在1997年的订单。

DELETE FROM fn_GetCustOrders(N'ALFKI') WHERE YEAR(OrderDate) = 1997;

不要运行这行代码，因为它会因违反外键约束而失败。我只是给你提供一个代码示例而已。

完成后，运行下面的代码进行清理。

IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL

DROP FUNCTION dbo.fn_GetCustOrders;

拆分数组(Split Array)

这一节将提供一个用于拆分数组的函数的T-SQL实现和CLR实现，该函数接收一个包含元素数组（array of element）的字符串作为输入并返回一个表，表中的每一行表示一个元素。

T-SQL拆分UDF

运行下面的代码创建内联表值函数fn_SplitTSQL。

USE CLRUtilities;

IF OBJECT_ID('dbo.fn_SplitTSQL') IS NOT NULL

DROP FUNCTION dbo.fn_SplitTSQL;

CREATE FUNCTION dbo.fn_SplitTSQL

(@string NVARCHAR(MAX), @separator NCHAR(1) = N',') RETURNS TABLE

RETURN

SELECT

n - LEN(REPLACE(LEFT(s, n), @separator, '')) + 1 AS pos,

SUBSTRING(s, n,

CHARINDEX(@separator, s + @separator, n) - n) AS element

FROM (SELECT @string AS s) AS D

JOIN dbo.Nums

ON n <= LEN(s)

AND SUBSTRING(@separator + s, n, 1) = @separator;

这个函数接收两个输入参数：@string和 @separator。@string参数保存输入的数组，@separator中的字符用于拆分数组中的各个元素。函数查询一个名为D的派生表（derived table），这个表只有一行和一列（array，表示输入的数组）。函数联接D和辅助表Nums，生成与元素数量一样多的数组副本。联接在 @separator + array中查找与每个 @separator值的匹配。也就是说，为每个元素复制一次数组，Nums中的n表示一个元素的开始位置。

SELECT列表包含一个表达式，它调用SUBSTRING函数以提取从第n个字符到数组的下一个 @separator之间的元素。SELECT列表还包含另外一个表达式，该表达式使用我在本章前面描述过的方法统计子字符串在一个字符串中出现的次数。在这个示例中，该方法用于统计数组的前n个字符中出现 @separator的次数。该次数加1表示当前元素在数组中的位置。

要测试fn_SplitTSQL函数，运行下面的代码，将生成表6-7所示的输出。

SELECT pos, element FROM dbo.fn_SplitTSQL(N'a,b,c', N',') AS F;

表6-7 把数组拆分成单个元素

pos	element
1	a
2	b
3	c

你可以用各种有意思的方式使用这个函数。例如，假设一个客户端应用程序需要向SQL Server发送一个以逗号分隔的订单ID列表，并返回相应的订单。一般来讲，开发人员会在存储过程中使用动态执行实现这样的逻辑。我在第4章讨论过这种方法在安全和性能方面的缺陷。利用这个新函数，你可以使用静态查询满足这种需要，并且可以通用缓存的执行计划。

DECLARE @arr AS NVARCHAR(MAX);

SET @arr = N'10248,10249,10250';

SELECT O.OrderID, O.CustomerID, O.EmployeeID, O.OrderDate

FROM dbo.fn_SplitTSQL(@arr, N',') AS F

JOIN Northwind.dbo.Orders AS O

ON CAST(F.element AS INT) = O.OrderID;

该查询生成的输出如表6-8所示。

表6-8 联接fn_SplitTSQL UDF 和 Orders 表产生的输出

OrderID	CustomerID	EmployeeID	OrderDate
10248	VINET	5	1996-07-04 00:00:00.000
10249	TOMSP	6	1996-07-05 00:00:00.000
10250	HANAR	4	1996-07-08 00:00:00.000

CLR 拆分 UDF

尽管这个拆分函数的CLR实使用了两种方法，但它很简单下面是fn_SplitCLR函数的C# 定义。

// 在字符串拆分函数中使用的结构

struct row_item

{

public string item;

public int pos;

}

// 拆分字符串数组并返回表

// FillRowMethodName = "ArrSplitFillRow"

[SqlFunction(FillRowMethodName = "ArrSplitFillRow",

DataAccess = DataAccessKind.None,

TableDefinition = "pos INT, element NVARCHAR(4000) ")]

public static IEnumerable fn_SplitCLR(SqlString inpStr,

SqlString charSeparator)

{

string locStr;

string[] splitStr;

char[] locSeparator = new char[1];

locSeparator[0] = (char)charSeparator.Value[0];

if (inpStr.IsNull)

locStr = "";

else

locStr = inpStr.Value;

splitStr = locStr.Split(locSeparator,

StringSplitOptions.RemoveEmptyEntries);

//locStr.Split(charSeparator.ToString()[0]);

List<row_item> SplitString = new List<row_item>();

int i = 1;

foreach (string s in splitStr)

{

row_item r = new row_item();

r.item = s;

r.pos = i;

SplitString.Add(r);

++i;

}

return SplitString;

}

public static void ArrSplitFillRow(

Object obj, out int pos, out string item)

{

pos = ((row_item)obj).pos;

item = ((row_item)obj).item;

}

函数头把FillRowMethodName属性设置为“ArrSplitFillRow”。ArrSplitFillRow是一个方法（在fn_SplitCLR函数后面定义），它把输入的对象转换为字符串。函数头还用TableDefinition属性定义了输出表的架构。只在使用Visual Studio自动的部署该函数时才需要该属性。如果你使用T-SQL手工部署这个函数，不需要指定这个属性。

函数调用string类型内置的Split方法拆分输入的数组（先把输入的数组从 .NET SQL SqlString类型转换为 .NET string本机类型）。它使用StringSplitOptions.RemoveEmptyEntries选项，因此返回值不包含内容为空字符串的数组元素。

下面是Visual Basic版的fn_SplitCLR函数。

在字符串拆分。

Structure row_item

Dim item As String

Dim pos As Integer

End Structure

'拆分字符串数组并返回一个表

' FillRowMethodName = "ArrSplitFillRow"

<SqlFunction(FillRowMethodName:="ArrSplitFillRow", _

DataAccess:=DataAccessKind.None, _

TableDefinition:="pos INT, element NVARCHAR(4000) ")> _

Public Shared Function fn_SplitCLR(ByVal inpStr As SqlString, _

ByVal charSeparator As SqlString) As IEnumerable

Dim locStr As String

Dim splitStr() As String

Dim locSeparator(0) As Char

locSeparator(0) = CChar(charSeparator.Value(0))

If (inpStr.IsNull) Then

locStr = ""

Else

locStr = inpStr.Value

End If

splitStr = locStr.Split(locSeparator, _

StringSplitOptions.RemoveEmptyEntries)

Dim SplitString As New List(Of row_item)

Dim i As Integer = 1

For Each s As String In splitStr

Dim r As New row_item

r.item = s

r.pos = i

SplitString.Add(r)

i=i+1

Return SplitString

End Function

Public Shared Sub ArrSplitFillRow( _

ByVal obj As Object, <Out()> ByRef pos As Integer, _

<Out()> ByRef item As String)

pos = CType(obj, row_item).pos

item = CType(obj, row_item).item

End Sub

使用下面的代码在数据库中注册该函数的C# 版。

IF OBJECT_ID('dbo.fn_SplitCLR') IS NOT NULL

DROP FUNCTION dbo.fn_SplitCLR;

CREATE FUNCTION dbo.fn_SplitCLR

(@string AS NVARCHAR(4000), @separator AS NCHAR(1))

RETURNS TABLE(pos INT, element NVARCHAR(4000))

EXTERNAL NAME CLRUtilities.CLRUtilities.fn_SplitCLR;

使用下面的代码注册该函数的Visual Basic版。

CREATE FUNCTION dbo.fn_SplitCLR

(@string AS NVARCHAR(4000), @separator AS NCHAR(1))

RETURNS TABLE(pos INT, element NVARCHAR(4000))

EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].fn_SplitCLR;

运行下面的代码测试fn_SplitCLR函数，生成的结果显示在前面的表6-7中。

SELECT pos, element FROM dbo.fn_SplitCLR(N'a,b,c', N',');

要使用数组表测试该函数，运行下面的代码，创建Arrays表并用一些示例数组填充该表。

IF OBJECT_ID('dbo.Arrays') IS NOT NULL

DROP TABLE dbo.Arrays;

CREATE TABLE dbo.Arrays

(

arrid INT NOT NULL IDENTITY PRIMARY KEY,

arr NVARCHAR(4000) NOT NULL

);

INSERT INTO dbo.Arrays(arr) VALUES(N'20,220,25,2115,14');

INSERT INTO dbo.Arrays(arr) VALUES(N'30,330,28');

INSERT INTO dbo.Arrays(arr) VALUES(N'12,10,8,8,122,13,2,14,10,9');

INSERT INTO dbo.Arrays(arr) VALUES(N'-4,-6,1050,-2');

使用下面的查询为Arrays表中的每个数组调用该函数，产生的结果如表6-9所示。

SELECT arrid, pos, element

FROM dbo.Arrays AS A

CROSS APPLY dbo.fn_SplitCLR(arr, N',') AS F;

表6-9 把Arrays表中的字符串拆分成基本元素

arrid	pos	element
1	1	20
1	2	220
1	3	25
1	4	2115
1	5	14
2	1	30
2	2	330
2	3	28
3	1	12
3	2	10
3	3	8
3	4	8
3	5	122
3	6	13
3	7	2
3	8	14

续表

arrid	pos	element
3	9	10
3	10	9
4	1	-4
4	2	-6
4	3	1050
4	4	-2

比较T-SQL 和CLR拆分的性能

要比较T-SQL和CLR拆分技术的性能，运行下面的代码并复制100 000次Arrays表当前的内容。

INSERT INTO dbo.Arrays

SELECT arr

FROM dbo.Arrays, dbo.Nums

WHERE n <= 100000;

现在Arrays表包含400,004行。

使用下面的查询（启用执行后放弃结果）应用这个T-SQL分离方法。

SELECT

n - LEN(REPLACE(LEFT(arr, n), ',', '')) + 1 AS pos,

SUBSTRING(arr, n, CHARINDEX(',', arr + ',', n) - n) AS element

FROM Arrays

JOIN dbo.Nums

ON n <= LEN(arr)

AND SUBSTRING(',' + arr, n, 1) = ',';

注意，我没有使用fn_SplitTSQL UDF，因为你可以对Arrays表直接应用相同的方法。上面的代码在我的系统上运行了17秒。

而使用CLR的版本，它只运行了8秒钟——比T-SQL版本快一倍。

完成后，运行下面的代码进行清理。

IF OBJECT_ID('dbo.Arrays') IS NOT NULL

DROP TABLE dbo.Arrays;

IF OBJECT_ID('dbo.fn_SplitTSQL') IS NOT NULL

DROP FUNCTION dbo.fn_SplitTSQL;

IF OBJECT_ID('dbo.fn_SplitCLR') IS NOT NULL

DROP FUNCTION dbo.fn_SplitCLR;

多语句表值UDF

多语句表值UDF是一种返回表变量的函数。它包含一个用于填充这个表变量的函数体。当需要一段程序以返回表，而且用单个查询无法实现该程序，只能使用多条语句，这时你可以创建多语句表值UDF。例如，像循环这样的流元素（flow element），等等。

多语句表值UDF与内联表值UDF的使用方式类似，但它不能作为修改语句的目标。也就是说，它只能用于SELECT查询的FROM子句。在内部，SQL Server对这两种函数的处理完全不同。它对待内联UDF的处理更像是视图，而对多语句表值UDF的处理更像是存储过程。与其他UDF一样，多语句表值UDF不允许产生副作用。

作为一个多语句表值UDF的示例，你要创建一个函数，它接收一个员工ID作为输入并返回该员工的详细信息和他的所有下属。运行代码清单6-5中的代码以创建Employees表并用一些示例数据填充该表。

代码清单6-5 Employees表的数据定义语言(DDL)和一些示例数据

SET NOCOUNT ON;

USE tempdb;

IF OBJECT_ID('dbo.Employees') IS NOT NULL

DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees

(

empid INT NOT NULL PRIMARY KEY,

mgrid INT NULL REFERENCES dbo.Employees,

empname VARCHAR(25) NOT NULL,

salary MONEY NOT NULL

);