Share via

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 除外。 SVF 必须确保SQL Server数据类型与实现方法的返回数据类型匹配。 有关类型转换的详细信息，请参阅 映射 CLR 参数数据。

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

标量值用户定义函数可以是确定性函数也可以是不确定性函数。 如果使用一组特定的输入参数调用某个确定性函数，则该确定性函数始终返回相同的结果。 如果使用一组特定的输入参数调用某个不确定性函数，则该不确定性函数返回的结果可能会不同。

注意

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

表值参数

表值参数 (TVP) 即传递到某一过程或函数的用户定义表类型，它提供了一种将多行数据传递到服务器的高效方法。 TVP 提供与参数数组类似的功能，但提供了更大的灵活性，并与 Transact-SQL 更紧密地集成。 它们还提供提升性能的潜力。 TVP 还有助于减少到服务器的往返次数。 可以将数据作为 TVP 发送到服务器，而不是向服务器发送多个请求（例如，对于标量参数列表）。 用户定义的表类型不能作为表值参数传递给或从SQL Server进程中执行的托管存储过程或函数返回。 有关 TVP 的详细信息，请参阅 使用 Table-Valued 参数 (数据库引擎) 。

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，.NET Framework数据提供程序 for SQL Server.)

接下来，函数接收在 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 编译的 Visual C++ 数据库对象。 例如，此类数据库对象包含标量值函数。

Transact-SQL 查询以及用于注册程序集和 UDF 的示例调用如下：

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 形式公开的函数名不需要与 target public static 方法的名称匹配。

另请参阅

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