项目
04/01/2012

调用 CLR 用户定义聚合函数

在 Transact-SQL SELECT 语句中，您可以根据适用于系统聚合函数的所有规则调用公共语言运行时 (CLR) 用户定义聚合。

应遵循下列附加规则：

当前用户必须对用户定义聚合拥有 EXECUTE 权限。
必须使用 schema_name.udagg_name 形式的由两部分组成的名称来调用用户定义聚合。
用户定义聚合的参数类型必须与聚合的 input_type 匹配或隐式转换为此类型，如 CREATE AGGREGATE 语句中的定义所示。
用户定义聚合的返回类型必须与 CREATE AGGREGATE 语句中的 return_type 匹配。

示例 1

以下是一个用户定义聚合函数的示例，该函数从某个表的一列中获取一组字符串值，然后将这些值连接起来：

[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

在将代码编译为 MyAgg.dll 后，即可在 SQL Server 中注册该聚合，如下所示：

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

注意
从 SQL Server 2005 开始，在兼容级别为“80”的 SQL Server 数据库中，不能创建托管用户定义类型、存储过程、函数、聚合或触发器。若要利用 SQL Server 的这些 CLR 集成功能，必须使用 sp_dbcmptlevel (Transact-SQL) 存储过程将数据库兼容级别设置为“90”。

注意
不支持在 SQL Server 中执行已使用 /clr:pure 编译器选项编译的 Visual C++ 数据库对象，例如标量值函数。

与大多数聚合相同，大部分逻辑位于 Accumulate 方法中。在此，作为参数传入到 Accumulate 方法的字符串将追加到已在 Init 方法中初始化的 StringBuilder 对象中。假如这不是第一次调用 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 BookAuthors
GROUP BY BookID

BookID	Author Names
1	Johnson
2	Taylor, Mayler
3	Roberts, Michaels, Steven

示例 2

下面的示例演示 Accumulate 方法中有两个参数的聚合。