当前位置导航:炫浪网>>网络学院>>编程开发>>MsSQL教程

使用 SQL Server 2005中的 CLR 集成(3)


  将标量分解为行
  
  经常需要在应用程序中传送多值参数。例如,在定单处理系统中,可能需要编写存储过程来将定单插入到 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
相关内容
赞助商链接