Invocando funções de agregação CLR definidas pelo usuário
Em instruções SELECT Transact-SQL, você pode invocar agregações CLR (Common Language Runtime) definidas pelo usuário de acordo com 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 para a agregação definida pelo usuário.
As agregações definidas pelo usuário devem ser invocados usando um nome de duas partes no formato schema_name.udagg_name.
O tipo de argumento da agregação definida pelo usuário deve corresponder ou deve ser implicitamente passível de conversão no 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>
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
Ao compilar o código em MyAgg.dll, é possível 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;
.gif "Observação") Observação |
|---|
Objetos de banco de dados Visual C++, como funções com valor escalar, que foram compilados com a opção do compilador /clr:pure não têm suporte para execução no SQL Server. |
Assim como ocorre com a maioria das agregações, grande parte da lógica está no método Accumulate. Aqui, a cadeia de caracteres 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 do acréscimo da cadeia de caracteres passada. Ao concluir as tarefas computacionais, o método Terminate é chamado, retornando 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 da compilação do código-fonte do Visual Basic ou C#, 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