SQL Server CLR全功略之三---CLR标量函数、表值函数和聚合函数(UDA)

来源：互联网发布：windows切换平板模式编辑：程序博客网时间：2024/05/05 05:29

本节主要介绍使用CLR创建标量函数，表值函数和聚合函数。

所谓标量函数指的就是此函数只返回一个值。表值函数返回值是一个表。聚合函数是在select语句中使用的，用来聚合一个结果集，类似于Sum()或是Count()等内置的函数，而且真正的自定义聚合函数目前只能用CLR来实现。

下面的例子使用了SQLServer自带的pubs数据库。

1.CLR标量函数

1.1无参函数
    ///
    /// 标量函数，不带参数
    ///
    ///
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess = DataAccessKind.Read,
        IsDeterministic = true)]
    public static SqlString UF_Scalar_SayHello()
    {
        string returnValue = "null";
        //由于程序是在SQL Server内执行，所以连接字符串写成"context connection=true"即可
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand com = new SqlCommand("select top 1 [au_lname] from [dbo].[authors]",conn);
            using (SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection))
            {
                if (dr.Read())
                    returnValue = dr.GetString(0);//返回au_lname
            }
        }

return returnValue;//返回"null”
}

CLR函数用Microsoft.SqlServer.Server.SqlFunction特征进行修饰。里面的参数含义为：DataAccess = DataAccessKind.Read表示可访问数据表。关于SqlFunctionAttribute的属性将附录在文章的最后。

    ///
    /// 标量函数，带参数
    ///
    ///
    ///
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString UF_Scalar_SayHelloByPar(SqlString par)
    {
        return par;
    }

2.CLR表值函数

表值与标量函数有些不同。因为要返回一个数据集合，所以一定要用一个填充数据的方法，在属性中用FillRowMethodName来表示，且返回值应该为IEnumerable类型。代码如下：

1.首先自定义返回类型
public class ReturnData
    {
        public SqlString Name { get; set; }
        public SqlString Password { get; set; }
        public ReturnData(string name, string password)
        {
            this.Name = name;
            this.Password = password;
        }
    }

2.写CLR表值函数
[Microsoft.SqlServer.Server.SqlFunction(
        DataAccess = DataAccessKind.Read,
        FillRowMethodName = "FillRow_ReturnData",//这里是此函数的具体填充方法
        IsDeterministic = true)]
    public static IEnumerable UF_Table_GetReturnData()
    {
        List returnDataList = new List();
        returnDataList.Add(new ReturnData("a", "a"));
        returnDataList.Add(new ReturnData("b", "b"));
        returnDataList.Add(new ReturnData("c", "c"));
        return returnDataList;
    }

3.写填充方法
public static void FillRow_ReturnData(object returnDataObj,
                       out SqlString name,
                       out SqlString password)
    {
        ReturnData item = returnDataObj as ReturnData;
        name = "";
        password = "";
        if (item != null)
        {
            name = item.Name;
            password = item.Password;
        }
    }

这样一个表值函数就写好了。确定有点麻烦，但是表值在某种情况下，也是不可替代的。

3.CLR聚合函数

用户自定义的CLR聚合类中必须四个函数:Init,Accumulate,Merge,Terminate。Init用户初始化，Accumulate用来实现具体的聚合算法，Merge用来执行每一次的聚合逻辑顺序，Terminate用来将聚合的结果返回。
下面的代码显示了字符串的自定义聚合

#region Aggregation
[Serializable]
[StructLayout(LayoutKind.Sequential)]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = true,
    IsInvariantToOrder = false,
    MaxByteSize=8000)]
public class StringAgg : IBinarySerialize
{
    private StringBuilder strBuffer;

    public void Init()
    {
        strBuffer = new StringBuilder();
    }

    public void Accumulate(SqlString str)
    {
        strBuffer.Append(string.Format("{0},", str));
    }

    public void Merge(StringAgg Group)
    {
        Accumulate(Group.Terminate());
    }

