sqlserver:如何:创建和运行 CLR SQL Server 聚合

on 2009-2-3 in 数据库 | 0 Comment
="t18">通过向 SQL Server 项目添加“聚合”项创建 SQL 聚合部署成功后在托管代码中创建聚合像其他任何 SQL Server 聚合样被和执行

注意
在默认情况下Microsoft SQL Server 中关闭了公共语言运行库 (CLR) 集成功能必须启用该功能才能使用 SQL Server 项目项若要启用 CLR 集成请使用 sp_configure 存储过程“启用 clr”选项有关更多信息请参见 启用 CLR 集成

注意
SQL Server 集合要求实现 4个特别思路方法:Init、Accumulate、Merge 和 Terminate有关更多信息请参见SQL Books _disibledevent=> End Sub


Public Sub Accumulate(ByVal value As SqlString)
Dim Char As String
Dim indexChar As Int32

' for each character in the given parameter
For indexChar = 0 To Len(value.) - 1

Char = value..Sub(indexChar, 1)

If Char.ToLower Like "[aeiou]" Then

' it is a vowel, increment the count
countOfVowels = countOfVowels + 1
End If
Next
End Sub


Public Sub Merge(ByVal value As CountVowels)

Accumulate(value.Terminate)
End Sub


Public Function Terminate As SqlString

Return countOfVowels.
End Function
End Structure
C# 复制代码
using ;
using .Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct CountVowels
{
// count _disibledevent=> }


public void Accumulate(SqlString value)
{
// list of vowels to look for
vowels = "aeiou";

// for each character in the given parameter
for ( i=0; i < value..Length; i)
{
// for each character in the vowels
for ( j=0; j < vowels.Length; j)
{
// convert parameter character to lower and compare to vowel
(value.Value.Sub(i,1).ToLower vowels.Sub(j,1))
{
// it is a vowel, increment the count
countOfVowels1;
}
}
}
}


public void Merge(CountVowels value)
{
Accumulate(value.Terminate);
}


public SqlString Terminate
{
countOfVowels.;
}
}
C 复制代码
# "stdafx.h"

#using <.dll>
#using <.Data.dll>
#using <.Xml.dll>

using ;
using ::Data;
using ::Data::Sql;
using ::Data::SqlTypes;
using Microsoft::SqlServer::Server;

// In order to debug your Aggregate, add the following to your debug.sql file:
//
// SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels
// FROM Person.Contact
// GROUP BY LastName
// ORDER BY LastName
//

[Serializable]
[SqlUserDefinedAggregate(Format::Native)]
public value struct CountVowels
{
public:
void Init
{
countOfVowels = 0;
}

void Accumulate(SqlString value)
{
// list of vowels to look for
String ^vowels = "aeiou";

// for each character in the given parameter
for ( i=0; i < value.->Length; i)
{
// for each character in the vowels
for ( j=0; j < vowels->Length; j)
{
// convert parameter character to lower and compare to vowel
(value.Value->Sub(i, 1)->ToLower vowels->Sub(j, 1))
{
// it is a vowel, increment the count
countOfVowels1;
;
}
}
}
}

void Merge(CountVowels value)
{
Accumulate(value.Terminate);
}

SqlTypes::SqlString Terminate
{
countOfVowels.;
}

private:
// count only the vowels in the passed-in s
SqlInt32 countOfVowels;
};

部署聚合后在 SQL Server 上运行它以进行调试并验证是否返回正确数据此查询返回对 Contact 表中 LastNames 列所有值元音计数结果集

复制代码
SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels
FROM Person.Contact
GROUP BY LastName
ORDER BY LastName
Tags:  sqlserver2008 sqlserver2005 sqlserver2000 sqlserver

延伸阅读

最新评论

发表评论