共用方式為

CLR 數據表值函式

  • 發行項

適用於：SQL Server

數據表值函式是傳回數據表的用戶定義函數。

SQL Server 可讓您以任何 Managed 語言定義資料表值函式，藉此擴充數據表值函式的功能。 數據會透過 IEnumerable 或 IEnumerator 對象從數據表值函式傳回。

針對數據表值函式，傳回數據表類型的數據行不能包含時間戳數據行或非 Unicode 字串數據類型數據行（例如 char、varchar，以及 文字）。 不支援 NOT NULL 條件約束。

Transact-SQL 與 CLR 數據表值函式之間的差異

Transact-SQL 數據表值函式會將呼叫函式的結果具體化為中繼數據表。 由於它們使用中繼數據表，因此可支援結果的條件約束和唯一索引。 傳回大型結果時，這些功能很有用。

相反地，Common Language Runtime （CLR） 數據表值函式代表串流替代方案。 不需要在單一數據表中具體化整個結果集。 Managed 函式所傳回的 IEnumerable 物件是由呼叫數據表值函式的查詢執行計劃直接呼叫，並以累加方式取用結果。 此串流模型可確保在第一個數據列可用之後立即取用結果，而不是等候填入整個數據表。 如果您有大量傳回的數據列，這也是較佳的替代方案，因為它們不需要整體在記憶體中具體化。 例如，Managed 資料表值函式可用來剖析文本檔，並以數據列傳回每一行。

實作數據表值函式

在 .NET Framework 元件中的類別上實作數據表值函式做為方法。 您的數據表值函式程式代碼必須實作 IEnumerable 介面。 IEnumerable 介面定義於 .NET Framework 中。 代表 .NET Framework 中陣列和集合的類型已經實作 IEnumerable 介面。 這可讓您輕鬆地撰寫將集合或數位轉換成結果集的數據表值函式。

數據表值參數

數據表值參數是傳遞至程式或函式的使用者定義數據表類型，並提供將多個數據列傳遞至伺服器的有效方式。 數據表值參數提供與參數數位類似的功能，但提供更大的彈性，並與 Transact-SQL 更緊密整合。 它們也會提供更佳效能的潛力。

數據表值參數也有助於減少往返伺服器的次數。 數據可以傳送至伺服器做為數據表值參數，而不是將多個要求傳送至伺服器，例如純量參數清單。 使用者定義數據表類型無法當做數據表值參數傳遞至 SQL Server 進程中執行的 Managed 預存程式或函式，或是從傳回。 如需資料表值參數的詳細資訊，請參閱 使用資料表值參數 （Database Engine）。

輸出參數和數據表值函式

資訊可能會使用輸出參數從數據表值函式傳回。 實作程式代碼數據表值函式中的對應參數應該使用傳遞傳址參數作為自變數。 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 查詢，則 InitMethod 應該使用 SqlFunction.DataAccess.Read 屬性標示。

範例數據表值函式

下表值函式會從系統事件記錄檔傳回資訊。 函式會採用包含要讀取之事件記錄檔名稱的單一字串自變數。

範例程序代碼

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 編譯的 Visual 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 EmailAdress;

      public EmailResult(SqlInt32 customerId, SqlString emailAdress) {
         CustomerId = customerId;
         EmailAdress = emailAdress;
      }
   }

   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 emailAdress) {
      EmailResult emailResult = (EmailResult)emailResultObj;

      customerId = emailResult.CustomerId;
      emailAdress = emailResult.EmailAdress;
   }
};

將原始碼編譯至 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