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所示界面：

　　图2：功能的外围应用配置器

　　确保“启用CLR集成”这一项打勾，然后点“确定”按钮。

　　步骤2：编写CLR代码

　　首先，我们需要做的是编写CLR代码。采用VB.net或者C#来写都可以，在这个例子中我们将使用VB.NET 。

　　下面的范例代码中，创建了一个名为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\Microsoft.NET\Framework\v2.0.50727\vbc /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由三个部分组成：

　　*CLRFunctions – 装配引用

　　*CLRFunctions – VB.net 代码中所引用的类名

　　*SortString – VB.net代码中所引用的函数

　　CREATE ASSEMBLY CLRFunctions FROM 'C:\SQLServerCLRSortString.dll'

　　GO

　　CREATE FUNCTION dbo.SortString

　　(

　　@name AS NVARCHAR(255)

　　)

　　RETURNS NVARCHAR(255)

　　AS EXTERNAL NAME CLRFunctions.CLRFunctions.SortString

　　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#、VB.NET等)来写存储过程、触发器和用户自定义函数，在实现某些功能的时候会比用T-SQL代码效率更高。