Condividi tramite

Richiamare funzioni di aggregazione CLR definite dall'utente

  • Articolo

Si applica a:SQL Server

Nelle istruzioni Transact-SQL SELECT è possibile richiamare aggregazioni CLR (Common Language Runtime) definite dall'utente, soggette a tutte le regole applicabili alle funzioni di aggregazione di sistema.

Sono valide le regole aggiuntive seguenti:

  • L'utente corrente deve disporre dell'autorizzazione EXECUTE per l'aggregazione definita dall'utente.

  • Le aggregazioni definite dall'utente devono essere richiamate usando un nome in due parti sotto forma di <schema_name>.<udagg_name>.

  • Il tipo di argomento dell'aggregazione definita dall'utente deve corrispondere o essere convertibile in modo implicito nella input_type dell'aggregazione, come definito nell'istruzione CREATE AGGREGATE.

  • Il tipo restituito dell'aggregazione definita dall'utente deve corrispondere al return_type nell'istruzione CREATE AGGREGATE.

Esempi

Un. Aggregazione definita dall'utente concatenando valori stringa

Il codice seguente è un esempio di una funzione di aggregazione definita dall'utente che concatena un set di valori stringa ricavati da una colonna in una tabella:

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

Dopo aver compilato il codice in MyAgg.dll, è possibile registrare l'aggregazione in SQL Server come indicato di seguito:

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

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

Nota

Gli oggetti di database di Visual C++, ad esempio funzioni con valori scalari, compilati con l'opzione del compilatore /clr:pure non sono supportati per l'esecuzione in SQL Server.

Come per la maggior parte delle aggregazioni, la maggior parte della logica si trova nel metodo Accumulate. In questo caso, la stringa passata come parametro al metodo Accumulate viene aggiunta all'oggetto StringBuilder inizializzato nel metodo Init. Supponendo che il metodo Accumulate non sia già stato chiamato, viene aggiunta anche una virgola al StringBuilder prima di accodare la stringa passata. Alla conclusione delle attività di calcolo viene chiamato il metodo Terminate, che restituisce il StringBuilder come stringa.

Si consideri, ad esempio, una tabella con lo schema seguente:

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

Inserire quindi le righe seguenti:

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

Tramite la query successiva verrebbe prodotto quindi il risultato seguente:

SELECT BookID, dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID;
BookID Author Names
1 Johnson
2 Taylor, Mayler
3 Roberts, Michaels, Steven

B. Aggregazione definita dall'utente con due parametri

L'esempio seguente mostra un'aggregazione con due parametri nel metodo 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;
        }
    }
}

Dopo aver compilato il codice sorgente C# o Visual Basic .NET, eseguire il codice transact-SQL seguente. Lo script presuppone che la DLL sia denominata WghtAvg.dll e si trovi nella directory radice dell'unità C. Si presuppone inoltre la presenza di un database denominato 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

Contenuto correlato