CLR テーブル値関数
テーブル値関数とは、テーブルを返すユーザー定義関数です。
SQL Server 2005 以降の SQL Server では、テーブル値関数の機能が拡張され、テーブル値関数をどのマネージ言語でも定義できるようになりました。 テーブル値関数からは IEnumerable オブジェクトまたは IEnumerator オブジェクトを経由してデータが返されます。
注意 |
---|
テーブル値関数で返されるテーブル型の列には、timestamp 型の列および Unicode 以外の文字列データ型の列 (char、varchar、text など) を含めることはできません。 NOT NULL 制約はサポートされません。 |
Transact-SQL と CLR のテーブル値関数の違い
Transact-SQL のテーブル値関数は、関数の呼び出し結果を具体化して中間テーブルを作成します。 TVF では中間テーブルを使用するため、結果に対する制約や一意インデックスがサポートされます。 これらの機能は、大量の結果が返される場合に非常に有用です。
一方、CLR のテーブル値関数は同じことをストリーミングで実現します。 結果セット全体を 1 つのテーブルに具体化する必要はありません。 テーブル値関数を呼び出すクエリの実行プランから、マネージ関数が返す IEnumerable オブジェクトを直接呼び出し、結果を増分方式で使用します。 このストリーミング モデルでは、テーブル全体に値が格納されるまで待たなくても、最初の行が生成された直後から結果を使用できます。 返される行をメモリ内で一括して具体化する必要がないので、返される行数が多い場合にもストリーミングが適しています。 たとえば、マネージ テーブル値関数を使用して、テキスト ファイルを解析し、テキストの各行を 1 つのテーブル行にして返すことができます。
テーブル値関数の実装
テーブル値関数は、Microsoft .NET Framework アセンブリのクラスのメソッドとして実装します。 テーブル値関数のコードでは、IEnumerable インターフェイスを実装する必要があります。 IEnumerable インターフェイスは .NET Framework で定義されています。 .NET Framework で配列およびコレクションを表す型は、既に IEnumerable インターフェイスを実装しています。 このため、コレクションまたは配列を結果セットに変換するテーブル値関数を簡単に記述できます。
テーブル値パラメーター
テーブル値パラメーターとは、プロシージャや関数に渡されるユーザー定義のテーブル型です。テーブル値パラメーターを使用すると、複数行のデータを効率的にサーバーに渡すことができます。 テーブル値パラメーターの機能はパラメーター配列に似ていますが、より柔軟性が高く、Transact-SQL との統合も緊密です。 テーブル値パラメーターを使用するとパフォーマンスが向上する可能性もあります。 さらに、サーバーへのラウンド トリップの回数を減らすのにも有用です。 スカラー パラメーターのリストを使用するなどしてサーバーに複数の要求を送信する代わりに、データをテーブル値パラメーターとしてサーバーに送信できます。 SQL Server のプロセスで実行されているマネージ ストアド プロシージャやマネージ関数にユーザー定義のテーブル型をテーブル値パラメーターとして渡したり、戻り値として受け取ったりすることはできません。 テーブル値パラメーターの詳細については、「テーブル値パラメータ (データベース エンジン)」を参照してください。
出力パラメーターとテーブル値関数
出力パラメーターを使用すると、テーブル値関数から情報を返すことができます。 実装コードのテーブル値関数の対応するパラメーターは、引数として参照渡しのパラメーターを使用する必要があります。 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);
テーブル値関数は、次の場合にテーブルを返すことができます。
スカラー値の入力引数から作成された場合。 たとえば、数値をコンマで区切った文字列を変換してテーブルにするテーブル値関数などです。
外部データから生成した場合。 たとえば、イベント ログを読み取り、テーブルとして公開するテーブル値関数などです。
注 テーブル値関数は、FillRow メソッドではなく InitMethod メソッドで Transact-SQL クエリを使用した場合に限り、データ アクセスを実行できます。 Transact-SQL クエリを実行する場合は、InitMethod を SqlFunction.DataAccess.Read 属性プロパティに指定してください。
テーブル値関数のサンプル
次のテーブル値関数は、システム イベント ログから情報を返します。 読み取るイベント ログの名前を含んだ文字列引数を 1 つ受け取ります。
サンプル コード
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 以降、互換性レベルが "80" の SQL Server データベースでは、マネージ コードでユーザー定義型、ストアド プロシージャ、関数、集計、またはトリガーを作成することはできません。 SQL Server のこれらの CLR 統合機能を使用するには、sp_dbcmptlevel ストアド プロシージャを使用してデータベースの互換性レベルを "100" に設定する必要があります。
SQL Server 2005 では、/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 の AdventureWorks Light データベースを使用します。 AdventureWorks のダウンロードについては、https://www.codeplex.com/sqlserversamples を参照してください。
ソース コード ファイルに 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 Microsoft.SqlServer.Server
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Public Class UserDefinedFunctions
<SqlFunction(DataAccess:=DataAccessKind.Read, FillRowMethodName:="FindInvalidEmails_FillRow"), TableDefinition="CustomerId int, EmailAddress nvarchar(4000)")> _
Public Shared Function FindInvalidEmails(ByVal modifiedSince As SqlDateTime) As IEnumerable
Dim resultCollection As New ArrayList
Using connection As SqlConnection = New SqlConnection("context connection=true")
connection.Open()
Using selectEmails As SqlCommand = New SqlCommand("SELECT [CustomerID], [EmailAddress] FROM [AdventureWorksLT2008].[SalesLT].[Customer] WHERE [ModifiedDate] >= @modifiedSince", connection)
selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime).Value = modifiedSince
Using emailsReader As SqlDataReader = selectEmails.ExecuteReader
Do While emailsReader.Read
Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
If UserDefinedFunctions.ValidateEmail(emailAddress) Then
resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
End If
Loop
End Using
Return resultCollection
End Using
End Using
End Function
Public Shared Sub FindInvalidEmails_FillRow(ByVal emailResultObj As Object, <Out()> ByRef customerId As SqlInt32, <Out()> ByRef emailAdress As SqlString)
Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)
customerId = emailResult.CustomerId
emailAdress = emailResult.EmailAdress
End Sub
Public Shared Function ValidateEmail(ByVal 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
Return True
End Function
Private Class EmailResult
Public Sub New(ByVal customerId As SqlInt32, ByVal emailAdress As SqlString)
Me.CustomerId = customerId
Me.EmailAdress = emailAdress
End Sub
Public CustomerId As SqlInt32
Public EmailAdress As SqlString
End Class
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
変更履歴
変更内容 |
---|
SQL Server クエリのサンプルを追加しました。 |