Delen via

Door de gebruiker gedefinieerde clr-functies aanroepen

  • Artikel

van toepassing op:SQL Server-

In Transact-SQL SELECT-instructies kunt u door de gebruiker gedefinieerde aggregaties (Common Language Runtime) aanroepen, afhankelijk van alle regels die van toepassing zijn op statistische functies van het systeem.

De volgende aanvullende regels zijn van toepassing:

  • De huidige gebruiker moet EXECUTE machtiging hebben voor de door de gebruiker gedefinieerde statistische functie.

  • Door de gebruiker gedefinieerde aggregaties moeten worden aangeroepen met behulp van een tweedelige naam in de vorm van <schema_name>.<udagg_name>.

  • Het argumenttype van de door de gebruiker gedefinieerde aggregatie moet overeenkomen met of impliciet worden omgezet in de input_type van de aggregatie, zoals gedefinieerd in de CREATE AGGREGATE-instructie.

  • Het retourtype van de door de gebruiker gedefinieerde aggregatie moet overeenkomen met de return_type in de CREATE AGGREGATE-instructie.

Voorbeelden

Een. Door de gebruiker gedefinieerde samenvoeging van tekenreekswaarden

De volgende code is een voorbeeld van een door de gebruiker gedefinieerde statistische functie waarmee een reeks tekenreekswaarden uit een kolom in een tabel worden samengevoegd:

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

Zodra u de code hebt gecompileerd in MyAgg.dll, kunt u de statistische functie als volgt registreren in SQL Server:

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

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

Notitie

Visual C++-databaseobjecten, zoals scalaire functies, die zijn gecompileerd met de optie /clr:pure compiler worden niet ondersteund voor uitvoering in SQL Server.

Net als bij de meeste aggregaties bevindt het grootste deel van de logica zich in de Accumulate methode. Hier wordt de tekenreeks die wordt doorgegeven als een parameter aan de Accumulate-methode, toegevoegd aan het StringBuilder-object dat is geïnitialiseerd in de Init methode. Ervan uitgaande dat de methode Accumulate nog niet is aangeroepen, wordt er ook een komma toegevoegd aan de StringBuilder voordat u de doorgegeven tekenreeks toevoegt. Aan het einde van de rekentaken wordt de Terminate methode aangeroepen, die de StringBuilder als een tekenreeks retourneert.

Denk bijvoorbeeld aan een tabel met het volgende schema:

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

Voeg vervolgens de volgende rijen in:

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

Met de volgende query wordt vervolgens het volgende resultaat geproduceerd:

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

B. Door de gebruiker gedefinieerde aggregaties met twee parameters

In het volgende voorbeeld ziet u een aggregaties met twee parameters op de Accumulate methode.

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

Nadat u de C# of Visual Basic .NET-broncode hebt gecompileerd, voert u de volgende Transact-SQL uit. In dit script wordt ervan uitgegaan dat het DLL-bestand wordt aangeroepen WghtAvg.dll en zich in de hoofdmap van uw C-station bevindt. Er wordt ook uitgegaan van een database met de naam 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

Verwante inhoud