Makale
06/07/2010

CLR tablo biçiminde işlevler

Bir tablo deerli tablo döndüren kullanıcı tanımlı bir işlev işlevidir.

BaşlayarakSQL Server 2005,SQL Servertablo değerli işlevler sağlayarak, yönetilen bir dil. bir tablo değerli işlev tanımlamak işlevselliğini genişletirVeri bir tablo değerli işlev tarafından döndürülen birIEnumerableorIEnumeratornesne.

Not

Tablo değerli işlevler için zaman damgası sütunları veya Unicode olmayan dize veri türü sütunlarındaki dönüş tablo türündeki sütunlar ekleyemezsiniz (örneğin char, varchar, ve text).NOT NULL kısıtlaması desteklenmez.

Transact-SQL ve CLR tablo biçiminde işlevleri arasındaki farklar

Transact-SQL tablo değerli işlevler işlev ara bir tabloya arama sonuçlarını materialize.Bir ara kullandıkları bu yana tablo, bunlar kısıtlamaları ve benzersiz dizin üzerinde sonuçlar destekleyebilir.Büyük sonuçlar verilir, bu özellikleri son derece yararlı olabilir.

Buna karşılık, CLR tablo değerli işlev bir akış yerine temsil eder.sonuçlar kümesinin tamamını tek bir tabloda materialized hiç gereksinim vardır.The IEnumerable object returned by the managed function is directly called by the execution plan of the query that calls the table-valued function, and the results are consumed in an incremental manner.Bu akış modeli hemen ilk satırda doldurulması tüm tablo için beklemek yerine, kullanılabilir sonra sonuçlar tüketilebilir sağlar.Bir bütün olarak bellekte materialized olmadığı için çok sayıda satır döndürdü, varsa, ayrıca daha iyi bir alternatif olduğu.Örneğin, bir metin dosyası ayrıştırma ve her satırı bir satır olarak yönetilen bir tablo değerli işlev kullanılabilir.

Tablo biçiminde işlevler uygulama

Uygula tablo değerli işlev sınıf yöntemleri olarak birMicrosoft.NET framework derleme., Tablo değerli işlev kodu uygulamalıdırIEnumerablearabirim.The IEnumerable interface is defined in the .NET Framework.Temsil eden diziler ve topluluklar .NET Framework uygulamanız zaten yazdığıIEnumerablearabirim.Bu, bir dizi ya da bir sonucu dönüştürme tablo değerli işlevler yazmak için kolaylaştırır küme.

Tablo biçiminde parametreleri

Tablo deerli bir yordam veya işlev iletilir ve veriler birden çok satır sunucuya geçirmek için etkili bir yöntem sağlar, kullanıcı tanımlı tablo türlerini parametreleridir.Parametreleri tablo deerli parametre dizileri, benzer işlevsellik sağlar, ancak daha fazla esneklik ve daha yakın tümleştirmeTransact-SQL.Bunlar da olası en iyi performansı sağlar.Tablo deerli parametreleri yuvarlak başvuru sayısını sunucuya da yardımcı olur.Birden fazla isteği sunucuya göndermek yerine, gibi skaler parametreleri listesini verileri sunucuya bir tablo deerli parametre olarak gönderilebilir., Yönetilen bir saklı yordam iade edilmesi ya da işlev içinde çalışan için bir tablo deerli parametre olarak kullanıcı tanımlı tablo türü geçirilemezSQL Serverişlem.Tablo deerli parametreleri hakkında daha fazla bilgi için bkz:Tablo biçiminde Parametreler (Veritabanı Altyapısı).

Çıktı parametreleri ve tablo biçiminde işlevleri

Çıkış parametreleri kullanan tablo deerli işlevlerden bilgi döndürülebilir.Uygulama kodu tablo değerli işlev ilgili parametresinde bir başvuru parola parametresi bağımsız değişken olarak kullanmanız gerekir.Visual Basic, Visual C# aynı şekilde çıktı parametrelerini desteklemediğini unutmayın., Başvuruya göre parametre kısa olmalıdır ve uygulamanızı<Out()>özniteliğini aşağıdaki gibi bir çıkış parametresi göstermek için:

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

Tanımlayan bir tablo değerli işlev Transact-SQL

Bir CLR tanımlamak için gerekli sözdizimi tablo değerli işlev olarak benzer birTransact-SQLtablo değerli işlev, birlikteEXTERNAL NAMEyan tümce.Örneğin:

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

Ayrıntılı sorgular gibi işlemek için ilişkili form içindeki verileri göstermek için kullanılan tablo değerli işlevler:

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

Tablo değerli işlevler tablo dönebilirsiniz:

skaler girdi bağımsız değişkenlerini oluşturulur.Örneğin, tablo değerli işlev numaralarını virgülle ayrılmış bir dizi alır ve bunları bir tabloya pivots.
Dış veri oluşturulur.Örneğin, bir tablo değerli işlev olay günlüğünü okur ve onu bir tablo olarak sunar.

NotBir tablo değerli işlev bir üzerinden veri erişimi yalnızca gerçekleştirebilirsiniz Transact-SQL, sorgu InitMethodyöntem ve değil FillRowyöntem.The InitMethod should be marked with the SqlFunction.DataAccess.Read attribute property if a Transact-SQL query is performed.

Bir örnek tablo değerli işlev

Aşağıdaki tablo değerli işlev, sistem olay günlüğü ' bilgi verir.işlev Okumak için olay günlüğünü adını içeren bir tek dize baðýmsýz deðiþken alýr.

Örnek kod

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

Bildirmek ve örnek tablo biçiminde kullanmak işlev

Sonra örnek tablo değerli işlev derlenmiş, bunun bildirilenTransact-SQLaşağıdaki gibi:

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

Not

BaşlayarakSQL Server 2005, on a"80 oluşturamazsınız" uyumluluk düzey SQL Serververitabanını, kullanıcı tanımlı türler, saklı yordamlar, İşlevler, toplamları veya tetikleyici yönetiliyor. Bu CLR entegrasyonu özelliklerinden yararlanmak için SQL Server, kullanmanız gereken saklı yordamı "100" veritabanı uyumluluk düzeyini ayarlamak için sp_dbcmptlevel .

Not

İle /clr derlenmiş Visual C++ yönetilen veritabanı nesneleri: saf Visual C++ Derleyici seçeneği desteklenmiyor çalıştırılmak üzereSQL Server 2005.Örneğin, tablo değerli işlevler gibi veritabanı nesnelerini içerir.

Örnek, sınama aşağıdakileri deneyinTransact-SQLkodu:

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

Örnek: Bir SQL Server Query sonuçlar döndürülüyor

Aşağıdaki örnek, sorgu tablo değerli işlev gösterir birSQL Serververitabanı.Bu örnek AdventureWorks açık veritabanından kullanırSQL Server 2008.Bkz:https://www.codeplex.com/sqlserversamples daha fazla bilgi yükleme AdventureWorks.

FindInvalidEmails.cs veya FindInvalidEmails.vb kaynak kodu dosyası adı.

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")]
   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")> _
   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

Bir DLL dosyası için kaynak kodu derleyin ve DLL C sürücünüzün kök dizinine kopyalayın.Daha sonra aşağıdaki yürütmekTransact-SQLsorgu.

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

Değişiklik Geçmişi

Güncelleştirilmiş içerik
Bir SQL sorgusuna gösteren bir örnek eklendi.

Aracılığıyla paylaş