Статья
04/01/2012

Возвращающие табличное значение функции среды CLR

Функция с табличным значением представляет собой определяемую пользователем функцию, которая возвращает таблицу.

Начиная с SQL Server 2005, SQL Server расширяет возможности возвращающих табличное значение функций, позволяя определить возвращающую табличное значение функцию на любом управляемом языке. Возвращающая табличное значение функция возвращает данные через объект IEnumerable или IEnumerator.

Примечание
Для возвращающих табличное значение функций столбцы возвращаемого табличного типа не могут включать столбцы отметок времени и столбцы строкового типа данных не в Юникоде (например, char, varchar и text). Ограничение NOT NULL не поддерживается.

Для возвращающих табличное значение функций столбцы возвращаемого табличного типа не могут включать столбцы отметок времени и столбцы строкового типа данных не в Юникоде (например, char, varchar и text). Ограничение NOT NULL не поддерживается.

Различия между возвращающими табличное значение функциями Transact-SQL и среды CLR

Возвращающие табличное значение функции Transact-SQL материализуют результаты вызова в промежуточной таблице. По этой причине они поддерживают в результатах ограничения и уникальные индексы. Это исключительно полезно при возвращении результатов большого объема.

Возвращающие табличное значение функции CLR, напротив, представляют альтернативу в виде потока. Материализация всего результирующего набора в одной таблице не требуется. Объект IEnumerable, возвращаемый управляемой функцией, напрямую вызывается планом выполнения запроса, который вызывает возвращающую табличное значение функцию, а обработка результатов происходит по мере их получения. Такая потоковая модель обеспечивает немедленную обработку результатов сразу после получения первой строки, вместо того чтобы ожидать заполнения всей таблицы. Она также полезна, если возвращается очень большое число строк, поскольку таблица не материализуется в памяти как единое целое. Например, возвращающая табличное значение функция может использоваться для синтаксического анализа текстового файла и возвращения каждой строки текста в виде строки таблицы.

Реализация возвращающих табличное значение функций

Возвращающие табличное значение функции реализуются в виде методов класса в сборке Microsoft .NET Framework. В коде возвращающей табличное значение функции должен быть реализован интерфейс IEnumerable. Интерфейс IEnumerable определен в .NET Framework. Типы, представляющие массивы и коллекции в .NET Framework, уже реализованы в интерфейсе IEnumerable. Это облегчает написание возвращающих табличное значение функций, преобразующих коллекцию или массив в результирующий набор.

Параметры, возвращающие табличные значения

Возвращающие табличное значение параметры — это определяемые пользователем табличные типы, которые передаются в процедуру или функцию, предоставляя эффективный способ передачи на сервер нескольких строк данных. Возвращающие табличное значение параметры выполняют функции, аналогичные массивам параметров, но обладают большей гибкостью и лучше интегрируются с Transact-SQL. Они также обеспечивают возможность повышения производительности. Кроме того, возвращающие табличное значение параметры способствуют сокращению циклов приема-передачи данных с сервера и на сервер. Вместо того чтобы отправлять на сервер несколько запросов (как в случае списка скалярных параметров), данные можно отправить в виде возвращающего табличное значение параметра. Определяемый пользователем табличный тип нельзя передавать в виде возвращающего табличное значение параметра в управляемую хранимую процедуру или функцию, которая выполняется в процессе SQL Server. Кроме того, такие процедуры и функции не могут возвращать определяемые пользователем табличные типы. Дополнительные сведения о возвращающих табличное значение параметрах см. в разделе Возвращающие табличное значение параметры (компонент Database Engine).

Выходные параметры и возвращающие табличное значение функции

Возврат данных из возвращающей табличное значение функции может производиться через выходные параметры. Соответствующий аргумент в коде реализации возвращающей табличное значение функции должен передаваться по ссылке. Следует отметить, что язык 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);

Возвращающие табличное значение функции могут вернуть таблицу в следующих случаях.

Если они созданы из скалярных входных аргументов. Например, возвращающая табличное значение функция, принимающая строку чисел, разделенных запятыми, и преобразующая ее в таблицу.
Если они созданы из внешних данных. Например, возвращающая табличное значение функция, считывающая журнал событий и представляющая его в виде таблицы.

Примечание. Возвращающая табличное значение функция может производить доступ к данным только через запрос Transact-SQL через метод InitMethod, но не через метод FillRow. Метод InitMethod не должен быть помечен свойством атрибута SqlFunction.DataAccess.Read, если выполняется запрос Transact-SQL.

Образец возвращающей табличное значение функции

Следующая возвращающая табличное значение функция возвращает сведения из журнала системных событий. Функция принимает один строковый аргумент, содержащий имя журнала событий.

Образец кода

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 создание управляемых определяемых пользователем типов, хранимых процедур, функций, агрегатов и триггеров в базах данных SQL Server с уровнем совместимости 80 невозможно. Чтобы воспользоваться преимуществами этих функций интеграции SQL Server со средой CLR, необходимо с помощью хранимой процедуры sp_dbcmptlevel установить уровень совместимости базы данных, равный 100.

Выполнение объектов базы данных, разработанных в Visual C++ и скомпилированных с параметром /clr:pure, в SQL Server 2005 не поддерживается. Например, в число таких объектов базы данных входят возвращающие табличное значение функции.

Чтобы проверить образец, выполните следующий код 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. В этом образце используется база данных AdventureWorks2008R2. Дополнительные сведения о загрузке AdventureWorks2008R2 см. на веб-сайте 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 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 и скопируйте ее в корневой каталог диска 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

См. также

Основные понятия

Параметр Database Compatibility Level

Поделиться через