Artikel
03/30/2012

Aufrufen von CLR-benutzerdefinierten Aggregatfunktionen

In Transact-SQL SELECT-Anweisungen können Sie CLR-benuutzderdefinierte 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 registrieren wie folgt:

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

Hinweis
Ab SQL Server 2005 können Sie in einer SQL Server-Datenbank mit einem Kompatibilitätsgrad von "80" keine verwalteten benutzerdefinierten Typen, gespeicherten Prozeduren, Funktionen, Aggregate oder Trigger erstellen. Um diese CLR-Integrationsfunktionen von SQL Server nutzen zu können, müssen Sie mit der gespeicherten Prozedur sp_dbcmptlevel (Transact-SQL) den Kompatibilitätsgrad der Datenbank auf "90" festlegen.

Ab SQL Server 2005 können Sie in einer SQL Server-Datenbank mit einem Kompatibilitätsgrad von "80" keine verwalteten benutzerdefinierten Typen, gespeicherten Prozeduren, Funktionen, Aggregate oder Trigger erstellen. Um diese CLR-Integrationsfunktionen von SQL Server nutzen zu können, müssen Sie mit der gespeicherten Prozedur sp_dbcmptlevel (Transact-SQL) den Kompatibilitätsgrad der Datenbank auf "90" festlegen.

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')
INSERT BookAuthors VALUES(2, 'Taylor')
INSERT BookAuthors VALUES(3, 'Steven')
INSERT BookAuthors VALUES(2, 'Mayler')
INSERT BookAuthors VALUES(3, 'Roberts')
INSERT BookAuthors VALUES(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, Michael, 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)
INSERT INTO @myTable VALUES(6, 1)

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

Freigeben über

Aufrufen von CLR-benutzerdefinierten Aggregatfunktionen

Beispiel 1

Beispiel 2

Siehe auch

Konzepte

Andere Ressourcen

Zusätzliche Ressourcen