Invoking CLR User-Defined Aggregate Functions

 引用http://msdn.microsoft.com/zh-cn/library/ms131056(SQL.90).aspx

 

In Transact-SQL SELECT statements, you can invoke common language runtime (CLR) user-defined aggregates, subject to all the rules that apply to system aggregate functions.

The following additional rules apply:

• The current user must have EXECUTE permission on the user-defined aggregate.
• User-defined aggregates must be invoked using a two-part name in the form of schema_name.udagg_name.
• The argument type of the user-defined aggregate must match or be implicitly convertible to the input_type of the aggregate, as defined in the CREATE AGGREGATE statement.
• The return type of the user-defined aggregate must match the return_type in the CREATE AGGREGATE statement.

Here is an example of a user-defined aggregate function that concatenates a set of string values taken from a column in a table:

[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

 

Once you compile the code into MyAgg.dll, you can register the aggregate in SQL Server as follows:

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

 
 

  
  

      
      注意：
On a SQL Server 2005 database with a compatibility level of "80," you cannot create managed user-defined types, stored procedures, functions, aggregates, or triggers. To take advantage of these CLR integration features of SQL Server 2005, you must use the sp_dbcmptlevel (Transact-SQL) stored procedure to set the database compatibility level to "90.". 
 
       
  
  

   
   
 
 
 
 

  
  

      
      注意：
Managed C++ database objects, such as scalar-valued functions, that have been compiled with the /clr:pure Visual C++ compiler option are not supported for execution in SQL Server 2005 RTM. 
 
       
  
  

   
   
 
 
As with most aggregates, the bulk of the logic is in the Accumulate method. Here, the string that is passed in as a parameter to the Accumulate method is appended to the StringBuilder object that was initialized in the Init method. Assuming that this is not the first time the Accumulate method has been called, a comma is also appended to the StringBuilder prior to appending the passed-in string. At the conclusion of the computational tasks, the Terminate method is called, which returns the StringBuilder as a string.
For example, consider a table with the following schema:
 
 

  
  

   
   

    
     复制代码
   
   
  
  
  
  

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

  
  

 
 
Then insert the following rows:

 
 


  
  


   
   

    
     复制代码
   
   

  
  

  
  


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

  
  

 
 
The following query would then produce the following result:

 
 


  
  


   
   

    
     复制代码
   
   

  
  

  
  


   
   
SELECT BookID, dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID

  
  

 
 

  
  

 
 
BookID 
Author Names 

1

Johnson

2

Taylor, Mayler

3

Roberts, Michaels, Steven