Sdílet prostřednictvím

Funkce s hodnotami tabulky CLR

  • Článek

platí pro:SQL Server

Funkce s hodnotou tabulky je uživatelem definovaná funkce, která vrací tabulku.

SQL Server rozšiřuje funkce tabulkových funkcí tím, že umožňuje definovat funkci s hodnotou tabulky v libovolném spravovaném jazyce. Data se vrací z funkce s hodnotou tabulky prostřednictvím IEnumerable nebo IEnumerator objektu.

U funkcí s hodnotou tabulky nemohou sloupce návratového typu tabulky obsahovat sloupce časového razítka ani sloupce s datovým typem řetězce jiného typu než Unicode (například znakové, varchara textové). Omezení NOT NULL se nepodporuje.

Rozdíly mezi funkcemi Transact-SQL a tabulkovými funkcemi CLR

Transact-SQL tabulkové funkce materializují výsledky volání funkce do zprostředkující tabulky. Vzhledem k tomu, že používají zprostředkující tabulku, můžou podporovat omezení a jedinečné indexy ve výsledcích. Tyto funkce můžou být užitečné, když se vrátí velké výsledky.

Naproti tomu funkce modulu CLR (Common Language Runtime) v tabulce představují alternativu streamování. Není nutné, aby celá sada výsledků byla materializována v jedné tabulce. Objekt IEnumerable vrácený spravovanou funkcí je volána přímo plánem provádění dotazu, který volá funkci s hodnotou tabulky a výsledky se spotřebovávají přírůstkově. Tento model streamování zajišťuje, aby se výsledky mohly využívat hned po prvním řádku a nečekaly na naplnění celé tabulky. Je také lepší alternativou, pokud máte velký počet vrácených řádků, protože nemusí být materializovány v paměti jako celek. Například spravovaná funkce hodnotná tabulkou se dá použít k analýze textového souboru a vrácení každého řádku jako řádku.

Implementace funkcí s hodnotami tabulky

Implementujte funkce hodnotné tabulkou jako metody třídy v sestavení .NET Framework. Kód funkce s hodnotou tabulky musí implementovat rozhraní IEnumerable. Rozhraní IEnumerable je definováno v rozhraní .NET Framework. Typy představující pole a kolekce v rozhraní .NET Framework již implementují rozhraní IEnumerable. To usnadňuje zápis funkcí s hodnotami tabulky, které převádějí kolekci nebo pole na sadu výsledků.

Parametry s hodnotami tabulky

Parametry hodnotné tabulkou jsou uživatelem definované typy tabulek, které se předávají do procedury nebo funkce a poskytují efektivní způsob předávání více řádků dat na server. Parametry s hodnotou tabulky poskytují podobné funkce jako pole parametrů, ale nabízejí větší flexibilitu a užší integraci s Jazykem Transact-SQL. Poskytují také potenciál pro lepší výkon.

Parametry s hodnotou tabulky také pomáhají snížit počet odezvy na server. Místo odesílání více požadavků na server, například se seznamem skalárních parametrů, je možné data odeslat na server jako parametr s hodnotou tabulky. Uživatelem definovaný typ tabulky nelze předat jako parametr s hodnotou tabulky nebo ho vrátit ze spravované uložené procedury nebo funkce spuštěné v procesu SQL Serveru. Další informace o parametrech s hodnotami tabulky najdete v tématu Použití parametrů hodnot tabulky (databázový stroj).

Výstupní parametry a funkce s hodnotami tabulky

Informace můžou být vráceny z funkcí s hodnotou tabulky pomocí výstupních parametrů. Odpovídající parametr ve funkci table-valued kódu implementace by měl jako argument použít parametr pass-by-reference. Visual Basic .NET nepodporuje výstupní parametry stejným způsobem jako jazyk C#. Parametr je nutné zadat pomocí odkazu a použít atribut <Out()> k reprezentaci výstupního parametru, jak je znázorněno v následujícím příkladu:

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

Definování funkce s hodnotou tabulky v Transact-SQL

Syntaxe pro definování funkce s hodnotou tabulky CLR je podobná funkci Transact-SQL tabulkové hodnotě s přidáním klauzule EXTERNAL NAME. Například:

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

Funkce s hodnotou tabulky se používají k reprezentaci dat v relační podobě pro další zpracování v dotazech, jako jsou:

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

Funkce s hodnotou tabulky můžou vracet tabulku, když:

  • Vytvořeno ze skalárních vstupních argumentů Například funkce s hodnotami tabulky, která přebírá řetězec čísel oddělených čárkami a přetáčí je do tabulky.

  • Generuje se z externích dat. Například funkce s hodnotou tabulky, která čte protokol událostí a zveřejňuje ji jako tabulku.

Poznámka

Funkce s hodnotou tabulky může provádět přístup k datům pouze prostřednictvím Transact-SQL dotazu v metodě InitMethod, a ne v metodě FillRow. Pokud se provede dotaz Transact-SQL, měl by být InitMethod označen vlastností atributu SqlFunction.DataAccess.Read.

Ukázková funkce s hodnotou tabulky

Následující funkce s hodnotou tabulky vrátí informace z protokolu událostí systému. Funkce přebírá jeden řetězcový argument obsahující název protokolu událostí ke čtení.

Ukázkový kód

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

Deklarace a použití ukázkové funkce s hodnotou tabulky

Po kompilaci ukázkové funkce s hodnotami tabulky je možné ji deklarovat v Transact-SQL takto:

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

Databázové objekty Visual C++ kompilované pomocí /clr:pure nejsou podporovány pro spouštění na SQL Serveru. Například takové databázové objekty zahrnují funkce s hodnotou tabulky.

Pokud chcete ukázku otestovat, vyzkoušejte následující Transact-SQL kód:

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

Příklady

Ukázky kódu Transact-SQL v tomto článku používají AdventureWorks2022 nebo AdventureWorksDW2022 ukázkovou databázi, kterou si můžete stáhnout z domovské stránky ukázky Microsoft SQL Serveru a projekty komunity.

A. Vrácení výsledků dotazu SQL Serveru

Následující ukázka ukazuje funkci s hodnotou tabulky, která se dotazuje na databázi SQL Serveru.

Pojmenujte soubor zdrojového kódu FindInvalidEmails.cs nebo 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 [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 emailAdress) {
      EmailResult emailResult = (EmailResult)emailResultObj;

      customerId = emailResult.CustomerId;
      emailAdress = emailResult.EmailAdress;
   }
};

Zkompilujte zdrojový kód do knihovny DLL a zkopírujte knihovnu DLL do kořenového adresáře jednotky C. Pak spusťte následující Transact-SQL dotaz.

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