使用 SQL Server 2005中的 CLR 集成（3）

　　將標量分解為行
　　
　　經常需要在應用程序中傳送多值參數例如在定單處理系統中可能需要編寫存儲過程來將定單插入到 Orders 表中存儲過程中的參數之一可能是定單中的行項目在這種情況下您會遇到 TSQL 限制它不支持表值參數或缺乏集合數據類型（如數組）解決這個問題的一種方法是將集合編碼為一個標量值（如 nvarchar 或 xml）然後將其作為參數傳遞給存儲過程在存儲過程內可以使用表值函數來接受標量輸入並將其轉換成一組行然後將這些行插入到 LineItems 表中
　　
　　雖然可以用 TSQL 編寫表值函數但是用 CLR 實現它有兩個好處
　　
　　SystemText 命名空間中的字符串處理函數使得編寫表值函數更加容易
　　
　　CLR TVF 提供了更有效的流實現這避免了將結果加載到工作表中
　　
　　下面的代碼片段顯示了如何實現一個表值函數它接受以;分隔的一組值作為輸入字符串並且以一組行（字符串中的每個值一行）的形式返回該字符串請注意MySqlReader 類的構造函數實現了大部分工作它使用 SystemStringSplit 方法將輸入字符串分解為數組
　　
　　// TVF that cracks a ; separated list of strings into a result
　　// set of nvarchar()column called Value
　　public static ISqlReader GetStrings(SqlString str)
　　{
　　return (ISqlReader)new MySqlReader(str);
　　}
　　
　　public class MySqlReader : ISqlReader
　　{
　　private string[]　m_strlist;
　　
　　private int m_iRow = ; // # rows read
　　
　　//The core methods
　　//Initialize list
　　public MySqlReader(SqlString str)
　　{
　　//Split input string if not database NULL;
　　//else m_strlist remains NULL
　　if (!strIsNull)
　　{
　　m_strlist = strValueSplit(;);
　　}
　　}
　　
　　// SECTION: Metadata related: Provide # names types of
　　// result columns
　　public int FieldCount { get { return ; } }
　　
　　public SqlMetaData GetSqlMetaData(int FieldNo)
　　{
　　if (FieldNo==)
　　return new SqlMetaData(Value SqlDbTypeNVarChar );
　　else throw new NotImplementedException();
　　}
　　
　　// SECTION: Row navigation Read is called until it returns
　　// false After each Read call Get<TypeName> for each
　　// column is called
　　public bool Read()
　　{
　　//Return empty result set if input is DB NULL
　　//and hence m_strlist is uninitialized
　　if (m_strlist==null) return false;
　　
　　m_iRow++;
　　if (m_iRow == m_strlistLength)
　　return false;
　　return true;
　　}
　　
　　//Column getters
　　//Implement Get<SqlTypeName> for each column produced by
　　//the TVF; in this case just one
　　public SqlChars GetSqlChars(int i)
　　{
　　if (i == )
　　return new SqlChars(m_strlist[m_iRow]);
　　else
　　throw new NotImplementedException();
　　}
　　
　　//Methods not used by SqlServer omitted;
　　//Actual implementation should provide an empty
　　//implementation
　　
　　
　　} // public class MySqlReader
　　} // class StringFunctions;
　　
　　假定 GetStrings 方法注冊為具有相同名稱的 TVF下面是存儲過程的代碼片段它使用此 TVF 從定單中提取表形式的行項目
　　
　　CREATE PROCEDURE Insert_Order @cust_id int @lineitems
　　nvarchar()
　　AS
　　BEGIN
　　
　　INSERT LineItems
　　SELECT * FROM dboGetStrings(@lineitems)
　　
　　END
　　
　　對數據進行自定義聚合
　　
　　在許多情況下您可能需要對數據進行聚合這包括執行統計計算（如 avgstddev 等等）如果所需的聚合函數不是作為內置聚合函數直接支持的SQL Server 中有三種方法可以進行這樣的自定義聚合
　　
　　將聚合編寫為用戶定義的聚合 (UDA)
　　
　　使用 CLR 存儲過程編寫聚合
　　
　　使用服務器端光標
　　
　　讓我們在一個稱為 PRODUCT(int) 的簡單聚合函數的上下文中檢查這三種替代方法該聚合函數計算一組給定值的乘積
　　
　　作為用戶定義的聚合函數實現的 PRODUCT
　　
　　下面是此函數的主干 C# 代碼示例所有的積累邏輯都在 Accumulate 函數中（為了簡單起見其他函數顯示為 {}）
　　
　　[SqlUserDefinedAggregate(FormatNative)]
　　public struct Product
　　{
　　public void Accumulate(SqlInt Value)
　　{
　　m_value *= Value;
　　}
　　public void Init() {}
　　public void Merge(Product Group) {}
　　public SqlInt Terminate() {}
　　}
　　
　　在定義類型創建程序集和注冊到 SQL Server 之後就可以通過以下方式使用 TSQL中的聚合函數
　　
　　SELECT dboProduct(intcol)
　　FROM tbl
　　GROUP BY col
　　
　　作為使用 SqlDataReader 的托管存儲過程實現的 PRODUCT
　　
　　可以創建存儲過程來執行查詢和循環訪問結果以執行計算這種循環訪問是通過使用 SqlDataReader 類完成的
　　
　　[SqlProcedure]
　　public static void Product(out SqlInt value)
　　{
　　SqlCommand cmd = SqlContextGetCommand();
　　cmdCommandText = select intcolumn from tbl;
　　SqlDataReader r = cmdExecuteReader();
　　bool first = true;
　　using (r)
　　{
　　while (rRead()) //skip to the next row
　　{
　　if (first)
　　{
　　value = rGetSqlInt();
　　first = false;
　　}
　　else
　　{
　　value *= rGetSqlInt();
　　}
　　}
　　}
　　}
　　
　　可以使用 EXEC 語句來調用這一過程
　　
　　EXEC Product @p OUTPUT
　　
　　作為使用光標的 TSQL 存儲過程實現的 PRODUCT
　　
　　可以創建 TSQL 存儲過程來執行查詢和通過使用 TSQL 光標循環訪問結果以執行計算
　　
　　create procedure TSQL_ProductProc (@product int output)
　　as
　　begin
　　declare @sales int
　　declare c insensitive cursor for select intcolumn from tbl
　　open c
　　fetch next from c into @sales
　　
　　if @@FETCH_STATUS =
　　set @product = @sales
　　
　　while @@FETCH_STATUS =
　　begin
　　fetch next from c into @sales
　　set @product = @product * @sales
　　end
　　
　　close c
　　deallocate c
　　end
　　
　　決定是使用 UDA 還是使用其他某種解決方案來產生結果取決於幾個因素
　　
　　可組合性要求UDA 實際上是獨立的對象可以用於任何 TSQL 查詢通常用在可以使用系統聚合函數的任何相同的地方不需要假定它所操作的查詢例如可以將其包括在視圖定義（不過索引視圖中不支持 UDA）和標量子查詢中
　　
　　聚合算法細節在 Order By 子句（如果查詢中有）之前可能對 UDA 進行求值因此不能保證傳遞給聚合函數的值的順序如果聚合算法需要按照特定的順序使用值則不能使用 UDA同樣地UDA 從整組中使用值並且返回單一值如果需要必須為組中的每個值返回值的聚合函數則應該考慮使用存儲過程或流表值函數來編寫您的函數詳細信息請參見本文中的產生結果一節
　　
　　對副作用和數據訪問的需要不允許 UDA 進行數據訪問或有副作用如果您的函數需要保留大量的數據作為聚合的中間狀態或因為其他某種原因需要進行數據訪問則必須使用過程
　　
　　使用 UDA 的第一種方法在這三個選擇中可能提供最好的性能通常如果沒有碰到上面所列的限制就應該嘗試將聚合函數編寫為 UDA如果無法使用 UDA 方法則使用 SqlReader 的托管代碼方法可能比 TSQL 光標方法執行得更好
　　
　　可以用 UDA 方法編寫的有用的聚合的示例還包括找到每組中第 N 大（或第 N 小）值找到每組中前 N 個最大值的平均值或總和等等
　　
　　用戶定義的類型 (UDT)
　　
　　現在我們來講 SQL Server 中功能更強大但是經常被錯誤理解的一個功能使用用戶定義的類型 (UDT)可以擴展數據庫的標量類型系統（不僅僅為系統類型定義您自己的別名這在 SQL Server 以前的版本中一直可用）定義 UDT 就像用托管代碼編寫類創建程序集然後使用create type語句在 SQL Server 中注冊該類型一樣簡單下面是實現 UDT 的主干代碼
　　
　　[SqlUserDefinedTypeAttribute(FormatNative)]
　　public struct SimpleUdt: INullable
　　{
　　public override string ToString() {}
　　public bool IsNull { get; }
　　public static SimpleUdt Null { get; }
　　public static SimpleUdt Parse(SqlString s) {}
　　
　　}
　　
　　create type simpleudt from [myassembly][SimpleUdt]
　　
　　create tab
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22103.html