通过

CLR 标量值函数

  • 项目

适用于：SQL Server

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

CLR 标量值函数的要求

在 .NET Framework 程序集中 .NET Framework SVF 将实现为类的方法。 从 SVF 返回的输入参数和类型可以是 SQL Server 支持的任何标量数据类型，但 varchar、char、rowversion、text、ntext、image、timestamp、table 或 cursor 除外。 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();  
        }  
    }  
}  

Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
Public Class T  
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _  
    Public Shared Function ReturnOrderCount() As Integer  
        Using conn As New SqlConnection("context connection=true")  
            conn.Open()  
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
            Return CType(cmd.ExecuteScalar(), Integer)  
        End Using  
    End Function  
End Class

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

接下来，该函数接收在 Microsoft.SqlServer.Server 命名空间中找到的 SqlFunction 自定义属性。 该自定义属性指示用户定义函数 (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 命名空间中找到的类型编写的客户端应用程序的开发人员很熟悉。

[C#]

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();  
}

[Visual Basic]

Using conn As New SqlConnection("context connection=true")  
   conn.Open()  
   Dim cmd As New SqlCommand( _  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
   Return CType(cmd.ExecuteScalar(), Integer)  
End Using

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

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

[C#]

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

[Visual Basic]

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb

注意

/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 中公开的函数名称不需要与目标公共静态方法的名称匹配。

另请参阅

映射 CLR 参数数据
CLR 集成自定义属性的概览
用户定义函数
从 CLR 数据库对象进行数据访问