Поделиться через

  • Статья

Вызов определяемых пользователем статистических функций CLR

В инструкциях Transact-SQL SELECT можно вызывать пользовательские статистические функции CLR, на которые распространяются те же правила, что и на системные статистические функции.

Применяются следующие дополнительные правила:

  • Текущий пользователь должен иметь разрешение EXECUTE на пользовательскую статистическую функцию.

  • Пользовательские статистические функции можно вызывать с помощью двусоставного имени в следующей форме: schema_name.udagg_name.

  • Тип аргумента пользовательской статистической функции должен совпадать с input_type статистической функции, как определено в инструкции CREATE AGGREGATE, или неявно преобразовываться в него.

  • Тип данных, возвращаемых пользовательской статистической функцией, должен совпадать с return_type в инструкции CREATE AGGREGATE.

Ниже приводится пример определяемой пользователем статистической функции, объединяющей набор строковых значений из столбца таблицы.

[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 SystemImports System.DataImports Microsoft.SqlServer.ServerImports System.Data.SqlTypesImports System.IOImports 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 SubEnd Class

После компиляции кода в библиотеку MyAgg.dll можно зарегистрировать статистическую функцию в SQL Server показанным далее образом.

CREATE ASSEMBLY MyAgg FROM 'C:\MyAgg.dll'GOCREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max)EXTERNAL NAME MyAgg.Concatenate
ПримечаниеПримечание

Начиная с версии SQL Server 2005 в базе данных SQL Server с уровнем совместимости 80 нельзя создавать управляемые определяемые пользователем типы, хранимые процедуры, функции, статистические функции и триггеры. Чтобы сделать эти функции интеграции со средой CLR доступными в SQL Server, необходимо при помощи хранимой процедуры sp_dbcmptlevel (Transact-SQL) задать для базы данных уровень совместимости 90.
ПримечаниеПримечание

Управляемые объекты базы данных C++, такие как функции, возвращающие скалярные значения, скомпилированные с помощью параметра компиляции /clr:pure Visual C++, не поддерживаются и не выполняются в SQL Server 2005 RTM.

Как и для большинства статистических функций, основная логика содержится в методе Accumulate. Здесь строка, передаваемая в параметре методу Accumulate, присоединяется к объекту StringBuilder, инициализированному в методе Init. Если метод Accumulate вызывается не в первый раз, к объекту StringBuilder также присоединяется запятая перед передаваемой строкой. По завершении вычислительных задач вызывается метод Terminate, возвращающий объект StringBuilder в виде строки.

Предположим, что таблица имеет следующую структуру.

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

Затем вставляются следующие строки.

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

В результате следующего запроса будет получен следующий результат.

SELECT BookID, dbo.MyAgg(AuthorName)FROM BookAuthorsGROUP BY BookID

Идентификатор книги

Имена авторов

1

Джонсон

2

Тэйлор, Майлер

3

Робертс, Майклс, Стивен