    public SqlString Terminate()
    {
        return strBuffer.ToString();
    }

#region IBinarySerialize Members

    public void Read(System.IO.BinaryReader r)
    {
        strBuffer = new StringBuilder(r.ReadString());
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(strBuffer.ToString());
    }

#endregion
}
#endregion;

4.创建函数的SQL脚本及调用方法
关于CLR Assembly的创建方法前面已经讲过了，这里不再重复
--创建函数
create function UF_Scalar_SayHello()
returns nvarchar(32)
as EXTERNAL NAME CLRDemoAssemly.UserDefinedFunctions.UF_Scalar_SayHello
go
create function UF_Scalar_SayHelloByPar(@Par nvarchar(32))
returns nvarchar(32)
as EXTERNAL NAME CLRDemoAssemly.UserDefinedFunctions.UF_Scalar_SayHelloByPar
go
create function UF_Table_GetReturnData()
returns table(Name nvarchar(32),Password nvarchar(32))
as EXTERNAL NAME CLRDemoAssemly.UserDefinedFunctions.UF_Table_GetReturnData
go
create AGGREGATE StringAgg(@Par nvarchar(32))
returns nvarchar(max)
EXTERNAL NAME CLRDemoAssemly.StringAgg
go
select dbo.UF_Scalar_SayHello()
go
select dbo.UF_Scalar_SayHelloByPar('Hello TJVictor')
go
select * from dbo.UF_Table_GetReturnData()
go
select dbo.StringAgg(au_lname) from dbo.authors

5.SqlFunctionAttribute的属性

名称说明DataAccess指示函数是否需要访问存储在 SQL Server 的本地实例中的用户数据。FillRowMethodName方法的名称，该方法与 TVF 协定所使用的表值函数 (TVF) 在同一个类中。IsDeterministic指示用户定义的函数是否是确定性的。IsPrecise指示函数是否涉及不精确的计算，如浮点运算。Name函数在 SQL Server 中注册时所使用的名称。SystemDataAccess指示函数是否需要访问存储在 SQL Server 的系统目录或虚拟系统表中的数据。TableDefinition如果方法用作表值函数 (TVF)，则为一个字符串，该字符串表示结果的表定义。TypeId当在派生类中实现时，获取该 Attribute 的唯一标识符。

6.SqlUserDefinedAggregateAttribute的属性

名称说明Format序列化格式为 Format 的值之一。如果选择Native，则聚合类一定要被[StructLayout(LayoutKind.Sequential)]修饰；如果选择UserDefined，则聚合类一定要继承IBinarySerialize接口，自己写序列化方法。IsInvariantToDuplicates指示聚合是否与重复值无关。IsInvariantToNulls指示聚合是否与空值无关。IsInvariantToOrder指示聚合是否与顺序无关。IsNullIfEmpty指示在没有对任何值进行累积时聚合是否返回空引用。MaxByteSize聚合实例的最大大小。Name聚合的名称。TypeId当在派生类中实现时，获取该 Attribute 的唯一标识符。

7.附录完整程序

[c-sharp] view plaincopy
using System;  
using System.Data;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using Microsoft.SqlServer.Server;  
using System.Collections;  
using System.Collections.Generic;  
using System.Text;  
using System.Runtime.InteropServices;  
  
public partial class UserDefinedFunctions  
{  
    #region Scalar  
    /// <summary>  
    /// 标量函数，不带参数  
    /// </summary>  
    /// <returns></returns>  
    [Microsoft.SqlServer.Server.SqlFunction(  
        DataAccess = DataAccessKind.Read)]  
    public static SqlString UF_Scalar_SayHello()  
    {  
        string returnValue = "null";  
        //由于程序是在SQL Server内执行，所以连接字符串写成"context connection=true"即可  
        using (SqlConnection conn = new SqlConnection("context connection=true"))  
        {  
            conn.Open();  
            SqlCommand com = new SqlCommand("select top 1 [au_lname] from [dbo].[authors]", conn);  
            using (SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection))  
            {  
                if (dr.Read())  
                    returnValue = dr.GetString(0);  
            }  
        }  
  
