使用者定義的函式

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體Microsoft Fabric 中的 SQL 分析端點Microsoft Fabric 中的倉儲Microsoft Fabric 中的 SQL 資料庫

SQL Server 使用者定義函數與程式設計語言中的函數類似，是可接受參數、執行動作 (如複雜計算) 以及傳回該動作所得值的常式。 傳回值可以是單一純量值或結果集。

使用者定義的函數優點

為何要使用使用者定義函式 (UDF)？

  • 模組化程式設計。 只需建立函數一次，將它儲存在資料庫中，就可以在程式中無限次地隨意呼叫。 使用者自訂函數不需透過原始程式碼即可修改 。

  • 更快速執行。 如同預存程序，Transact-SQL 使用者定義函數會快取執行計畫，並在重複執行時重複使用這些計畫，藉此降低 Transact-SQL 程式碼的編譯成本。 這意味著使用者定義的函數不需要在每次使用時重新解析和重新最佳化，從而加快執行時間。

    與 Transact-SQL 函數相比，通用語言執行平台 (CLR) 函數在計算工作、字串處理與商務邏輯等方面提供更顯著的效能優勢。 Transact-SQL 函數更適用於經常需要存取資料的作業。

  • 減少網路流量。 對於無法以單一純量運算式表示的作業 (例如根據某些複雜條件約束來篩選資料)，可以利用函數來表示。 接著，您可以在 WHERE 子句中叫用函式，以減少傳送到用戶端的資料列數。

重要

查詢中的 Transact-SQL UDF 只能在單一執行緒上執行 (序列執行計劃)。 因此使用 UDF 會禁止平行查詢處理。 如需平行查詢處理的詳細資訊，請參閱查詢處理架構指南。

函式的類型

本節說明純量函式、資料表值函式以及系統函數之間的差異。

純量函式

使用者定義純量函數會傳回在 RETURNS 子句中所定義之類型的單一資料值。 針對內嵌純量函式，傳回的純量值是單一陳述式的結果。 針對多重陳述式純量函數，函數主體可包含一系列傳回單一值的 Transact-SQL 陳述式。 傳回類型可以是除 text、ntext、image、cursor 和 timestamp 以外的任何資料類型。 如需查看範例，請參閱建立使用者定義函數 (資料庫引擎)。

資料表值函式

.使用者定義的資料表值函數 (TVF) 會傳回 table 資料類型。 若是內嵌資料表值函式，則不會有函式主體；資料表會是單一 SELECT 陳述式的結果集。 如需查看範例，請參閱建立使用者定義函數 (資料庫引擎)。

系統功能

SQL Server 提供許多可用以執行各種作業的系統函數。 這些函數無法修改。 如需詳細資訊，請參閱什麼是 SQL 資料庫函式？、Transact-SQL 的按類別劃分的系統函數以及系統動態管理檢視。

指導方針

造成陳述式取消並且以模組中下一個陳述式繼續 (例如觸發程序或預存程序) 的 Transact-SQL 錯誤，會在函數內部以不同方式處理。 在函數中，這樣的錯誤會造成函數停止執行。 進而導致叫用該函數的陳述式取消。

BEGIN...END 區塊中的陳述式不能有任何副作用。 函數副作用是在函數的範圍外對資源狀態所做的任何永久變更，例如修改資料庫資料表。 函數中的陳述唯一能做的變更，是變更函數中的區域物件，例如區域游標或變數。 對資料庫資料表進行修改、操作非函式本地的游標（例如發送電子郵件、嘗試修改目錄以及生成返回給使用者的結果集），這些都是無法在函式中執行的動作範例。

若 CREATE FUNCTION 陳述式會對資源產生在發出 CREATE FUNCTION 陳述式時不存在的副作用，則 SQL Server 會執行該陳述式。 不過，SQL Server 不會執行叫用的函數。

查詢中指定的函數執行的次數，會因最佳化工具建立的執行計畫而有不同。 WHERE 子句中的子查詢所叫用的函式就是一個例子。 子查詢及其函數的執行次數，會因最佳化工具選擇的存取路徑而有不同。

決定性函數必須繫結至結構描述。 建立決定性函數時，請使用 SCHEMABINDING 子句。

如需使用者定義函數的詳細資訊和效能考量事項，請參閱建立使用者定義函數 (資料庫引擎)。

函數中有效的陳述式

函數中有效的陳述式類型包括：

  • DECLARE 陳述可用來定義函式的區域資料變數與游標。

  • 指派值給對函式而言為區域的物件，例如使用 SET 將值指派給純量及資料表區域變數。

  • 參考在函數中宣告、開啟、關閉及解除配置之本機游標的游標作業。 不允許將資料傳回用戶端的 FETCH 陳述式。 只允許使用 FETCH 子句將值指派給區域變數的 INTO 陳述式。

  • 除 TRY...CATCH 陳述式之外的流程控制陳述式。

  • SELECT 陳述式，包含選取清單，其中含有將值指派給區域變數的運算式。

  • UPDATE、INSERT 和 DELETE 陳述式，修改對函式而言為區域變數的資料表變數。

  • 呼叫擴充預存程序的 EXECUTE 陳述式。

內建系統函數

下列非決定性內建函數可用於 Transact-SQL 使用者定義函數中。

  • CURRENT_TIMESTAMP
  • GET_TRANSMISSION_STATUS
  • GETDATE
  • GETUTCDATE
  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@DBTS
  • @@IDLE
  • @@IO_BUSY
  • @@MAX_CONNECTIONS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@PACKET_ERRORS
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE

下列非決定性內建函數 無法 在 Transact-SQL 使用者定義函數 （UDF） 中使用。

  • NEWID
  • NEWSEQUENTIALID
  • RAND
  • TEXTPTR

如果您在 UDF 內參考其中一個函式，您會收到下列錯誤：

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

如需決定性與非決定性內建系統函數的清單，請參閱決定性與非決定性函數。

繫結至結構描述的函數

CREATE FUNCTION 支援 SCHEMABINDING 子句，它可將函式與其參考的任何物件結構描述繫結在一起，例如資料表、檢視及其他使用者定義函式。 嘗試更改或卸除任何被結構描述繫結函數所參考的物件將會失敗。

必須先符合下列條件，才能在 CREATE FUNCTION 中指定 SCHEMABINDING：

  • 函數所參考的所有檢視和使用者自訂函數都必須已繫結至結構描述。

  • 函數所參考的所有物件，都必須與函數位於相同的資料庫。 這些物件必須利用單一部份或兩部份名稱來加以參考。

  • 您對於函式中參考的所有物件 (資料表、檢視及使用者定義函式) 必須擁有 REFERENCES 權限。

您可以使用 ALTER FUNCTION 來移除結構描述繫結。 ALTER FUNCTION 陳述式應在不指定 WITH SCHEMABINDING 的情況下重新定義函式。

指定參數

使用者自訂函數會使用零或多個輸入參數，並會傳回純量值或資料表。 每一函數最多可以有 1024 個輸入參數。 若函數的參數有預設值，在呼叫函數以取得預設值時必須指定 DEFAULT 關鍵字。 此一行為不同於使用者自訂預存程序中有預設值的參數，在這些預存程序中省略參數亦意謂著省略預設值。 使用者自訂函數不支援輸出參數。

相關內容

  • Last updated on