伴隨著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:WindowsMicrosoft.NETFrameworkv2.0.50727vbc /target:library C:SQLServerCLRSortString.vbvbc.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代碼效率更高。