通过

CLR 表值函数

  • 项目

适用范围：SQL Server

表值函数是返回表的用户定义函数。

SQL Server 允许在任何托管语言中定义表值函数，从而扩展表值函数的功能。 通过 IEnumerable 或 IEnumerator 对象从表值函数返回数据。

对于表值函数，返回表类型的列不能包含时间戳列或非 Unicode 字符串数据类型列（如 char、varchar和 文本）。 不支持 NOT NULL 约束。

Transact-SQL 与 CLR 表值函数之间的差异

Transact-SQL 表值函数将调用函数的结果具体化为中间表。 由于它们使用中间表，因此它们可以对于结果支持约束和唯一索引。 返回大型结果时，这些功能非常有用。

相比之下，公共语言运行时（CLR）表值函数表示流式处理替代函数。 无需在单个表中具体化整个结果集。 托管函数返回的 IEnumerable 对象由调用表值函数的查询的执行计划直接调用，结果以增量方式使用。 这种流模型可确保在第一行可用之后立即使用结果，而不是等待填充整个表。 如果返回了大量行，则这也是更好的替代方法，因为它们不必在整个内存中具体化。 例如，可以使用托管表值函数分析文本文件并将其中的每行作为一行返回。

实现表值函数

在 .NET Framework 程序集中的类上实现表值函数作为方法。 表值函数代码必须实现 IEnumerable 接口。 IEnumerable 接口在 .NET Framework 中定义。 表示 .NET Framework 中的数组和集合的类型已经实现 IEnumerable 接口。 这样，就可以轻松地编写将集合或数组转换为结果集的表值函数。

表值参数

表值参数即传递到某一过程或函数的用户定义表类型，它提供了一种将多行数据传递到服务器的高效方法。 表值参数提供与参数数组类似的功能，但提供更大的灵活性和更紧密的与 Transact-SQL 的集成。 它们还提供提升性能的潜力。

表值参数还有助于减少到服务器的往返次数。 可以将数据作为表值参数发送到服务器，而不是向服务器发送多个请求（例如，对于标量参数列表）。 用户定义表类型不能作为表值参数传递给在 SQL Server 进程中执行的托管存储过程或函数，也不能作为表值参数进行返回。 有关表值参数的详细信息，请参阅 使用表值参数（数据库引擎）。

输出参数和表值函数

可以使用输出参数从表值函数返回信息。 在实现代码表值函数中的相应参数应将按引用传递参数用作参数。 Visual Basic .NET 不支持与 C# 相同的输出参数。 必须按引用指定参数，并应用 <Out()> 属性来表示输出参数，如以下示例所示：

Imports System.Runtime.InteropServices
...
Public Shared Sub FillRow ( <Out()> ByRef value As SqlInt32)

在 Transact-SQL 中定义表值函数

定义 CLR 表值函数的语法类似于 Transact-SQL 表值函数的语法，并添加了 EXTERNAL NAME 子句。 例如：

CREATE FUNCTION GetEmpFirstLastNames()
RETURNS TABLE (
    FirstName NVARCHAR (4000),
    LastName NVARCHAR (4000)
)
AS EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname].GetEmpFirstLastNames;

表值函数用于以相关格式表示数据，以便在查询中进一步处理，例如：

SELECT *
FROM func();

SELECT *
FROM tbl
     INNER JOIN func() AS f
         ON tbl.col = f.col;

SELECT *
FROM tbl AS t
CROSS APPLY func(t.col);

在以下情况下，表值函数可以返回表：

  • 当从标量输入参数中创建表值函数时。 例如，使用以逗号分隔的数字字符串并将它们透视到某个表中的表值函数。

  • 当从外部数据生成表值函数时。 例如，读取事件日志并将其显示为表的表值函数。

注意

表值函数只能通过 InitMethod 方法中的 Transact-SQL 查询执行数据访问，而不能在 FillRow 方法中执行。 执行 Transact-SQL 查询时，应使用 SqlFunction.DataAccess.Read 属性标记 InitMethod。

表值函数示例

下面的表值函数返回系统事件日志中的信息。 此函数采用单个字符串参数，其中包含要读取的事件日志的名称。

示例代码

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;

public class TabularEventLog
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod(String logname)
    {
        return new EventLog(logname).Entries;
    }

    public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
    {
        EventLogEntry eventLogEntry = (EventLogEntry)obj;
        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
        message = new SqlChars(eventLogEntry.Message);
        category = new SqlChars(eventLogEntry.Category);
        instanceId = eventLogEntry.InstanceId;
    }
}

声明和使用示例表值函数

编译示例表值函数后，可以在如下所示的 Transact-SQL 中声明它：

USE master;
-- Replace SQL_Server_logon with your SQL Server user credentials.

