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

小文05

　　将标量分解为行 　　 　　经常需要在应用程序中传送多值参数。例如，在定单处理系统中，可能需要编写存储过程来将定单插入到 Orders 表中。存储过程中的参数之一可能是定单中的行项目。在这种情况下，您会遇到 T-SQL 限制，它不支持表值参数或缺乏集合数据类型（如数组）。解决这个问题的一种方法是，将集合编码为一个标量值（如 nvarchar 或 xml），然后将其作为参数传递给存储过程。在存储过程内，可以使用表值函数来接受标量输入，并将其转换成一组行，然后将这些行插入到 LineItems 表中。 　　 　　虽然可以用 T-SQL 编写表值函数，但是用 CLR 实现它有两个好处： 　　 　　System.Text 命名空间中的字符串处理函数使得编写表值函数更加容易。 　　 　　CLR TVF 提供了更有效的流实现，这避免了将结果加载到工作表中。 　　 　　下面的代码片段显示了如何实现一个表值函数，它接受以‘;’分隔的一组值作为输入字符串，并且以一组行（字符串中的每个值一行）的形式返回该字符串。请注意，MySqlReader 类的构造函数实现了大部分工作，它使用 System.String.Split 方法将输入字符串分解为数组。 　　 　　// TVF that cracks a ';' separated list of strings into a result 　　// set of 1 nvarchar(60)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 = -1; // # rows read 　　 　　//The core methods 　　//Initialize list 　　public MySqlReader(SqlString str) 　　{ 　　//Split input string if not database NULL; 　　//else m_strlist remains NULL 　　if (!str.IsNull) 　　{ 　　m_strlist = str.Value.Split(';'); 　　} 　　} 　　 　　// SECTION: Metadata related: Provide #, names, types of 　　// result columns 　　public int FieldCount { get { return 1; } } 　　 　　public SqlMetaData GetSqlMetaData(int FieldNo) 　　{ 　　if (FieldNo==0) 　　return new SqlMetaData("Value", SqlDbType.NVarChar, 60); 　　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_strlist.Length) 　　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 == 0) 　　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(8000) 　　AS 　　BEGIN 　　... 　　INSERT LineItems 　　SELECT * FROM dbo.GetStrings(@lineitems) 　　... 　　END 　　 　　对数据进行自定义聚合 　　 　　在许多情况下，您可能需要对数据进行聚合。这包括执行统计计算（如 avg、stddev 等等）。如果所需的聚合函数不是作为内置聚合函数直接支持的，SQL Server 2005 中有三种方法可以进行这样的自定义聚合： 　　 　　将聚合编写为用户定义的聚合 (UDA)。 　　 　　使用 CLR 存储过程编写聚合。 　　 　　使用服务器端光标。 　　 　　让我们在一个称为 PRODUCT(int) 的简单聚合函数的上下文中检查这三种替代方法，该聚合函数计算一组给定值的乘积。 　　 　　作为用户定义的聚合函数实现的 PRODUCT 　　 　　下面是此函数的主干 C# 代码示例。所有的积累逻辑都在 Accumulate 函数中（为了简单起见，其他函数显示为 {...}）。 　　 　　[SqlUserDefinedAggregate(Format.Native)] 　　public struct Product 　　{ 　　public void Accumulate(SqlInt32 Value) 　　{ 　　m_value *= Value; 　　} 　　public void Init() {...} 　　public void Merge(Product Group) {...} 　　public SqlInt32 Terminate() {...} 　　} 　　 　　在定义类型、创建程序集和注册到 SQL Server 之后，就可以通过以下方式使用 T-SQL中的聚合函数： 　　 　　SELECT dbo.Product(intcol) 　　FROM tbl 　　GROUP BY col 　　 　　作为使用 SqlDataReader 的托管存储过程实现的 PRODUCT 　　 　　可以创建存储过程来执行查询和循环访问结果，以执行计算。这种循环访问是通过使用 SqlDataReader 类完成的。 　　 　　[SqlProcedure] 　　public static void Product(out SqlInt32 value) 　　{ 　　SqlCommand cmd = SqlContext.GetCommand(); 　　cmd.CommandText = "select intcolumn from tbl"; 　　SqlDataReader r = cmd.ExecuteReader(); 　　bool first = true; 　　using (r) 　　{ 　　while (r.Read()) //skip to the next row 　　{ 　　if (first) 　　{ 　　value = r.GetSqlInt32(0); 　　first = false; 　　} 　　else 　　{ 　　value *= r.GetSqlInt32(0); 　　} 　　} 　　} 　　} 　　 　　可以使用 EXEC 语句来调用这一过程 　　 　　EXEC Product @p OUTPUT 　　 　　作为使用光标的 T-SQL 存储过程实现的 PRODUCT 　　 　　可以创建 T-SQL 存储过程来执行查询和通过使用 T-SQL 光标循环访问结果，以执行计算。 　　 　　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 = 0 　　set @product = @sales 　　 　　while @@FETCH_STATUS = 0 　　begin 　　fetch next from c into @sales 　　set @product = @product * @sales 　　end 　　 　　close c 　　deallocate c 　　end 　　 　　决定是使用 UDA 还是使用其他某种解决方案来产生结果取决于几个因素： 　　 　　可组合性要求。UDA 实际上是独立的对象，可以用于任何 T-SQL 查询，通常用在可以使用系统聚合函数的任何相同的地方。不需要假定它所操作的查询。例如，可以将其包括在视图定义（不过，索引视图中不支持 UDA）和标量子查询中。 　　 　　聚合算法细节。在 Order By 子句（如果查询中有）之前可能对 UDA 进行求值，因此不能保证传递给聚合函数的值的顺序。如果聚合算法需要按照特定的顺序使用值，则不能使用 UDA。同样地，UDA 从整组中使用值并且返回单一值。如果需要必须为组中的每个值返回值的聚合函数，则应该考虑使用存储过程或流表值函数来编写您的函数。详细信息请参见本文中的“产生结果”一节。 　　 　　对副作用和数据访问的需要。不允许 UDA 进行数据访问或有副作用。如果您的函数需要保留大量的数据作为聚合的中间状态，或因为其他某种原因需要进行数据访问，则必须使用过程。 　　 　　使用 UDA 的第一种方法在这三个选择中可能提供最好的性能。通常，如果没有碰到上面所列的限制，就应该尝试将聚合函数编写为 UDA。如果无法使用 UDA 方法，则使用 SqlReader 的托管代码方法可能比 T-SQL 光标方法执行得更好。 　　 　　可以用 UDA 方法编写的有用的聚合的示例还包括：找到每组中第 N 大（或第 N 小）值，找到每组中前 N 个最大值的平均值或总和，等等。 　　 　　用户定义的类型 (UDT) 　　 　　现在，我们来讲 SQL Server 2005 中功能更强大但是经常被错误理解的一个功能。使用用户定义的类型 (UDT)，可以扩展数据库的标量类型系统（不仅仅为系统类型定义您自己的别名，这在 SQL Server 以前的版本中一直可用）。定义 UDT 就像用托管代码编写类，创建程序集，然后使用“create type”语句在 SQL Server 中注册该类型一样简单。下面是实现 UDT 的主干代码： 　　 　　[SqlUserDefinedTypeAttribute(Format.Native)] 　　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 <