Aufrufen von CLR-benutzerdefinierten Aggregatfunktionen

Artikel
05/07/2013

In Transact-SQL SELECT-Anweisungen können Sie CLR-benutzderdefinierte Aggregate (Common Language Runtime, CLR) aufrufen, für die dieselben Regeln gelten, wie für systembasierte Aggregatfunktionen.

Es gelten folgende zusätzliche Regeln:

Der aktuelle Benutzer muss über die EXECUTE-Berechtigung für das benutzerdefinierte Aggregat verfügen.
Benutzerdefinierte Aggregate müssen mit einem zweiteiligen Namen im Format von schema_name.udagg_name aufgerufen werden.
Der Argumenttyp des benutzerdefinierten Aggregats muss dem input_type des Aggregats entsprechen oder implizit in diesen Typ verwandelt werden können, wie in der CREATE AGGREGATE-Anweisung definiert.
Der Rückgabetyp des benutzerdefinierten Aggregats muss dem in der CREATE AGGREGATE-Anweisung definierten return_type entsprechen.

Beispiel 1

Es folgt ein Beispiel einer benutzerdefinierten Aggregatfunktion, die eine Reihe von aus einer Tabellenspalte entnommenen Zeichenfolgenwerten verkettet:

[C#]

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>
    private 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());
    }
}

[Visual Basic]

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Text

<Serializable(), SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=8000)> _
Public Class Concatenate
    Implements IBinarySerialize

    ''' <summary>
    ''' The variable that holds the intermediate result of the concatenation
    ''' </summary>
    Private intermediateResult As StringBuilder

    ''' <summary>
    ''' Initialize the internal data structures
    ''' </summary>
    Public Sub Init()
        Me.intermediateResult = New StringBuilder()
    End Sub

    ''' <summary>
    ''' Accumulate the next value, not if the value is null
    ''' </summary>
    ''' <param name="value"></param>
    Public Sub Accumulate(ByVal value As SqlString)
        If value.IsNull Then
            Return
        End If

        Me.intermediateResult.Append(value.Value).Append(","c)
    End Sub
    ''' <summary>
    ''' Merge the partially computed aggregate with this aggregate.
    ''' </summary>
    ''' <param name="other"></param>
    Public Sub Merge(ByVal other As Concatenate)
        Me.intermediateResult.Append(other.intermediateResult)
    End Sub

    ''' <summary>
    ''' Called at the end of aggregation, to return the results of the aggregation.
    ''' </summary>
    ''' <returns></returns>
    Public Function Terminate() As SqlString
        Dim output As String = String.Empty

        'delete the trailing comma, if any
        If Not (Me.intermediateResult Is Nothing) AndAlso Me.intermediateResult.Length > 0 Then
            output = Me.intermediateResult.ToString(0, Me.intermediateResult.Length - 1)
        End If

        Return New SqlString(output)
    End Function

    Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read
        intermediateResult = New StringBuilder(r.ReadString())
    End Sub

    Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write
        w.Write(Me.intermediateResult.ToString())
    End Sub
End Class

Sobald Sie den Code in MyAgg.dll kompilieren, können Sie das Aggregat in SQL Server wie folgt registrieren:

CREATE ASSEMBLY MyAgg FROM 'C:\MyAgg.dll';
GO
CREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME MyAgg.Concatenate;

Hinweis
Visual C++-Datenbankobjekte wie Skalarwertfunktionen, die mit der Compileroption /clr:pure kompiliert wurden, werden für die Ausführung in SQL Server nicht unterstützt.

Wie bei den meisten Aggregaten befindet sich der Großteil der Logik in der Accumulate-Methode. Hier wird die Zeichenfolge, die als Parameter an die Accumulate-Methode übergeben wird, an das StringBuilder-Objekt angefügt, welches in der Init-Methode initialisiert wurde. Angenommen, die Accumulate-Methode wird nicht zum ersten Mal aufgerufen, wird vor dem Anfügen der übergebenen Zeichenfolge dem StringBuilder-Objekt außerdem ein Komma angefügt. Nach Abschluss der Berechnungstasks wird die Terminate-Methode aufgerufen, die das StringBuilder-Objekt als Zeichenfolge zurückgibt.

Angenommen, eine Tabelle mit folgendem Schema liegt vor:

CREATE TABLE BookAuthors
(
   BookID   int       NOT NULL,
   AuthorName    nvarchar(200) NOT NULL
);

Fügen Sie dann die folgenden Zeilen ein:

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

Die folgende Abfrage würde dann zum folgenden Ergebnis führen:

SELECT BookID, dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID;

BookID	Author Names
1	Johnson
2	Taylor, Mayler
3	Roberts, Michaels, Steven

Beispiel 2

Im folgenden Beispiel wird ein Aggregat dargestellt, das in der Accumulate-Methode über zwei Parameter verfügt.

[C#]

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

[Visual Basic]

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices

<StructLayout(LayoutKind.Sequential)> _
<Serializable(), SqlUserDefinedAggregate(Format.Native, _
IsInvariantToDuplicates:=False, _
IsInvariantToNulls:=True, _
IsInvariantToOrder:=True, _
IsNullIfEmpty:=True, _
Name:="WeightedAvg")> _
Public Class WeightedAvg

    ''' <summary>
    ''' The variable that holds the intermediate sum of all values multiplied by their weight
    ''' </summary>
    Private sum As Long

    ''' <summary>
    ''' The variable that holds the intermediate sum of all weights
    ''' </summary>
    Private count As Integer

    ''' <summary>
    ''' The variable that holds the intermediate sum of all weights
    ''' </summary>
    Public Sub Init()
        sum = 0
        count = 0
    End Sub

    ''' <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 Sub Accumulate(ByVal Value As SqlInt32, ByVal Weight As SqlInt32)
        If Not Value.IsNull AndAlso Not Weight.IsNull Then
            sum += CType(Value, Long) * CType(Weight, Long)
            count += CType(Weight, Integer)
        End If
    End Sub

    ''' <summary>
    ''' Merge the partially computed aggregate with this aggregate.
    ''' </summary>
    ''' <param name="Group">The other partial results to be merged</param>
    Public Sub Merge(ByVal Group As WeightedAvg)
        sum = Group.sum
        count = Group.count
    End Sub

    ''' <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 Function Terminate() As SqlInt32
        If count > 0 Then
            ''                        int value = (int)(sum / count);
            ''          return new SqlInt32(value);
            Dim value As Integer = CType(sum / count, Integer)
            Return New SqlInt32(value)
        Else
            Return SqlInt32.Null
        End If
    End Function
End Class

Führen Sie nach dem Kompilieren des C#- oder Visual Basic-Quellcodes das folgende Transact-SQL aus. In diesem Skript wird vorausgesetzt, dass die DLL WghtAvg.dll heißt und sich im Stammverzeichnis von Laufwerk C: befindet. Außerdem muss die Datenbank test vorhanden sein.

use test;
go

-- sp_configure 'clr enabled', 1;
-- go

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

Siehe auch

Andere Ressourcen

Benutzerdefinierte CLR-Aggregate

Freigeben über

Aufrufen von CLR-benutzerdefinierten Aggregatfunktionen

Beispiel 1

Beispiel 2

Siehe auch

Andere Ressourcen

Zusätzliche Ressourcen