SQLServer2005中使用CLR函数实现字符串排序

SQL Server 2005中使用CLR函数实现字符串排序
伴随着SQL Server 2005 ，微软发布了公共语言运行库(CLR)，允许开发人员和DBA能够利用SQL Server之外的托管代码。

CLR为那些懂的.net开发语言，但是不懂的T-SQL语言的人，提供了一种实现方式。

使用CLR，可以直接在SQL Server中创建存储过程、触发器，用户自定义函数，集合体和类型等等。

在本文中，我们通过一个例子，来看看如何使用CLR函数，对输入的字符串进行分析、排序，最后得到排序后的字符串。

假设储存在数据库表中的数据如下所示：
apple,pear,orange,banana,grape,kiwi
我们希望的结果排序如下所示：
apple,banana,grape,kiwi,orange,pear
我们可以写SQL Server函数来实现这一功能：解析字符串，将结果储存在一个临时表中，然后按指定顺序检索结果，并将字符串重新结合在一起。

我们也可以编写一个简短的CLR函数，来实现同样的功能。

接下来让我们来看看CLR
函数具体是如何实现这一功能的。

步骤1：启用CLR集成
首先需要在SQL Server外围应用配置器中进行设置，确保SQL Server已经启用了CLR。

如图1所示：
图1：SQL Server 2005外围应用配置器
选择“功能的外围应用配置器”，显示如图2所示界面：
确保“启用CLR集成”这一项打勾，然后点“确定”按钮。

步骤2：编写CLR代码
首先，我们需要做的是编写CLR代码。

采用或者C#来写都可以，在这个例子中我们将使用 。

下面的范例代码中，创建了一个名为CLRFunctions的类，该类中包含了一个名为SortString的函数。

SortString函数对输入的一个字符串变量进行排序，并返回一个排序后的字符串。

第一步：使用内置的VB函数，把“，”作为分隔符分割输入的字符串，得到一个字符串数组;
第二步：采用Array.Sort，对数组中的数据进行排序列。

这一部如果采用T-SQL来写的话，更为简单。

将代码保存到SQLServerCLRSortString.vb文件中。

Public Class CLRFunctions
Public Shared Function SortString(ByVal Name As String) As String
Dim i As Integer
Dim returnValue As String
Dim stringArray() As String
' 分割字符串，得到数组
stringArray = Split(Name, ",")
' 对数组进行排序
Array.Sort(stringArray)
'初始化返回值字符串
returnValue = ""
For i = LBound(stringArray) To UBound(stringArray)
returnValue = returnValue & stringArray(i) & ","
Next i
Return returnValue
End Function
End Class
步骤3：编译CLR代码
为在命令行状态下，可以使用vbc.exe应用程序来编译代码。

具体命令如下所示：C:\WINDOWS\\Framework\ /target:library
C:\SQLServerCLRSortString.vb
vbc.exe应用程序，在.NET 2.0框架目录中可以找到，不同的服务器或桌面因安装路径不同可能会有差异。

编译成功后，将得到C:\SQLServerCLRSortString.dll这个DLL文件。

把DLL拷贝到我们的SQL Server机器上
步骤4：在SQL Server中注册DLL
代码编译通过后，您需要与SQL Server中注册DLL。

要实现到这一目的，可以在要使用该函数的数据库中能运行这些命令。

注册的目的，就是将外部创建的DLL与SQL Server内部对象进行绑定，这样外部DLL 中的函数就可以和SQL Server中的正常函数一样进行调用了。

在下面的函数中，我们可以看到引用
了使用此代码，需要先对代码进行编译。

*CLRFunctions –装配引用
*CLRFunctions – 代码中所引用的类名
*SortString – 代码中所引用的函数
CREATE ASSEMBLY CLRFunctions FROM 'C:\SQLServerCLRSortString.dll'
GO
CREATE FUNCTION dbo.SortString
(
@name AS NVARCHAR(255)
)
RETURNS NVARCHAR(255)
AS EXTERNAL NAME
GO
步骤5：创建测试用的数据表和数据
可以创建一个示例表和一些测试数据，来检验一下，具体代码如下所示：
CREATE TABLE testSort (data VARCHAR(255))
GO
INSERT INTO testSort VALUES('apple,pear,orange,banana,grape,kiwi')
INSERT INTO testSort VALUES('pineapple,grape,banana,apple')
INSERT INTO testSort VALUES('apricot,pear,strawberry,banana')
INSERT INTO testSort VALUES('cherry,watermelon,orange,melon,grape')
步骤6：测试
如果要测试CLR函数，在步骤4执行后示例表和数据创建完之后，可以运行下面的SELECT语句，分别列出排序前和排序后的数据，两者便于对比。

SELECT data, dbo.sortString(data) as sorted FROM testSort
上述SQL执行后，如图3所示：
图3：排序前和排序后的数据
步骤7：删除CLR函数
如果希望彻底删除CLR函数，您需要将之前创建的VB文件以及编译过程中产生的DLL 都删除。

运行下面的T-SQL代码可以删除之前创建的对象。

DROP FUNCTION dbo.SortString
GO
DROP ASSEMBLY CLRFunctions
GO
DROP TABLE testSort
GO
小结
通过宿主.NET Framework 2.0 公共语言运行库 (CLR)，SQL Server 2005显著地增强了数据库编程模型。

开发人员可以用任何CLR语言(如C#、等)来写存储过程、触发器和用户自定义函数，在实现某些功能的时候会比用T-SQL代码效率更高。