SQL Server 自訂函數與游標、預存程序、觸發器完整教學

本文系統整理 SQL Server 2008 中三種自訂函數的語法差異，並搭配游標、預存程序、觸發器的實戰範例，幫助開發者快速掌握進階 T-SQL 技巧。

一、三種自訂函數比較

二、實戰範例

1. 標量函數：查詢員工工號

CREATE FUNCTION F_GONGHAO(@XINGMING NVARCHAR(5))
RETURNS INT
AS
BEGIN
    DECLARE @GONGHAO INT
    SET @GONGHAO = (SELECT Y.工號 FROM [T_員工資訊] AS Y WHERE Y.姓名 = @XINGMING)
    RETURN @GONGHAO
END

-- 呼叫
SELECT [姓名], DBO.F_GONGHAO('李異峰') AS 工號 FROM [T_員工資訊] WHERE [姓名]='李異峰'

2. 內聯表值函數：查詢指定日期打卡記錄

CREATE FUNCTION F_DAKAJILU(@RIQI DATE)
RETURNS TABLE
AS
RETURN (SELECT * FROM [T_考勤] AS K WHERE K.日期 = @RIQI)

-- 呼叫
SELECT * FROM [F_DAKAJILU]('2014/02/28')

3. 多陳述表值函數：打卡記錄結合員工資訊

CREATE FUNCTION F_D_DAKAJILU(@RIQI DATE)
RETURNS @TEMP_TABLE TABLE (
    [姓名] NVARCHAR(5) NOT NULL, [工號] INT NOT NULL, [職位] NVARCHAR(10) NOT NULL,
    [部門] NVARCHAR(5) NOT NULL, [是否夜班] NCHAR(1) NOT NULL,
    [日期] DATE NOT NULL, [當天上班時間] FLOAT(1) NOT NULL
)
AS
BEGIN
    INSERT INTO @TEMP_TABLE
    SELECT Y.姓名, Y.工號, Y.職位, Y.部門, K.夜班, K.日期, K.當天上班時間
    FROM [T_員工資訊] AS Y CROSS JOIN [T_考勤] AS K
    WHERE Y.工號 = K.工號 AND K.日期 = @RIQI
    RETURN
END

三、游標操作完整流程

游標可逐筆處理資料，類似指標操作。完整步驟：宣告 → 開啟 → 擷取迴圈 → 關閉 → 釋放。

-- 1. 宣告唯讀游標
DECLARE CUR_T_員工資訊 SCROLL CURSOR FOR
SELECT Y.姓名, Y.工號, Y.職位, Y.部門 FROM [T_員工資訊] AS Y ORDER BY Y.工號 ASC

-- 2. 開啟
OPEN GLOBAL CUR_T_員工資訊

-- 3. 逐筆讀取
DECLARE @XINGMING NVARCHAR(3), @GONGHAO INT, @ZHIWEI NVARCHAR(10), @BUMEN NVARCHAR(8)
FETCH FIRST FROM [CUR_T_員工資訊] INTO @XINGMING, @GONGHAO, @ZHIWEI, @BUMEN
WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT '姓名：' + @XINGMING + ' 工號：' + CONVERT(VARCHAR(3), @GONGHAO) + ...
    FETCH NEXT FROM [CUR_T_員工資訊] INTO @XINGMING, @GONGHAO, @ZHIWEI, @BUMEN
END

-- 4. 關閉釋放
CLOSE GLOBAL CUR_T_員工資訊
DEALLOCATE GLOBAL CUR_T_員工資訊

四、預存程序與觸發器

預存程序支援輸入/輸出參數、動態 SQL、錯誤處理；觸發器分 DDL（如防刪除表）與 DML（利用 inserted/deleted 邏輯表記錄異動），可實現稽核、防護等功能。

DDL 觸發器範例：防刪除表

CREATE TRIGGER DROP_T ON DATABASE FOR DROP_TABLE
AS BEGIN ROLLBACK TRANSACTION PRINT '禁止刪除資料表！' END

DML 觸發器範例：記錄更新前後資料

CREATE TRIGGER UPDATE_T ON [dbo].[AA] INSTEAD OF UPDATE
AS BEGIN
    INSERT INTO T_UPDATE_HOU SELECT * FROM [inserted]
    INSERT INTO T_UPDATE_QIAN SELECT * FROM [deleted]
    PRINT '更新完成，舊資料寫入 T_UPDATE_QIAN，新資料寫入 T_UPDATE_HOU'
END