GRANT EXTERNAL ACCESS ASSEMBLY TO [SQL_Server_logon];

-- Modify the following line to specify a different database.
ALTER DATABASE master
SET TRUSTWORTHY ON;

-- Modify the next line to use the appropriate database.
CREATE ASSEMBLY tvfEventLog
    FROM 'D:\assemblies\tvfEventLog\tvfeventlog.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION ReadEventLog
(@logname NVARCHAR (100))
RETURNS TABLE (
    logTime DATETIME,
    Message NVARCHAR (4000),
    Category NVARCHAR (4000),
    InstanceId BIGINT)
AS EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod;
GO

SQL Server 上不支持使用 /clr:pure 编译的视觉C++数据库对象。 例如，此类数据库对象包含表值函数。

若要测试示例，请尝试以下 Transact-SQL 代码：

-- Select the top 100 events,
SELECT TOP 100 *
FROM dbo.ReadEventLog(N'Security') AS T;
GO

-- Select the last 10 login events.
SELECT TOP 10 T.logTime,
              T.Message,
              T.InstanceId
FROM dbo.ReadEventLog(N'Security') AS T
WHERE T.Category = N'Logon/Logoff';
GO

例子

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可以从 Microsoft SQL Server 示例和社区项目 主页下载该数据库。

一个。 返回 SQL Server 查询的结果

以下示例显示了查询 SQL Server 数据库的表值函数。

将源代码文件命名为 FindInvalidEmails.cs 或 FindInvalidEmails.vb。

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions {
   private class EmailResult {
      public SqlInt32 CustomerId;
      public SqlString EmailAddress;

      public EmailResult(SqlInt32 customerId, SqlString emailAddress) {
         CustomerId = customerId;
         EmailAddress = emailAddress;
      }
   }

   public static bool ValidateEmail(SqlString emailAddress) {
      if (emailAddress.IsNull)
         return false;

      if (!emailAddress.Value.EndsWith("@adventure-works.com"))
         return false;

      // Validate the address. Put any more rules here.
      return true;
   }

   [SqlFunction(
       DataAccess = DataAccessKind.Read,
       FillRowMethodName = "FindInvalidEmails_FillRow",
       TableDefinition="CustomerId int, EmailAddress nvarchar(4000)")]
   public static IEnumerable FindInvalidEmails(SqlDateTime modifiedSince) {
      ArrayList resultCollection = new ArrayList();

      using (SqlConnection connection = new SqlConnection("context connection=true")) {
         connection.Open();

         using (SqlCommand selectEmails = new SqlCommand(
             "SELECT " +
             "[CustomerID], [EmailAddress] " +
             "FROM [AdventureWorksLT2022].[SalesLT].[Customer] " +
             "WHERE [ModifiedDate] >= @modifiedSince",
             connection)) {
            SqlParameter modifiedSinceParam = selectEmails.Parameters.Add(
                "@modifiedSince",
                SqlDbType.DateTime);
            modifiedSinceParam.Value = modifiedSince;

            using (SqlDataReader emailsReader = selectEmails.ExecuteReader()) {
               while (emailsReader.Read()) {
                  SqlString emailAddress = emailsReader.GetSqlString(1);
                  if (ValidateEmail(emailAddress)) {
                     resultCollection.Add(new EmailResult(
                         emailsReader.GetSqlInt32(0),
                         emailAddress));
                  }
               }
            }
         }
      }

      return resultCollection;
   }

   public static void FindInvalidEmails_FillRow(
       object emailResultObj,
       out SqlInt32 customerId,
       out SqlString emailAddress) {
      EmailResult emailResult = (EmailResult)emailResultObj;

      customerId = emailResult.CustomerId;
      emailAddress = emailResult.EmailAddress;
   }
};

将源代码编译为 DLL 并将此 DLL 复制到 C 驱动器的根目录下。 然后，执行以下 Transact-SQL 查询。

USE AdventureWorksLT2022;
GO

IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name = 'FindInvalidEmails')
    DROP FUNCTION FindInvalidEmails;
GO

IF EXISTS (SELECT name
           FROM sys.assemblies
           WHERE name = 'MyClrCode')
    DROP ASSEMBLY MyClrCode;
GO

CREATE ASSEMBLY MyClrCode
    FROM 'C:\FindInvalidEmails.dll'
    WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION FindInvalidEmails
(@ModifiedSince DATETIME)
RETURNS TABLE (
    CustomerId INT,
    EmailAddress NVARCHAR (4000))
AS EXTERNAL NAME MyClrCode.UserDefinedFunctions.[FindInvalidEmails];
GO

SELECT *
FROM FindInvalidEmails('2000-01-01');
GO