Aracılığıyla paylaş

CLR tablo değerli işlevler

Şunlar için geçerlidir: SQL Server

Tablo değerli işlev, tablo döndüren kullanıcı tanımlı bir işlevdir.

SQL Server, tablo değerli işlevlerin işlevselliğini, herhangi bir yönetilen dilde tablo değerli bir işlev tanımlamanıza olanak tanıyarak genişletir. Veriler bir IEnumerable veya IEnumerator nesnesi aracılığıyla tablo değerli bir işlevden döndürülür.

Tablo değerli işlevler için, dönüş tablo türünün sütunları zaman damgası sütunlarını veya Unicode olmayan dize veri türü sütunlarını (karakter, varcharve metin) içeremez. NOT NULL kısıtlaması desteklenmez.

Transact-SQL ve CLR tablo değerli işlevler arasındaki farklar

tablo değerli işlevler Transact-SQL işlevi ara tabloya çağırmanın sonuçlarını oluşturur. Ara tablo kullandıklarından, sonuçlar üzerinde kısıtlamaları ve benzersiz dizinleri destekleyebilirler. Bu özellikler, büyük sonuçlar döndürülürken yararlı olabilir.

Buna karşılık, ortak dil çalışma zamanı (CLR) tablo değerli işlevler bir akış alternatifi temsil ediyor. Sonuç kümesinin tamamının tek bir tabloda gerçekleştirilmesi gerekmez. Yönetilen işlev tarafından döndürülen IEnumerable nesnesi, tablo değerli işlevi çağıran sorgunun yürütme planı tarafından doğrudan çağrılır ve sonuçlar artımlı bir şekilde kullanılır. Bu akış modeli, sonuçların tablonun tamamının doldurulmasını beklemek yerine ilk satır kullanılabilir olduktan hemen sonra kullanılabilmesini sağlar. Ayrıca çok sayıda satır döndürülürse daha iyi bir alternatiftir çünkü bunların bir bütün olarak bellekte gerçekleştirilmesi gerekmez. Örneğin, bir metin dosyasını ayrıştırmak ve her satırı satır olarak döndürmek için yönetilen tablo değerli bir işlev kullanılabilir.

Tablo değerli işlevleri uygulama

Tablo değerli işlevleri .NET Framework derlemesindeki bir sınıfta yöntemler olarak uygulayın. Tablo değerli işlev kodunuz IEnumerable arabirimini uygulamalıdır. IEnumerable arabirimi .NET Framework'te tanımlanır. .NET Framework'te dizileri ve koleksiyonları temsil eden türler zaten IEnumerable arabirimini uygular. Bu, bir koleksiyonu veya diziyi sonuç kümesine dönüştüren tablo değerli işlevler yazmayı kolaylaştırır.

Tablo değerli parametreler

Tablo değerli parametreler, bir yordama veya işleve geçirilen ve sunucuya birden çok veri satırı geçirmek için verimli bir yol sağlayan kullanıcı tanımlı tablo türleridir. Tablo değerli parametreler parametre dizilerine benzer işlevler sağlar, ancak Transact-SQL ile daha fazla esneklik ve daha yakın tümleştirme sunar. Ayrıca daha iyi performans potansiyeli sağlar.

Tablo değerli parametreler, sunucuya gidiş dönüş sayısını azaltmaya da yardımcı olur. Skaler parametrelerin listesi gibi birden çok isteği sunucuya göndermek yerine, veriler tablo değerli bir parametre olarak sunucuya gönderilebilir. Kullanıcı tanımlı tablo türü, SQL Server işleminde yürütülen yönetilen bir saklı yordama veya işleve tablo değerli parametre olarak geçirilemiyor veya bu parametreden döndürülemiyor. Tablo değerli parametreler hakkında daha fazla bilgi için bkz. tablo değerli parametreleri kullanma (Veritabanı Altyapısı).

Çıkış parametreleri ve tablo değerli işlevler

Çıktı parametreleri kullanılarak tablo değerli işlevlerden bilgiler döndürülebilir. Uygulama kodu tablo değerli işlevindeki karşılık gelen parametre, bağımsız değişken olarak bir pass-by-reference parametresi kullanmalıdır. Visual Basic .NET, çıkış parametrelerini C# ile aynı şekilde desteklemez. Parametreyi başvuruya göre belirtmeniz ve aşağıdaki örnekte olduğu gibi bir çıkış parametresini temsil etmek için <Out()> özniteliğini uygulamanız gerekir:

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

Transact-SQL'de tablo değerli bir işlev tanımlama

CLR tablo değerli işlev tanımlama söz dizimi, EXTERNAL NAME yan tümcesinin eklenmesiyle Transact-SQL tablo değerli işlevin söz dizimine benzer. Mesela:

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

Tablo değerli işlevler, aşağıdaki gibi sorgularda daha fazla işlem yapmak üzere verileri ilişkisel biçimde göstermek için kullanılır:

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

Tablo değerli işlevler aşağıdaki durumlarda bir tablo döndürebilir:

  • Skaler giriş bağımsız değişkenlerinden oluşturulur. Örneğin, virgülle ayrılmış bir sayı dizesi alan ve bunları tabloya özetleyen tablo değerli bir işlev.

  • Dış verilerden oluşturulur. Örneğin, olay günlüğünü okuyan ve tablo olarak kullanıma sunan tablo değerli bir işlev.

Not

Tablo değerli bir işlev, InitMethod yönteminde değil, yalnızca FillRow yöntemindeki bir Transact-SQL sorgusu aracılığıyla veri erişimi gerçekleştirebilir. Transact-SQL bir sorgu gerçekleştirilirse InitMethodSqlFunction.DataAccess.Read özniteliği özelliğiyle işaretlenmelidir.

Tablo değerli örnek bir işlev

Aşağıdaki tablo değerli işlev, sistem olay günlüğündeki bilgileri döndürür. İşlev, okunacak olay günlüğünün adını içeren tek bir dize bağımsız değişkeni alır.

Örnek kod

  • C#
  • Visual Basic .NET 
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;
    }
}

Örnek tablo değerli işlevi bildirme ve kullanma

Tablo değerli örnek işlev derlendiğinde aşağıdaki gibi Transact-SQL bildirilebilir:

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

/clr:pure ile derlenen Visual C++ veritabanı nesneleri SQL Server'da yürütme için desteklenmez. Örneğin, bu tür veritabanı nesneleri tablo değerli işlevler içerir.

Örneği test etmek için aşağıdaki Transact-SQL kodunu deneyin:

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

Örnekler

Bu makaledeki kod örnekleri, AdventureWorks2025 giriş sayfasından indirebileceğiniz AdventureWorksDW2025 veya örnek veritabanını kullanır.

A. SQL Server sorgusunun sonuçlarını döndürme

Aşağıdaki örnekte, SQL Server veritabanını sorgulayan tablo değerli bir işlev gösterilmektedir.

Kaynak kod dosyanıza FindInvalidEmails.cs veya FindInvalidEmails.vb adını verin.

  • C#
  • Visual Basic .NET 
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;
   }
};

Kaynak kodu bir DLL'ye derleyin ve DLL'yi C sürücünüzün kök dizinine kopyalayın. Ardından aşağıdaki Transact-SQL sorgusunu yürütür.

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
  • Last updated on