SQL Server 2000 使用者定義函數 (UDF) 完整語法與三大類型詳解

UDF 三大類型

通用語法骨架

CREATE FUNCTION [schema.]function_name
( [ @param_name [AS] scalar_type [ = default ] [ ,...n ] ] )
RETURNS { scalar_type | TABLE }
[ WITH  [ ,...n ] ]
[ AS ]
BEGIN
    function_body
    RETURN { scalar_expr | }  -- 標量/多陳述式需 RETURN
END

關鍵選項 WITH

• ENCRYPTION：加密函數定義 (隱藏邏輯)。
• SCHEMABINDING：綁定參考物件結構，防止被誤刪/修改導致函數失效。

三類型完整範例

1. 標量函數：計算含稅價格

CREATE FUNCTION dbo.fn_TaxPrice(@Price MONEY, @TaxRate FLOAT = 0.05)
RETURNS MONEY
WITH SCHEMABINDING
AS
BEGIN
    RETURN ROUND(@Price * (1 + @TaxRate), 2);
END
GO
-- 呼叫
SELECT dbo.fn_TaxPrice(100, 0.05);  -- 105.00

2. 內嵌表值函數：依類別取產品

CREATE FUNCTION dbo.fn_ProductsByCategory(@CatID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT ProductID, ProductName, UnitPrice
    FROM dbo.Products
    WHERE CategoryID = @CatID
);
GO
-- 呼叫 (如資料表)
SELECT * FROM dbo.fn_ProductsByCategory(1);

3. 多陳述式表值函數：產生日期序列

CREATE FUNCTION dbo.fn_DateRange(@Start DATE, @End DATE)
RETURNS @Dates TABLE (Dt DATE PRIMARY KEY)
AS
BEGIN
    DECLARE @Cur DATE = @Start;
    WHILE @Cur <= @End
    BEGIN
        INSERT @Dates VALUES (@Cur);
        SET @Cur = DATEADD(DAY, 1, @Cur);
    END
    RETURN;
END
GO
-- 呼叫
SELECT * FROM dbo.fn_DateRange('2023-01-01', '2023-01-07');

修改與刪除

ALTER FUNCTION dbo.fn_TaxPrice ...  -- 修改
DROP FUNCTION dbo.fn_TaxPrice;      -- 刪除

呼叫方式差異

• 標量/多陳述式表值：SELECT dbo.fn_Name(...) 或 EXEC @ret = dbo.fn_Name ... (僅標量)。
• 內嵌表值：只能用 SELECT * FROM dbo.fn_Name(...) (FROM 子句)。

限制事項

• UDF 內不可執行修改資料庫狀態的操作 (INSERT/UPDATE/DELETE/DDL)，只能讀取與計算。
• 不可呼叫非確定性函數 (如 GETDATE(), NEWID(), RAND()) —— 除非 WITH SCHEMABINDING 未指定且為多陳述式表值函數 (2005+ 放寬)。
• 最多 1024 個參數。

效能建議

• 能用內嵌表值函數就不用多陳述式，優化器可合併查詢計畫。
• 標量函數在大量列上調用會導致逐列執行 (RBAR)，考慮改寫為 APPLY / 內嵌表值 / 直接內聯運算式。
• 善用 SCHEMABINDING 獲得穩定性與潛在優化。

UDF 是封裝可重用邏輯的利器，選對類型、避開 RBAR 陷阱，能讓 T-SQL 程式碼更模組化、可維護。