CLR 純量值函式

發行項
07/04/2024

適用於：SQL Server

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

CLR 純量值函式的需求

.NET Framework SDF 會在 .NET Framework 元件中的類別上實作為方法。從 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 進程中執行的 Managed 預存程式或函式，或從傳回。如需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，.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 命名空間中所找到類型的用戶端應用程式的開發人員。

[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 中註冊。

注意

不支援使用 /clr：pure 編譯的資料庫物件，在 SQL Server 上執行 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 資料庫物件進行資料存取

共用方式為