SQL Server 2000 使用者定義函數完整指南：標量、內嵌表值、多陳述式表值

SQL Server 2000 引入使用者定義函數 (UDF)，將可重複使用的邏輯封裝為可在查詢、條件約束、計算欄位中呼叫的常式。本文詳細解析三種函數類型的語法、參數、選項、限制與實戰範例。

函數類型總覽

通用語法骨架

CREATE FUNCTION [擁有者.]函數名稱
( [ @參數名 資料型別 [ = 預設值 ] ] [ ,...n ] )
RETURNS { 標量型別 | TABLE [ (欄位定義) ] }
[ WITH <函數選項> [ ,...n ] ]
[ AS ]
BEGIN
    函數主體
    RETURN [ 運算式 | @table_variable ]
END

關鍵選項

• ENCRYPTION：加密系統表中的函數文字，防止被複寫發佈。
• SCHEMABINDING：將函數綁定至參考物件，防止參考物件被意外修改/刪除。需滿足：參考物件也綁定結構、兩部分命名、同一資料庫、建立者有 REFERENCES 權限。

確定性與副作用限制

函數分為確定性 (相同輸入必得相同輸出) 與非確定性。UDF 不得 包含非確定性內建函數，例如：

@@CONNECTIONS, @@CPU_BUSY, @@IDLE, @@IO_BUSY, @@MAX_CONNECTIONS,
@@PACK_RECEIVED, @@PACK_SENT, @@PACKET_ERRORS, @@TIMETICKS,
@@TOTAL_ERRORS, @@TOTAL_READ, @@TOTAL_WRITE, GETDATE, GETUTCDATE,
NEWID, RAND, TEXTPTR

若 UDF 呼叫擴充預存程序，視為非確定性，可能產生副作用（修改資料庫狀態、外部資源），結果集一致性無保障。

呼叫方式

• 標量函數：需至少兩部分名稱 擁有者.函數名(參數...)，可用於計算欄位、CHECK 約束、WHERE 條件等標量運算式處。
• 表值函數：可用單部分名稱 函數名(參數...)，在 FROM 子句中視為資料表：SELECT * FROM dbo.函數名(參數)。

實戰範例

範例 A：計算 ISO 週數的標量函數

CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
    DECLARE @ISOweek int
    SET @ISOweek = DATEPART(wk, @DATE) + 1
                 - DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104')
    -- 特殊情況：1/1-1/3 可能屬前一年
    IF (@ISOweek = 0)
        SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4)) + '12'
                        + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1
    -- 特殊情況：12/29-12/31 可能屬次年
    IF (DATEPART(mm, @DATE) = 12 AND (DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28)
        SET @ISOweek = 1
    RETURN @ISOweek
END
GO

-- 呼叫前必須 SET DATEFIRST 1
SET DATEFIRST 1
SELECT dbo.ISOweek('1999-12-26') AS ISO_Week  -- 結果 52

範例 B：依書店查詢銷售的內嵌表值函數

CREATE FUNCTION dbo.SalesByStore (@storeid VARCHAR(30))
RETURNS TABLE
AS
RETURN (
    SELECT t.title, s.qty
    FROM sales s JOIN titles t ON s.title_id = t.title_id
    WHERE s.stor_id = @storeid
);
GO

-- 呼叫
SELECT * FROM dbo.SalesByStore('7066');

範例 C：遞迴查詢員工下屬的多陳述式表值函數

CREATE FUNCTION dbo.fn_FindReports (@InEmpId NCHAR(5))
RETURNS @retFindReports TABLE (
    empid NCHAR(5) PRIMARY KEY,
    empname NVARCHAR(50) NOT NULL,
    mgrid NCHAR(5),
    title NVARCHAR(30)
)
AS
BEGIN
    DECLARE @RowsAdded INT
    DECLARE @reports TABLE (
        empid NCHAR(5) PRIMARY KEY,
        empname NVARCHAR(50) NOT NULL,
        mgrid NCHAR(5),
        title NVARCHAR(30),
        processed TINYINT DEFAULT 0
    )

    -- 初始化：直接下屬
    INSERT @reports
    SELECT empid, empname, mgrid, title, 0
    FROM employees
    WHERE mgrid = @InEmpId
    SET @RowsAdded = @@ROWCOUNT

    -- 迴圈尋找間接下屬
    WHILE @RowsAdded > 0
    BEGIN
        UPDATE @reports SET processed = 1 WHERE processed = 0
        INSERT @reports
        SELECT e.empid, e.empname, e.mgrid, e.title, 0
        FROM employees e JOIN @reports r ON e.mgrid = r.empid
        WHERE r.processed = 1 AND e.mgrid <> e.empid
        SET @RowsAdded = @@ROWCOUNT
        UPDATE @reports SET processed = 2 WHERE processed = 1
    END

    INSERT @retFindReports
    SELECT empid, empname, mgrid, title FROM @reports
    RETURN
END
GO

-- 呼叫
SELECT * FROM dbo.fn_FindReports('11234');

權限與限制摘要

• 建立需 CREATE FUNCTION 權限 (預設 sysadmin、db_owner、db_ddladmin)。
• 執行需該函數 EXECUTE 權限。
• 若用於計算欄位/約束/預設值，建立表時需該函數 REFERENCES 權限。
• 函數主體不得：修改資料庫狀態、動態 SQL、臨時表 (可用表變數)、非確定性函數、向用戶端傳回結果集的游標操作。

善用 UDF 可將商業邏輯集中化、提升查詢可讀性並促進程式碼重用。選擇正確類型（標量 vs 表值、內嵌 vs 多陳述式）是效能與維護性的關鍵。