Udostępnij za pośrednictwem

Wywoływanie funkcji agregujących zdefiniowanych przez użytkownika środowiska CLR

  • Artykuł

Dotyczy:programu SQL Server

W Transact-SQL SELECT instrukcji można wywołać agregacje zdefiniowane przez użytkownika środowiska uruchomieniowego języka wspólnego (CLR) z uwzględnieniem wszystkich reguł, które mają zastosowanie do funkcji agregacji systemu.

Obowiązują następujące dodatkowe reguły:

  • Bieżący użytkownik musi mieć EXECUTE uprawnienia do agregacji zdefiniowanej przez użytkownika.

  • Agregacje zdefiniowane przez użytkownika muszą być wywoływane przy użyciu dwuczęściowej nazwy w postaci <schema_name>.<udagg_name>.

  • Typ argumentu agregacji zdefiniowanej przez użytkownika musi być zgodny lub niejawnie konwertowany na input_type agregacji, zgodnie z definicją w instrukcji CREATE AGGREGATE.

  • Zwracany typ agregacji zdefiniowanej przez użytkownika musi być zgodny z return_type w instrukcji CREATE AGGREGATE.

Przykłady

A. Agregujące wartości ciągów zdefiniowane przez użytkownika

Poniższy kod to przykład funkcji agregującej zdefiniowanej przez użytkownika, która łączy zestaw wartości ciągu pobranych z kolumny w tabeli:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property
    MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
    /// <summary>
    /// The variable that holds the intermediate result of the concatenation
    /// </summary>
    public StringBuilder intermediateResult;

    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        this.intermediateResult = new StringBuilder();
    }

    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="value"></param>
    public void Accumulate(SqlString value)
    {
        if (value.IsNull)
        {
            return;
        }

        this.intermediateResult.Append(value.Value).Append(',');
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate.
    /// </summary>
    /// <param name="other"></param>
    public void Merge(Concatenate other)
    {
        this.intermediateResult.Append(other.intermediateResult);
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        string output = string.Empty;
        //delete the trailing comma, if any
        if (this.intermediateResult != null
            && this.intermediateResult.Length > 0)
        {
            output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
}

Po skompilowaniu kodu do MyAgg.dllmożna zarejestrować agregację w programie SQL Server w następujący sposób:

CREATE ASSEMBLY MyAgg
    FROM 'C:\MyAgg.dll';
GO

CREATE AGGREGATE MyAgg(@input NVARCHAR (200))
    RETURNS NVARCHAR (MAX)
    EXTERNAL NAME MyAgg.Concatenate;

Nuta

Obiekty bazy danych Visual C++, takie jak funkcje skalarne, które zostały skompilowane przy użyciu opcji kompilatora /clr:pure, nie są obsługiwane do wykonywania w programie SQL Server.

Podobnie jak w przypadku większości agregacji, większość logiki znajduje się w metodzie Accumulate. W tym miejscu ciąg przekazywany jako parametr do metody Accumulate jest dołączany do obiektu StringBuilder, który został zainicjowany w metodzie Init. Zakładając, że metoda Accumulate nie została jeszcze wywołana, przecinek jest również dołączany do StringBuilder przed dołączeniem przekazanego ciągu. Na zakończenie zadań obliczeniowych wywoływana jest metoda Terminate, która zwraca StringBuilder jako ciąg.

Rozważmy na przykład tabelę z następującym schematem:

CREATE TABLE BookAuthors
(
    BookID INT NOT NULL,
    AuthorName NVARCHAR (200) NOT NULL
);

Następnie wstaw następujące wiersze:

INSERT BookAuthors
VALUES
    (1, 'Johnson'),
    (2, 'Taylor'),
    (3, 'Steven'),
    (2, 'Mayler'),
    (3, 'Roberts'),
    (3, 'Michaels');

Następujące zapytanie spowoduje wygenerowanie następującego wyniku:

SELECT BookID, dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID;
Identyfikator książki Nazwy autorów
1 Johnson
2 Taylor, Mayler
3 Roberts, Michaels, Steven

B. Agregacja zdefiniowana przez użytkownika z dwoma parametrami

W poniższym przykładzie przedstawiono agregację zawierającą dwa parametry metody Accumulate.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(
    Format.Native,
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = true,
    IsInvariantToOrder = true,
    IsNullIfEmpty = true,
    Name = "WeightedAvg")]
public struct WeightedAvg
{
    /// <summary>
    /// The variable that holds the intermediate sum of all values multiplied by their weight
    /// </summary>
    private long sum;

    /// <summary>
    /// The variable that holds the intermediate sum of all weights
    /// </summary>
    private int count;

    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        sum = 0;
        count = 0;
    }

    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="Value">Next value to be aggregated</param>
    /// <param name="Weight">The weight of the value passed to Value parameter</param>
    public void Accumulate(SqlInt32 Value, SqlInt32 Weight)
    {
        if (!Value.IsNull && !Weight.IsNull)
        {
            sum += (long)Value * (long)Weight;
            count += (int)Weight;
        }
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate
    /// </summary>
    /// <param name="Group">The other partial results to be merged</param>
    public void Merge(WeightedAvg Group)
    {
        sum += Group.sum;
        count += Group.count;
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns>The weighted average of all inputed values</returns>
    public SqlInt32 Terminate()
    {
        if (count > 0)
        {
            int value = (int)(sum / count);
            return new SqlInt32(value);
        }
        else
        {
            return SqlInt32.Null;
        }
    }
}

Po skompilowaniu kodu źródłowego języka C# lub Visual Basic platformy .NET uruchom następujący kod języka Transact-SQL. Ten skrypt zakłada, że biblioteka DLL jest nazywana WghtAvg.dll i znajduje się w katalogu głównym dysku C. Zakłada się również bazę danych o nazwie test.

USE test;
GO

-- EXECUTE sp_configure 'clr enabled', 1;
-- RECONFIGURE WITH OVERRIDE;
-- GO
IF EXISTS (SELECT name
           FROM systypes
           WHERE name = 'MyTableType')
    DROP TYPE MyTableType;
GO

IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name = 'WeightedAvg')
    DROP AGGREGATE WeightedAvg;
GO

IF EXISTS (SELECT name
           FROM sys.assemblies
           WHERE name = 'MyClrCode')
    DROP ASSEMBLY MyClrCode;
GO

CREATE ASSEMBLY MyClrCode
    FROM 'C:\WghtAvg.dll';
GO

CREATE AGGREGATE WeightedAvg(@value INT, @weight INT)
    RETURNS INT
    EXTERNAL NAME MyClrCode.WeightedAvg;
GO

CREATE TYPE MyTableType AS TABLE (
    ItemValue INT,
    ItemWeight INT);
GO

DECLARE @myTable AS MyTableType;

INSERT INTO @myTable
VALUES (1, 4),
(6, 1);

SELECT dbo.WeightedAvg(ItemValue, ItemWeight)
FROM @myTable;
GO

Powiązana zawartość