CLR 純量值函式

發行項
05/04/2023

適用於：SQL Server

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

CLR 純量值函式的需求

.NET Framework SVF 會在 .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 的詳細資訊，請參閱使用 Table-Valued 參數 (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 ，以存取屬性和 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。在下一行中，目標方法為 public static (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中註冊。

注意

使用/clr：pure編譯的 Visual C++ 資料庫物件不支援在 SQL Server 上執行。例如，這類資料庫物件包括純量值函式。

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 資料庫物件進行資料存取

Share via