SQL CLR簡介及應用

作者/蕭育杰

前言 SQL CLR(Common Languate Runtime)是從SQL Server 2005開始推出的新功能,其中還包括OLE Automation、特定遠端查詢、DAC、Database Mail、原生XML Web Service、Service Broker、SQL Mail、Web助理、xp_cmd.shell和CTE(Common Table Expressions)等等,都是相當好用的工具。 SQL CLR簡介 官方說明如下:做為 Microsoft .NET Framework 核心的 Common Language Runtime (CLR) 提供了所有 .NET Framework 程式碼的執行環境。CLR 中執行的程式碼稱為 Managed 程式碼。利用 Microsoft SQL Server 中裝載的CLR (稱為 CLR 整合),您能夠以 Managed 程式碼撰寫預存程序、觸發程序、使用者定義函式、使用者定義型別及使用者定義彙總。因為 Managed 程式碼在執行前會編譯成原生程式碼,所以在部分案例中可大幅提升效能。

《圖一》SQL CLR架構圖
(資料來源: http://zh.wikipedia.org/wiki/File:SQL_CLR.png)
簡單的說,就是可以利用.NET的程式語言(如VB或C#)開發出資料庫物件,然後在資料庫中作使用,使得前端程式和後端資料庫的整合上有更多的彈性,有更大的實作空間。 因為Transact-SQL(以下簡稱T-SQL) 不支援陣列、集合、for-each 迴圈、位元移位或類別。而Microsoft Visual Basic .NET 及 Microsoft Visual C# 提供物件導向功能,如封裝、繼承及多型,可將相關程式碼輕鬆組成類別及命名空間,也可以與Web Services整合使用,與外部系統連結。相較之下,可以比較輕鬆簡潔的方式解決以往所遇到的難題。下面用一個實際應用的例子來說明SQL CLR。 SQL CLR應用-使用SQL CLR轉換簡體與繁體資料 首先啟動SQL Server2005的SQLCLR功能,程式集->SQL Server 2005->組態工具->SQL Server 介面區組態->功能的介面區組態,啟用CLR整合。

《圖二》CLR整合啟用畫面
若要使用 T-SQL 來啟用 CLR,請參考以下的範例(適用於SQL Server 2005 以上的環境):

《表一》以T-SQL來啟用CLR之語法
(資料來源: 德瑞克: SQL Server學習筆記)
將CLR整合功能設定完成後,接下來使用Visual Studio 2005撰寫一個字串轉換的函數。如圖三新增一個SQL Server專案。

《圖三》新增SQL Server專案
按下確定後,Visual Studio 2005會要求指定加入資料庫參考,以設定連結資料庫,部署後的函數會在指定的資料庫中出現。 接下來點選方案按下右鍵->加入->使用者定義函式,新增一個vb檔。

《圖四》加入使用者定義函式
因為使用到Strings.StrConv函數,所以需要先Imports Microsoft.VisualBasic命名空間。然後新增2個函數,分別是轉換為簡體的convertToSimp及轉換為繁體的convertToTrad。

《圖五》convert程式內容
Strings.StrConv的第三個參數(LocaleID)是Windows定義的地區別代碼,繁體中文是1028而簡體中文是2052。但是利用Strings.StrConv將字元轉換為繁體時,LocaleID要選擇用2052而不是1028,主要的原因是簡體中文要轉繁體時,會有些無法直接對應的編碼值,轉換後會出現?。因此直接使用2052搭配TraditionalChinese參數就可以將簡體中文完整的轉換為繁體中文字了。 完成後點選方案按下右鍵->部署,開啟SQL Server Management Studio可看到組件中的clrConvert及純量值函數中的convertToSimp及convertToTrad函數。

《圖六》部署至組件及函數
執行的語法及結果如圖七,使用dbo.convertToSimp將原本為繁體的原始資料轉為簡體文字,還有使用dbo.convertToTrad將原本為簡體的原始資料轉為繁體文字。

《圖七》語法及結果
測試時常常需要重新部署,請注意刪除函數時因為有相依性的問題,須先刪除純量值函數中的函數,再刪除組件中的clrConvert。 實務上,因為不是每個專案都可以直接與客戶的SQL Server作連結,所以也可以找到專案資料夾下的dll檔,以T-SQL來加入SQL CLR組件,語法如下:

《表二》使用T-SQL來加入SQL CLR組件之語法
將SQL CLR組件加入後,要再將函數加入才可使用,但要注意函數名稱必須填寫完整,否則無法新增,語法範例如下:

《表三》使用T-SQL來新增函數
而對於 SQL CLR 組件本身,SQL Server 有三種防護設定:  ‧SAFE:只有最少的權限可以執行,不可存取外部資源與外部程式碼。  ‧EXTERNAL_ACCESS:可以存取外部資源,像是檔案、登錄資料庫、網路資源等。  ‧UNSAFE:可以無限制的存取外部資源,連 Win32 API 等都可以呼叫。 大多數的情況下使用 SAFE 即可以正常使用組件,如果是要存取外部檔案才使用 EXTERNAL_ACCESS,只有在特殊的情況下(例如呼叫外部的商業邏輯元件),才會啟用 UNSAFE 層次。 結語 SQL CLR在SQL Server上的應用,不僅解決了許多舊框架下的限制,而且可以將原始碼封裝在組件中,避免重要資料的外洩。同時可以與Stored procedure和Trigger整合使用,使得很多繁複的工作得以簡化,是一項非常值得學習的工具。 參考資料SQL Server CLR 整合簡介維基百科: SQL CLR啟用CLR,以SQL Server 2005為例 ‧T-SQL問題解決[實戰], 2008年6月,楊志強