CLR 純量值函式

適用於：SQL Server

純量值函式 （SVF） 會傳回單一值，例如字串、整數或位值。 您可以使用任何 .NET Framework 程式設計語言，在Managed程式碼中建立純量值使用者定義函式。 Transact-SQL 或其他 Managed 程式代碼可以存取這些函式。 如需 Common Language Runtime （CLR） 整合的優點，以及在 Managed 程式代碼與 Transact-SQL 之間選擇的相關信息，請參閱 CLR 整合概觀。

CLR 純量值函式的需求

.NET Framework SDF 會在 .NET Framework 元件中的類別上實作為方法。 從 SVF 傳回的輸入參數和型別可以是 SQL Server 支援的任何純量數據類型， 除了 varchar、char、rowversion、text、ntext、image、timestamp、數據表或 數據指標。 SDF 必須確保 SQL Server 數據類型與實作方法的傳回數據類型之間相符。 如需類型轉換的詳細資訊，請參閱 對應 CLR 參數資料。

當您以 .NET Framework 語言實作 .NET Framework SVF 時，您可以指定 SqlFunction 自定義屬性以包含函式的其他資訊。 SqlFunction 屬性會指出函式是否存取或修改數據、如果具決定性，以及函式是否牽涉到浮點運算。

純量值使用者定義函式可能是具決定性或不具決定性的。 使用一組特定的輸入參數呼叫決定性函式時，一律會傳回相同的結果。 當呼叫具有特定輸入參數集時，非決定性函式可能會傳回不同的結果。

數據表值參數

數據表值參數 （TVP），傳遞至程式或函式的使用者定義資料表類型，提供將多個數據列傳遞至伺服器的有效方法。 TVP 提供與參數數位類似的功能，但提供更大的彈性，並與 Transact-SQL 更緊密整合。 它們也會提供更佳效能的潛力。

TVP 也有助於減少往返伺服器的次數。 您可以將資料以TVP的形式傳送至伺服器，而不是將多個要求傳送至伺服器，例如使用純量參數清單。 使用者定義數據表類型無法當做數據表值參數傳遞至 SQL Server 進程中執行的 Managed 預存程式或函式，或是從傳回。 如需 TVP 的詳細資訊，請參閱 使用資料表值參數 （Database Engine）。

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 來存取屬性，以及存取 ADO.NET 命名空間 System.Data.SqlClient。 （此命名空間包含 SqlClient、.NET Framework Data Provider for SQL Server。

接下來，函式會接收 SqlFunction 自定義屬性，該屬性位於 Microsoft.SqlServer.Server 命名空間中。 自訂屬性會指出使用者定義函數 （UDF） 是否使用進程內提供者來讀取伺服器中的數據。 SQL Server 不允許 UDF 更新、插入或刪除資料。 SQL Server 可以優化未使用進程內提供者的 UDF 執行。 這是藉由將 DataAccessKind 設定為 DataAccessKind.None來表示。 在下一行中，目標方法是公用靜態 （在 Visual Basic .NET 中共用）。

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

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 的檔案中，它可以編譯成元件，如下所示：

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

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 中所公開的函式名稱不需要符合目標公用靜態方法的名稱。

相關內容

• 對應 CLR 參數數據
• CLR 整合：CLR 例程的自定義屬性
• 使用者定義函數
• 從 CLR 資料庫物件 資料存取