通过

CLR 标量值函数

  • 项目

适用范围：SQL Server

标量值函数 （SVF） 返回单个值，例如字符串、整数或位值。 可以使用任何 .NET Framework 编程语言在托管代码中创建标量值用户定义函数。 Transact-SQL 或其他托管代码可以访问这些函数。 有关公共语言运行时（CLR）集成的优势以及在托管代码与 Transact-SQL 之间进行选择的信息，请参阅 CLR 集成概述。

CLR 标量值函数的要求

在 .NET Framework 程序集中 .NET Framework SVF 将实现为类的方法。 从 SVF 返回的输入参数和类型可以是 SQL Server 支持的任何标量数据类型， 除 varchar、char、rowversion、文本、ntext、图像、时间戳、表或 游标除外。 SDF 必须确保 SQL Server 数据类型与实现方法的返回数据类型之间的匹配。 有关类型转换的详细信息，请参阅 映射 CLR 参数数据。

在 .NET Framework 语言中实现 .NET Framework SVF 时，可以指定 SqlFunction 自定义属性以包含有关函数的其他信息。 SqlFunction 属性指示函数是否访问或修改数据（如果是确定性的）以及该函数是否涉及浮点操作。

标量值用户定义函数可能是确定性函数或不确定函数。 使用特定输入参数集调用确定性函数时，始终返回相同的结果。 使用特定输入参数集调用非确定性函数时，可能会返回不同的结果。

注意

如果函数并不总是生成相同的输出值，则不要将函数标记为确定性，前提是相同的输入值和相同的数据库状态。 如果函数不具备真正的确定性而将其标记为确定性函数，将可能产生损坏的索引视图和计算列。 通过将 IsDeterministic 属性设置为 true 来将函数标记为确定性。

表值参数

表值参数 (TVP) 即传递到某一过程或函数的用户定义表类型，它提供了一种将多行数据传递到服务器的高效方法。 TVP 提供与参数数组类似的功能，但提供更大的灵活性和更紧密的与 Transact-SQL 的集成。 它们还提供提升性能的潜力。

TVP 还有助于减少到服务器的往返次数。 可以将数据作为 TVP 发送到服务器，而不是向服务器发送多个请求（例如，对于标量参数列表）。 用户定义表类型不能作为表值参数传递给在 SQL Server 进程中执行的托管存储过程或函数，也不能作为表值参数进行返回。 有关 TVP 的详细信息，请参阅 使用表值参数（数据库引擎）。

CLR 标量值函数的示例

下面是一个简单的 SVF，用于访问数据并返回整数值：

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class T
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int ReturnOrderCount()
    {
        using (SqlConnection conn
            = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
            return (int)cmd.ExecuteScalar();
        }
    }
}

第一行代码引用 Microsoft.SqlServer.Server 访问属性并 System.Data.SqlClient 访问 ADO.NET 命名空间。 （此命名空间包含用于 SQL Server 的 .NET Framework 数据提供程序 SqlClient。

接下来，该函数接收 SqlFunction 自定义属性，该属性位于 Microsoft.SqlServer.Server 命名空间中。 该自定义属性指示用户定义函数 (UDF) 是否使用进程内访问接口来读取服务器中的数据。 SQL Server 不允许 UDF 更新、插入或删除数据。 SQL Server 可以优化不使用进程内提供程序的 UDF 的执行。 这是通过将 DataAccessKind 设置为 DataAccessKind.None来指示的。 在下一行中，目标方法为公共静态方法（在 Visual Basic .NET 中为 shared）。

然后，位于 Microsoft.SqlServer.Server 命名空间中的 SqlContext 类可以访问与已设置的 SQL Server 实例的连接 SqlCommand 对象。 虽然此处未使用，但当前事务上下文也可通过 System.Transactions 应用程序编程接口（API）使用。

函数正文中的大多数代码行应该让编写使用 System.Data.SqlClient 命名空间中找到的类型的客户端应用程序的开发人员所熟悉。

using(SqlConnection conn = new SqlConnection("context connection=true"))
{
   conn.Open();
   SqlCommand cmd = new SqlCommand(
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
   return (int) cmd.ExecuteScalar();
}

通过初始化 SqlCommand 对象来指定适当的命令文本。 上一个示例对表 SalesOrderHeader中的行数进行计数。 接下来，调用 cmd 对象的 ExecuteScalar 方法。 这会基于查询返回类型 int 的值。 最后，将订单计数返回给调用方。

如果此代码保存在名为 FirstUdf.cs 的文件中，则它将能够编译到如下的程序集中：

csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs

/t:library 指示应生成一个库，而非可执行程序。 无法在 SQL Server 中注册可执行文件。

SQL Server 上不支持使用 /clr:pure 编译的视觉C++数据库对象。 例如，此类数据库对象包含标量值函数。

用于注册程序集和 UDF 的 Transact-SQL 查询和示例调用包括：

CREATE ASSEMBLY FirstUdf
    FROM 'FirstUdf.dll';
GO

CREATE FUNCTION CountSalesOrderHeader()
RETURNS INT
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;
GO

SELECT dbo.CountSalesOrderHeader();
GO

Transact-SQL 中公开的函数名称不需要与目标公共静态方法的名称匹配。

相关内容