Fungsi Bernilai Tabel CLR

Artikel
07/04/2024

Berlaku untuk: SQL Server

Fungsi bernilai tabel adalah fungsi yang ditentukan pengguna yang mengembalikan tabel.

Dimulai dengan SQL Server 2005 (9.x), SQL Server memperluas fungsionalitas fungsi bernilai tabel dengan memungkinkan Anda menentukan fungsi bernilai tabel dalam bahasa terkelola apa pun. Data dikembalikan dari fungsi bernilai tabel melalui objek IEnumerable atau IEnumerator .

Catatan

Untuk fungsi bernilai tabel, kolom tipe tabel yang dikembalikan tidak dapat menyertakan kolom tanda waktu atau kolom tipe data string non-Unicode (seperti karakter, varchar, dan teks). Batasan NOT NULL tidak didukung.

Untuk informasi selengkapnya tentang fungsi CLR Table-Valued, lihat MSSQLTips' Pengantar fungsi bernilai tabel CLR SQL Server!

Perbedaan Antara Fungsi Bernilai Tabel Transact-SQL dan CLR

Fungsi bernilai tabel Transact-SQL mewujudkan hasil pemanggilan fungsi ke dalam tabel perantara. Karena menggunakan tabel perantara, mereka dapat mendukung batasan dan indeks unik atas hasilnya. Fitur-fitur ini bisa sangat berguna ketika hasil besar dikembalikan.

Sebaliknya, fungsi bernilai tabel CLR mewakili alternatif streaming. Tidak ada persyaratan bahwa seluruh set hasil diwujudkan dalam satu tabel. Objek IEnumerable yang dikembalikan oleh fungsi terkelola secara langsung dipanggil oleh rencana eksekusi kueri yang memanggil fungsi bernilai tabel, dan hasilnya dikonsumsi secara bertahap. Model streaming ini memastikan bahwa hasil dapat dikonsumsi segera setelah baris pertama tersedia, alih-alih menunggu seluruh tabel diisi. Ini juga merupakan alternatif yang lebih baik jika Anda memiliki jumlah baris yang sangat besar yang dikembalikan, karena tidak harus diwujudkan dalam memori secara keseluruhan. Misalnya, fungsi bernilai tabel terkelola dapat digunakan untuk mengurai file teks dan mengembalikan setiap baris sebagai baris.

Menerapkan Fungsi Bernilai Tabel

Terapkan fungsi bernilai tabel sebagai metode pada kelas dalam rakitan Microsoft .NET Framework. Kode fungsi bernilai tabel Anda harus mengimplementasikan antarmuka IEnumerable . Antarmuka IEnumerable didefinisikan dalam .NET Framework. Jenis yang mewakili array dan koleksi dalam .NET Framework sudah mengimplementasikan antarmuka IEnumerable . Ini memudahkan penulisan fungsi bernilai tabel yang mengonversi koleksi atau array menjadi kumpulan hasil.

parameter bernilai tabel

Parameter bernilai tabel adalah jenis tabel yang ditentukan pengguna yang diteruskan ke prosedur atau fungsi dan menyediakan cara yang efisien untuk meneruskan beberapa baris data ke server. Parameter bernilai tabel menyediakan fungsionalitas yang sama dengan array parameter, tetapi menawarkan fleksibilitas yang lebih besar dan integrasi yang lebih dekat dengan Transact-SQL. Mereka juga memberikan potensi performa yang lebih baik. Parameter bernilai tabel juga membantu mengurangi jumlah perjalanan pulang pergi ke server. Alih-alih mengirim beberapa permintaan ke server, seperti dengan daftar parameter skalar, data dapat dikirim ke server sebagai parameter bernilai tabel. Jenis tabel yang ditentukan pengguna tidak dapat diteruskan sebagai parameter bernilai tabel ke, atau dikembalikan dari, prosedur tersimpan terkelola atau fungsi yang dijalankan dalam proses SQL Server. Untuk informasi selengkapnya tentang parameter bernilai tabel, lihat Menggunakan Parameter Bernilai Tabel (Mesin Database).

Parameter Output dan Fungsi Bernilai Tabel

Informasi dapat dikembalikan dari fungsi bernilai tabel menggunakan parameter output. Parameter yang sesuai dalam fungsi kode implementasi bernilai tabel harus menggunakan parameter referensi pass-by sebagai argumen. Perhatikan bahwa Visual Basic tidak mendukung parameter output dengan cara yang sama seperti visual C#. Anda harus menentukan parameter berdasarkan referensi dan menerapkan <atribut Out()> untuk mewakili parameter output, seperti dalam hal berikut:

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

Menentukan Fungsi Bernilai Tabel dalam Transact-SQL

Sintaks untuk menentukan fungsi bernilai tabel CLR mirip dengan fungsi bernilai tabel Transact-SQL, dengan penambahan klausa NAMA EKSTERNAL. Contohnya:

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

Fungsi bernilai tabel digunakan untuk mewakili data dalam bentuk relasional untuk pemrosesan lebih lanjut dalam kueri seperti:

select * from function();  
select * from tbl join function() f on tbl.col = f.col;  
select * from table t cross apply function(t.column);

Fungsi bernilai tabel dapat mengembalikan tabel saat:

Dibuat dari argumen input skalar. Misalnya, fungsi bernilai tabel yang mengambil string angka yang dibatasi koma dan mempivotnya ke dalam tabel.
Dihasilkan dari data eksternal. Misalnya, fungsi bernilai tabel yang membaca log peristiwa dan mengeksposnya sebagai tabel.

Catatan Fungsi bernilai tabel hanya dapat melakukan akses data melalui kueri Transact-SQL dalam metode InitMethod , dan bukan dalam metode FillRow . InitMethod harus ditandai dengan properti atribut SqlFunction.DataAccess.Read jika kueri Transact-SQL dilakukan.

Contoh Fungsi Bernilai Tabel

Fungsi bernilai tabel berikut mengembalikan informasi dari log peristiwa sistem. Fungsi ini mengambil argumen string tunggal yang berisi nama log peristiwa untuk dibaca.

Kode Sampel

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

Mendeklarasikan dan Menggunakan Contoh Fungsi Bernilai Tabel

Setelah contoh fungsi bernilai tabel dikompilasi, fungsi dapat dideklarasikan dalam Transact-SQL seperti ini:

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

Objek database Visual C++ yang dikompilasi dengan /clr:pure tidak didukung untuk eksekusi pada SQL Server 2005 (9.x). Misalnya, objek database tersebut menyertakan fungsi bernilai tabel.

Untuk menguji sampel, coba kode Transact-SQL berikut:

-- 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

Sampel: Mengembalikan Hasil Kueri SQL Server

Contoh berikut menunjukkan fungsi bernilai tabel yang meminta database SQL Server. Sampel ini menggunakan database AdventureWorks Lightweight dari SQL Server 2008 (10.0.x), lihat database sampel AdventureWorks

Beri nama file kode sumber Anda FindInvalidEmails.cs atau 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

Kompilasi kode sumber ke DLL dan salin DLL ke direktori akar drive C Anda. Kemudian, jalankan kueri Transact-SQL berikut.

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

Bagikan melalui