Agregação do CLR definida pelo usuário – Invocação de funções
Aplica-se a: 
SQL Server
Nas instruções SELECT do Transact-SQL, você pode invocar agregações definidas pelo usuário CLR (Common Language Runtime), sujeitas a todas as regras que se aplicam às funções de agregação do sistema.
As seguintes regras adicionais se aplicam:
O usuário atual deve ter a permissão EXECUTE na agregação definida pelo usuário.
As agregações definidas pelo usuário devem ser invocadas usando um nome de duas partes na forma de schema_name.udagg_name.
O tipo de argumento da agregação definida pelo usuário deve corresponder ou ser implicitamente conversível para o input_type da agregação, conforme definido na instrução CREATE AGGREGATE .
O tipo de retorno da agregação definida pelo usuário deve corresponder ao return_type na instrução CREATE AGGREGATE .
Exemplo 1
O seguinte exemplo é uma função de agregação definida pelo usuário que concatena um conjunto de valores de cadeia de caracteres obtidos de uma coluna em uma tabela:
[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>
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());
}
}
[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>
Public 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
Depois de compilar o código em MyAgg.dll, você pode registrar a agregação no SQL Server da seguinte maneira:
CREATE ASSEMBLY MyAgg FROM 'C:\MyAgg.dll';
GO
CREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME MyAgg.Concatenate;
Observação
Não há suporte para objetos de banco de dados do Visual C++, como funções com valor escalar, que foram compilados com a opção do compilador /clr:pure para execução no SQL Server.
Como acontece com a maioria das agregações, a maior parte da lógica está no método Accumulate . Aqui, a cadeia de caracteres que é passada como um parâmetro para o método Accumulate é acrescentada ao objeto StringBuilder que foi inicializado no método Init . Supondo que esta não seja a primeira vez que o método Accumulate foi chamado, uma vírgula também é acrescentada ao StringBuilder antes de acrescentar a cadeia de caracteres passada. Na conclusão das tarefas computacionais, o método Terminate é chamado, que retorna o StringBuilder como uma cadeia de caracteres.
Por exemplo, considere uma tabela com o seguinte esquema:
CREATE TABLE BookAuthors
(
BookID int NOT NULL,
AuthorName nvarchar(200) NOT NULL
);
Em seguida, insira as seguintes linhas:
INSERT BookAuthors VALUES(1, 'Johnson'),(2, 'Taylor'),(3, 'Steven'),(2, 'Mayler'),(3, 'Roberts'),(3, 'Michaels');
A consulta a seguir geraria então o seguinte resultado:
SELECT BookID, dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID;
| BookID | Nomes de autores |
|---|---|
| 1 | Johnson |
| 2 | Taylor, Mayler |
| 3 | Roberts, Michaels, Steven |
Exemplo 2
O exemplo a seguir mostra uma agregação que tem dois parâmetros no método Accumulate .
[C#]
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;
}
}
}
[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices
<StructLayout(LayoutKind.Sequential)> _
<Serializable(), SqlUserDefinedAggregate(Format.Native, _
IsInvariantToDuplicates:=False, _
IsInvariantToNulls:=True, _
IsInvariantToOrder:=True, _
IsNullIfEmpty:=True, _
Name:="WeightedAvg")> _
Public Class WeightedAvg
''' <summary>
''' The variable that holds the intermediate sum of all values multiplied by their weight
''' </summary>
Private sum As Long
''' <summary>
''' The variable that holds the intermediate sum of all weights
''' </summary>
Private count As Integer
''' <summary>
''' The variable that holds the intermediate sum of all weights
''' </summary>
Public Sub Init()
sum = 0
count = 0
End Sub
''' <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 Sub Accumulate(ByVal Value As SqlInt32, ByVal Weight As SqlInt32)
If Not Value.IsNull AndAlso Not Weight.IsNull Then
sum += CType(Value, Long) * CType(Weight, Long)
count += CType(Weight, Integer)
End If
End Sub
''' <summary>
''' Merge the partially computed aggregate with this aggregate.
''' </summary>
''' <param name="Group">The other partial results to be merged</param>
Public Sub Merge(ByVal Group As WeightedAvg)
sum = Group.sum
count = Group.count
End Sub
''' <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 Function Terminate() As SqlInt32
If count > 0 Then
'' int value = (int)(sum / count);
'' return new SqlInt32(value);
Dim value As Integer = CType(sum / count, Integer)
Return New SqlInt32(value)
Else
Return SqlInt32.Null
End If
End Function
End Class
Depois de compilar o código-fonte C# ou Visual Basic, execute o Transact-SQL a seguir. Este script assume que a DLL é chamada WghtAvg.dll e está no diretório raiz de sua unidade C. Um banco de dados chamado teste também é presumido.
use test;
go
-- sp_configure 'clr enabled', 1;
-- go
--- 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