Partager via

Appeler des fonctions d’agrégation CLR définies par l’utilisateur

  • Article

S'applique à :SQL Server

Dans Transact-SQL SELECT instructions, vous pouvez appeler des agrégats CLR (Common Language Runtime) définis par l’utilisateur, 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 actuel doit disposer d’une 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 de <schema_name>.<udagg_name>.

  • Le type d’argument de l’agrégat défini par l’utilisateur doit correspondre ou être implicitement convertible en input_type de l’agrégat, comme défini dans l’instruction CREATE AGGREGATE.

  • Le type de retour de l’agrégat défini par l’utilisateur doit correspondre à la return_type dans l’instruction CREATE AGGREGATE.

Exemples

Un. Valeurs de chaîne concatènement définies par l’utilisateur

Le code suivant est un exemple de fonction d’agrégation définie par l’utilisateur qui concatène un ensemble de valeurs de chaîne provenant d’une colonne d’une table :

  • C#
  • Visual Basic .NET 
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());
    }
}

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

Les objets de base de données Visual C++, tels que les fonctions scalaires, qui ont été compilées avec l’option de compilateur /clr:pure ne sont pas pris en charge pour l’exécution dans SQL Server.

Comme avec la plupart des agrégats, la majeure partie de la logique se trouve dans la méthode Accumulate. Ici, la chaîne transmise en tant que paramètre à la méthode Accumulate est ajoutée à l’objet StringBuilder initialisé dans la méthode Init. En supposant que la méthode Accumulate n’était pas déjà appelée, une virgule est également ajoutée à l'StringBuilder avant d’ajouter la chaîne passée. À la fin des tâches de calcul, la méthode Terminate est appelée, qui retourne la StringBuilder sous forme de 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

B. Agrégat défini par l’utilisateur avec deux paramètres

L’exemple suivant montre un agrégat qui a deux paramètres sur la méthode Accumulate.

  • C#
  • Visual Basic .NET 
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;
        }
    }
}

Après avoir compilé le code source C# ou Visual Basic .NET, exécutez la commande 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

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

Contenu connexe