程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server 2005中使用CLR函數實現字符串排序

SQL Server 2005中使用CLR函數實現字符串排序

編輯:關於SqlServer

  伴隨著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代碼效率更高。

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved