Appel de fonctions d'agrégation CLR définies par l'utilisateur

Article
05/07/2013

Dans les instructions SELECT Transact-SQL, vous pouvez appeler les agrégats CLR (Common Language Runtime) définis par l'utilisateur et soumis à toutes les règles qui s'appliquent aux fonctions d'agrégation système.

Les règles suivantes supplémentaires s'appliquent :

L'utilisateur en cours doit disposer de l'autorisation EXECUTE sur l'agrégat défini par l'utilisateur.
Les agrégats définis par l'utilisateur doivent être appelés à l'aide d'un nom en deux parties, sous la forme nom_schéma.nom_udagg.
Le type d'argument de l'agrégat défini par l'utilisateur doit correspondre à l'input_type de l'agrégat (ou pouvoir implicitement y être converti), comme défini dans l'instruction CREATE AGGREGATE.
Le type de retour de l'agrégat défini par l'utilisateur doit correspondre au return_type de l'instruction CREATE AGGREGATE.

Exemple 1

Voici un exemple de fonction d'agrégation définie par l'utilisateur qui concatène un jeu de valeurs de chaîne extraites d'une colonne de table :

[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

Une fois que vous avez compilé le code dans MyAgg.dll, vous pouvez inscrire l'agrégat dans SQL Server comme suit :

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

[!REMARQUE]

L'exécution des objets de base de données Visual C++, tels que les fonctions scalaires, qui ont été compilés avec l'option /clr:pure du compilateur, n'est pas prise en charge dans SQL Server.

Comme dans le cas de la plupart des agrégats, l'essentiel de la logique se trouve dans la méthode Accumulate. Ici, la chaîne transmise comme paramètre à la méthode Accumulate vient s'ajouter à l'objet StringBuilder initialisé dans la méthode Init. En supposant qu'il ne s'agit pas du premier appel de la méthode Accumulate, une virgule est également ajoutée à l'objet StringBuilder avant l'ajout de la chaîne transmise. À la fin des opérations de calcul, la méthode Terminate est appelée et retourne StringBuilder en tant que chaîne.

Imaginons, par exemple, une table avec le schéma suivant :

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

Puis, insérons les lignes suivantes :

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

La requête suivante génère alors le résultat suivant :

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

BookID	Noms d'auteur
1	Johnson
2	Taylor, Mayler
3	Roberts, Michaels, Steven

Exemple 2

L'exemple suivant illustre un agrégat avec deux paramètres sur la méthode Accumulate.

[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

Après avoir compilé le code source C# ou Visual Basic, exécutez la requête Transact-SQL suivante. Ce script part du principe que la DLL est appelée WghtAvg.dll et figure dans le répertoire racine de votre lecteur C. Une base de données appelée test est également prise en compte.

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

Voir aussi

Autres ressources

Agrégats CLR définis par l'utilisateur

Partager via

Appel de fonctions d'agrégation CLR définies par l'utilisateur

Exemple 1

Exemple 2

Voir aussi

Autres ressources

Ressources supplémentaires