SQL 自訂函數深度解析：類型、語法、適用場景與限制

SQL Server 自 2000 版起支援使用者自訂函數（UDF），雖然已有預存程序，但函數在 SELECT 陳述式中直接呼叫、支援遞迴、可用於 CHECK 約束等特性，使其成為不可或缺的工具。本文從產生背景、發展歷程、三大類型結構、建立語法、適用範圍、注意事項到常見疑問，全方位解析 UDF。

為何需要自訂函數？（與預存程序差異）

1. 可直接在 SELECT、WHERE、JOIN 等 SQL 陳述式中使用，預存程序不行。
2. 支援呼叫其他函數與遞迴呼叫自身。
3. 可用於計算欄位、CHECK 約束定義特殊規則。
4. 無副作用：不得修改資料庫狀態（不可 INSERT/UPDATE/DELETE 表、不可寄信、不可修改目錄等），預存程序無此限制。
5. 只能回傳單一變數（標量或表變數），預存程序可回傳多個結果集。

三大函數類型結構

建立語法關鍵點

標量函數

CREATE FUNCTION [schema.]function_name (@param type, ...)
RETURNS scalar_type
[WITH ENCRYPTION | SCHEMABINDING]
AS
BEGIN
    -- 邏輯
    RETURN scalar_expression
END

• 參數前須加 @，最多 1024 個，不支援 OUTPUT 參數。
• RETURNS 後接資料型別，非變數名。
• 函數本體內用 RETURN 回傳值。

內聯表值函數

CREATE FUNCTION [schema.]function_name (@param type, ...)
RETURNS TABLE
[WITH ...]
AS
RETURN (SELECT ...)

• RETURNS TABLE，無 BEGIN...END。
• 單一 SELECT 定義回傳結果集。

多陳述表值函數

CREATE FUNCTION [schema.]function_name (@param type, ...)
RETURNS @return_var TABLE (col_def, ...)
[WITH ...]
AS
BEGIN
    INSERT @return_var SELECT ...
    RETURN
END

• RETURNS @table_var TABLE(結構定義)。
• 本體內用 INSERT 累積資料，最後只需 RETURN。

適用場景

1. 唯讀查詢，不修改資料庫狀態。
2. 需遞迴或多步驟邏輯產生結果集。
3. 結果集需直接嵌入 SELECT 等陳述式再加工。

常見錯誤與注意事項

• 標量函數：參數必須加 @；RETURNS 接型別；BEGIN...END 內用 RETURN。
• 內聯表值函數：RETURNS TABLE；無 BEGIN...END；單一 RETURN SELECT。
• 多陳述表值函數：RETURNS 定義表變數結構；本體 INSERT 至表變數；最後 RETURN 不帶參數。

疑問：函數能呼叫修改資料的預存程序嗎？

答：僅能呼叫擴展預存程序，但 SQL Server 後續版本已移除此支援，實務開發中應避免在函數中呼叫任何會修改資料的程序，確保無副作用原則。

參考資源

• Microsoft Docs: CREATE FUNCTION (Transact-SQL)
• MSDN: User-Defined Functions
• 部落格實戰: SQL Server 自訂函數詳解