【第182期 December 5, 2012】
 

研發新視界

SQL CLR簡介及應用

作者/蕭育杰

[發表日期:2012/12/5]


前言

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月,楊志強