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

Функции с табличным значением среды CLR

  • Статья

Область применения:SQL Server

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

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

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

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

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

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

Реализация табличных функций

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

Параметры с табличным значением

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

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

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

Сведения могут быть возвращены из табличных функций с помощью выходных параметров. Соответствующий аргумент в коде реализации функции с табличным значением должен передаваться по ссылке. Visual Basic .NET не поддерживает выходные параметры таким же образом, как и 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)
)
AS EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname].GetEmpFirstLastNames;

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

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

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

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

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

Примечание.

Табличное значение функции может выполнять доступ только к данным через запрос 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;
    }
}

Объявление и использование примера табличного значения функции

После компиляции примера табличной функции его можно объявить в 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

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

Чтобы протестировать пример, попробуйте использовать следующий код 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

Примеры

Примеры кода Transact-SQL в этой статье используют пример базы данных AdventureWorks2022 или AdventureWorksDW2022, которую можно скачать на домашней странице примеров Microsoft SQL Server и проектов сообщества.

A. Возврат результатов запроса SQL Server

В следующем примере показана табличное значение функция, которая запрашивает базу данных SQL Server.

Задайте для файла исходного кода имя 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 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;
   }
};

Скомпилируйте исходный код в библиотеку DLL и скопируйте ее в корневой каталог диска C. Затем выполните следующий запрос Transact-SQL.

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