        return returnValue;  
    }  
  
    /// <summary>  
    /// 标量函数，带参数  
    /// </summary>  
    /// <param name="par"></param>  
    /// <returns></returns>  
    [Microsoft.SqlServer.Server.SqlFunction]  
    public static SqlString UF_Scalar_SayHelloByPar(SqlString par)  
    {  
        return par;  
    }  
    #endregion  
 
    #region Table  
    /// <summary>  
    /// 表值函数。  
    /// </summary>  
    /// <returns></returns>  
    [Microsoft.SqlServer.Server.SqlFunction(  
        DataAccess = DataAccessKind.Read,  
        FillRowMethodName = "FillRow_ReturnData",  
        IsDeterministic = true)]  
    public static IEnumerable UF_Table_GetReturnData()  
    {  
        List<ReturnData> returnDataList = new List<ReturnData>();  
        returnDataList.Add(new ReturnData("a", "a"));  
        returnDataList.Add(new ReturnData("b", "b"));  
        returnDataList.Add(new ReturnData("c", "c"));  
        return returnDataList;  
    }  
  
    public class ReturnData  
    {  
        public SqlString Name { get; set; }  
        public SqlString Password { get; set; }  
        public ReturnData(string name, string password)  
        {  
            this.Name = name;  
            this.Password = password;  
        }  
    }  
  
    public static void FillRow_ReturnData(object returnDataObj,  
                       out SqlString name,  
                       out SqlString password)  
    {  
        ReturnData item = returnDataObj as ReturnData;  
        name = "";  
        password = "";  
        if (item != null)  
        {  
            name = item.Name;  
            password = item.Password;  
        }  
    }  
    #endregion  
};  
 
#region Aggregation  
[Serializable]  
[StructLayout(LayoutKind.Sequential)]  
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(  
    Format.UserDefined,  
    IsInvariantToDuplicates = false,  
    IsInvariantToNulls = true,  
    IsInvariantToOrder = false,  
    MaxByteSize=8000)]  
public class StringAgg : IBinarySerialize  
{  
    private StringBuilder strBuffer;  
  
    public void Init()  
    {  
        strBuffer = new StringBuilder();  
    }  
  
    public void Accumulate(SqlString str)  
    {  
        strBuffer.Append(string.Format("{0},", str));  
    }  
  
    public void Merge(StringAgg Group)  
    {  
        Accumulate(Group.Terminate());  
    }  
  
    public SqlString Terminate()  
    {  
        return strBuffer.ToString();  
    }  
 
 
    #region IBinarySerialize Members  
  
    public void Read(System.IO.BinaryReader r)  
    {  
        strBuffer = new StringBuilder(r.ReadString());  
    }  
  
    public void Write(System.IO.BinaryWriter w)  
    {  
        w.Write(strBuffer.ToString());  
    }  
 
    #endregion  
}  
#endregion;  

CLR系列文章链接:
SQL Server CLR全功略之一---CLR介绍和配置:
http://blog.csdn.net/tjvictor/archive/2009/10/25/4726933.aspx

SQL Server CLR全功略之二---CLR存储过程:
http://blog.csdn.net/tjvictor/archive/2009/10/26/4731052.aspx

SQL Server CLR全功略之三---CLR标量函数、表值函数和聚合函数(UDA):
http://blog.csdn.net/tjvictor/archive/2009/11/10/4793781.aspx

SQL Server CLR全功略之四---CLR触发器:
http://blog.csdn.net/tjvictor/archive/2009/11/10/4795569.aspx

SQL Server CLR全功略之五---CLR自定义数据类型

http://blog.csdn.net/tjvictor/archive/2009/11/13/4807901.aspx

如需转载，请注明本文原创自CSDN TJVictor专栏：http://blog.csdn.net/tjvictor