Megosztás a következőn keresztül:

CLR táblaértékelt függvények

A következőkre vonatkozik:SQL Server

A táblaértékelt függvény egy felhasználó által definiált függvény, amely egy táblát ad vissza.

Az SQL Server kibővíti a táblaértékű függvények funkcióit azáltal, hogy lehetővé teszi egy táblaértékű függvény definiálását bármely felügyelt nyelven. A függvény egy táblaértékű függvényből IEnumerable vagy IEnumerator objektumon keresztül adja vissza az adatokat.

Táblázatértékelt függvények esetén a visszaadott táblázattípus oszlopai nem tartalmazhatnak időbélyegoszlopokat vagy nem Unicode-sztring típusú oszlopokat (például karakteres, varcharés szöveges). A NOT NULL korlátozás nem támogatott.

A Transact-SQL és a CLR táblaértékelt függvények közötti különbségek

Transact-SQL táblaértékelt függvények a függvény köztes táblába való meghívásának eredményét eredményezik. Mivel köztes táblát használnak, támogathatják az eredményekre vonatkozó korlátozásokat és egyedi indexeket. Ezek a funkciók nagy eredmények visszaadásakor hasznosak lehetnek.

Ezzel szemben a közös nyelvi futtatókörnyezet (CLR) táblaértékes függvényei streamelési alternatívát jelentenek. Nincs szükség arra, hogy az eredmények teljes készlete egyetlen táblában valósuljon meg. A felügyelt függvény által visszaadott IEnumerable objektumot közvetlenül a táblaértékű függvényt meghívó lekérdezés végrehajtási terve hívja meg, és az eredményeket növekményes módon használja fel a rendszer. Ez a streamelési modell biztosítja, hogy az eredmények azonnal felhasználhatók az első sor rendelkezésre állása után, ahelyett, hogy a teljes táblázat feltöltésére várnak. Az is jobb alternatíva, ha nagy számú sort ad vissza, mert nem kell a memóriában egészében materializálni őket. Egy felügyelt táblaértékű függvény például használható egy szövegfájl elemzésére, és sorként adja vissza az egyes sorokat.

Táblaértékű függvények implementálása

Táblaértékű függvények implementálása metódusként egy osztályon egy .NET-keretrendszer-szerelvényben. A táblaértékű függvénykódnak implementálnia kell a IEnumerable felületet. A IEnumerable felület a .NET-keretrendszerben van definiálva. A .NET-keretrendszer tömbeit és gyűjteményeit képviselő típusok már implementálják a IEnumerable felületet. Ez megkönnyíti a gyűjteményt vagy tömböt eredményhalmazsá konvertáló táblaértékű függvények írását.

Táblaértékkel megadott paraméterek

A táblaértékkel rendelkező paraméterek olyan felhasználó által definiált táblatípusok, amelyek egy eljárásba vagy függvénybe kerülnek, és hatékony módot nyújtanak több adatsor kiszolgálónak való átadására. A táblaértékű paraméterek hasonló funkciókat biztosítanak a paramétertömbökhöz, de nagyobb rugalmasságot és szorosabb integrációt biztosítanak a Transact-SQL-vel. Emellett a jobb teljesítmény lehetőségét is biztosítják.

A táblaértékelt paraméterek a kiszolgálóra történő ciklikus utazások számának csökkentésében is segítenek. Ahelyett, hogy több kérést küldene a kiszolgálónak, például a skaláris paraméterek listájával, az adatok táblaértékű paraméterként küldhetők el a kiszolgálónak. A felhasználó által definiált táblatípus nem adható át táblaértékkel rendelkező paraméterként egy felügyelt tárolt eljárásnak vagy függvénynek, amely az SQL Server-folyamatban fut. A táblaértékű paraméterekről további információt a Táblaértékű paraméterek használata (Adatbázismotor)című témakörben talál.

Kimeneti paraméterek és táblaértékű függvények

A táblaértékelt függvények kimeneti paraméterekkel adhatják vissza az információkat. A implementálási kód táblaértékes függvény megfelelő paraméterének argumentumként egy pass-by-reference paramétert kell használnia. A Visual Basic .NET nem támogatja a kimeneti paramétereket ugyanúgy, mint a C# . A paramétert hivatkozással kell megadnia, és az <Out()> attribútumot kell alkalmaznia egy kimeneti paraméter megjelenítéséhez, ahogyan az alábbi példában is látható:

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

Táblaértékű függvény definiálása Transact-SQL

A CLR táblaértékű függvények definiálásának szintaxisa hasonló egy Transact-SQL táblaértékű függvényhez, a EXTERNAL NAME záradék hozzáadásával. Például:

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

A táblaértékű függvények relációs formában jelenítik meg az adatokat a lekérdezések további feldolgozásához, például:

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

A táblaértékelt függvények akkor adnak vissza táblát, ha:

  • Skaláris bemeneti argumentumokból jön létre. Például egy táblaértékű függvény, amely egy vesszővel tagolt számsztringet használ, és egy táblába forgatja őket.

  • Külső adatokból jön létre. Például egy táblaértékű függvény, amely beolvassa az eseménynaplót, és táblaként teszi elérhetővé.

Jegyzet

A táblaértékű függvények csak Transact-SQL lekérdezésen keresztül végezhetnek adathozzáférést a InitMethod metódusban, a FillRow metódusban nem. Ha Transact-SQL lekérdezést végez, a InitMethod a SqlFunction.DataAccess.Read attribútum tulajdonsággal kell megjelölni.

Táblaértékű mintafüggvény

A következő táblaértékű függvény a rendszer eseménynaplójából ad vissza információkat. A függvény egyetlen sztringargumentumot használ, amely az eseménynapló nevét tartalmazza az olvasáshoz.

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

Deklarálja és használja a táblaértékű mintafüggvényt

A táblaértékű mintafüggvény fordítása után az alábbi módon deklarálható 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

A /clr:pure lefordított Visual C++ adatbázis-objektumok nem támogatottak az SQL Serveren való végrehajtáshoz. Az ilyen adatbázis-objektumok közé tartoznak például a táblaértékelt függvények.

A minta teszteléséhez próbálkozzon a következő Transact-SQL kóddal:

-- 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éldák

A cikkben szereplő kódminták a AdventureWorks2025 vagy AdventureWorksDW2025 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

Egy. SQL Server-lekérdezés eredményeinek visszaadása

Az alábbi példa egy SQL Server-adatbázist lekérdező táblaértékű függvényt mutat be.

Nevezze el a forráskódfájlt FindInvalidEmails.cs vagy 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;
   }
};

Fordítsa le a forráskódot egy DLL-fájlra, és másolja a DLL-t a C meghajtó gyökérkönyvtárába. Ezután hajtsa végre a következő Transact-SQL lekérdezést.

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