CLR 資料表值函式
適用於：
SQL Server
資料表值函式是會傳回資料表的使用者定義函數。
從 SQL Server 2005 (9.x) 開始，SQL Server可讓您在任何 Managed 語言中定義資料表值函式來擴充資料表值函式的功能。 資料會透過 IEnumerable 或 IEnumerator 物件從資料表值函式傳回。
注意
對於資料表值函式，傳回資料表類型的資料行不能包含時間戳記資料行或非 Unicode 字串資料類型資料行， (例如 char、 Varchar和 text) 。 不支援 NOT NULL 條件約束。
如需 CLR Table-Valued函式的詳細資訊，請參閱 MSSQLTips' SQL Server CLR 資料表值函式簡介！
Transact-SQL 和 CLR 資料表值函式之間的差異
Transact-SQL 資料表值函式會將呼叫函式的結果具體化為中繼資料表。 由於 TVF 使用中繼資料表，因此可以透過結果支援條件約束和唯一的索引。 當傳回較大的結果時，這些功能會非常有用。
相反地，CLR 資料表值函式則是屬於以資料流模型進行處理的替代方案。 整組結果不需要在單一資料表中具體化。 Managed 函式所傳回的 IEnumerable 物件會由呼叫資料表值函式之查詢的執行計畫直接呼叫，並以累加方式取用結果。 此資料流模型能確保第一個資料列可供使用之後，就立即使用結果，而不會等待整個資料表填入完成。 如果傳回大量的資料列，這也是一個較好的替代方式，因為它們不必整體在記憶體中進行實體化。 例如，Managed 資料表值函式可用來剖析文字檔案，並將每一行以資料列的方式傳回。
實作資料表數值函數
在 Microsoft .NET Framework 元件中的類別上實作資料表值函式作為方法。 您的資料表值函式程式碼必須實作 IEnumerable 介面。 IEnumerable介面定義于.NET Framework中。 代表.NET Framework中陣列和集合的類型已經實作IEnumerable介面。 這樣您就可以輕易地撰寫出能將集合或陣列轉換為結果集的資料表值函式。
資料表值參數
資料表值參數是傳入到程序或函數中的使用者定義資料表類型，能提供有效的方式將資料的多個資料列傳遞到伺服器。 資料表值參數提供與參數陣列類似的功能，但提供更大的彈性，並與 Transact-SQL 更緊密整合。 它們也能夠協助您獲得更佳的效能。 資料表值參數也可以減少與伺服器之間的往返次數。 資料能以資料表值參數的形式傳送到伺服器，而不是傳送多個要求到伺服器，例如一併傳送純量參數的清單。 使用者定義資料表類型無法當做資料表值參數傳遞至在SQL Server進程中執行的 Managed 預存程式或函式，或從中傳回。 如需資料表值參數的詳細資訊，請參閱使用資料表值參數 (資料庫引擎)。
輸出參數和資料表值函式
資訊可以使用輸出參數，從資料表值函式傳回。 實作程式碼資料表值函式中的對應參數應使用依參照傳遞的參數做為引數。 請注意，Visual Basic 不支援輸出參數的方式，與 Visual 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))
EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname]. GetEmpFirstLastNames;
資料表值函式用於以關聯式形式表示資料，以便在查詢中進行進一步的處理，例如：
select * from function();
select * from tbl join function() f on tbl.col = f.col;
select * from table t cross apply function(t.column);
當資料表值函式符合下列條件時，便會傳回資料表：
當資料表值函式是從純量輸入引數建立時。 例如，採用逗號分隔數字字串，並將它們樞紐至資料表的資料表值函式。
當資料表值函式是從外部資料產生時。 例如，讀取事件記錄檔並將其以資料表的方式公開的資料表值函式。
注意 資料表值函式只能透過 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;
}
}
Imports System
Imports System.Data.Sql
Imports Microsoft.SqlServer.Server
Imports System.Collections
Imports System.Data.SqlTypes
Imports System.Diagnostics
Imports System.Runtime.InteropServices
Public Class TabularEventLog
<SqlFunction(FillRowMethodName:="FillRow")> _
Public Shared Function InitMethod(ByVal logname As String) As IEnumerable
Return New EventLog(logname).Entries
End Function
Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef timeWritten As SqlDateTime, <Out()> ByRef message As SqlChars, <Out()> ByRef category As SqlChars, <Out()> ByRef instanceId As Long)
Dim eventLogEnTry As EventLogEntry = CType(obj, EventLogEntry)
timeWritten = New SqlDateTime(eventLogEnTry.TimeWritten)
message = New SqlChars(eventLogEnTry.Message)
category = New SqlChars(eventLogEnTry.Category)
instanceId = eventLogEnTry.InstanceId
End Sub
End Class
宣告和使用範例資料表值函式
編譯範例資料表值函式之後，可以在 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 2005 (9.x) 不支援使用 /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
範例：傳回 SQL Server 查詢的結果
下列範例顯示查詢SQL Server資料庫的資料表值函式。 此範例使用來自 SQL Server 2008 (10.0.x) 的 AdventureWorks 輕量型資料庫，請參閱AdventureWorks 範例資料庫
將原始程式碼檔命名為 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 [AdventureWorksLT2008].[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;
}
};
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Public Partial Class UserDefinedFunctions
Private Class EmailResult
Public CustomerId As SqlInt32
Public EmailAdress As SqlString
Public Sub New(customerId__1 As SqlInt32, emailAdress__2 As SqlString)
CustomerId = customerId__1
EmailAdress = emailAdress__2
End Sub
End Class
Public Shared Function ValidateEmail(emailAddress As SqlString) As Boolean
If emailAddress.IsNull Then
Return False
End If
If Not emailAddress.Value.EndsWith("@adventure-works.com") Then
Return False
End If
' Validate the address. Put any more rules here.
Return True
End Function
<SqlFunction(DataAccess := DataAccessKind.Read, FillRowMethodName := "FindInvalidEmails_FillRow", TableDefinition := "CustomerId int, EmailAddress nvarchar(4000)")> _
Public Shared Function FindInvalidEmails(modifiedSince As SqlDateTime) As IEnumerable
Dim resultCollection As New ArrayList()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Using selectEmails As New SqlCommand("SELECT " & "[CustomerID], [EmailAddress] " & "FROM [AdventureWorksLT2008].[SalesLT].[Customer] " & "WHERE [ModifiedDate] >= @modifiedSince", connection)
Dim modifiedSinceParam As SqlParameter = selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime)
modifiedSinceParam.Value = modifiedSince
Using emailsReader As SqlDataReader = selectEmails.ExecuteReader()
While emailsReader.Read()
Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
If ValidateEmail(emailAddress) Then
resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
End If
End While
End Using
End Using
End Using
Return resultCollection
End Function
Public Shared Sub FindInvalidEmails_FillRow(emailResultObj As Object, ByRef customerId As SqlInt32, ByRef emailAdress As SqlString)
Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)
customerId = emailResult.CustomerId
emailAdress = emailResult.EmailAdress
End Sub
End ClassImports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Public Partial Class UserDefinedFunctions
Private Class EmailResult
Public CustomerId As SqlInt32
Public EmailAdress As SqlString
Public Sub New(customerId__1 As SqlInt32, emailAdress__2 As SqlString)
CustomerId = customerId__1
EmailAdress = emailAdress__2
End Sub
End Class
Public Shared Function ValidateEmail(emailAddress As SqlString) As Boolean
If emailAddress.IsNull Then
Return False
End If
If Not emailAddress.Value.EndsWith("@adventure-works.com") Then
Return False
End If
' Validate the address. Put any more rules here.
Return True
End Function
<SqlFunction(DataAccess := DataAccessKind.Read, FillRowMethodName := "FindInvalidEmails_FillRow", TableDefinition := "CustomerId int, EmailAddress nvarchar(4000)")> _
Public Shared Function FindInvalidEmails(modifiedSince As SqlDateTime) As IEnumerable
Dim resultCollection As New ArrayList()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Using selectEmails As New SqlCommand("SELECT " & "[CustomerID], [EmailAddress] " & "FROM [AdventureWorksLT2008].[SalesLT].[Customer] " & "WHERE [ModifiedDate] >= @modifiedSince", connection)
Dim modifiedSinceParam As SqlParameter = selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime)
modifiedSinceParam.Value = modifiedSince
Using emailsReader As SqlDataReader = selectEmails.ExecuteReader()
While emailsReader.Read()
Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
If ValidateEmail(emailAddress) Then
resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
End If
End While
End Using
End Using
End Using
Return resultCollection
End Function
Public Shared Sub FindInvalidEmails_FillRow(emailResultObj As Object, customerId As SqlInt32, emailAdress As SqlString)
Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)
customerId = emailResult.CustomerId
emailAdress = emailResult.EmailAdress
End Sub
End Class
將原始程式碼編譯為 DLL，並且將這個 DLL 複製到 C 磁碟機的根目錄。 然後，執行下列 Transact-SQL 查詢。
use AdventureWorksLT2008;
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 -- EXTERNAL_ACCESS;
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
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應