SQL 進階技巧：預存程序、觸發器、交易控制實戰教學【2025】

引言：從 SQL 使用者到 SQL 開發者

會寫 SELECT、JOIN、WHERE 只是 SQL 的入門。真正的 SQL 高手，還要懂得運用預存程序、觸發器、交易控制這些進階功能。

為什麼這些技能很重要？

想像一下：你的電商系統每秒處理數百筆訂單，每筆訂單都要同時更新庫存、建立訂單記錄、扣款。如果其中任何一步失敗，整筆交易都必須取消。這就是交易控制要解決的問題。

這篇文章將帶你深入了解 SQL 的進階功能，讓你從「會用 SQL」提升到「精通 SQL」。

如果你還不熟悉基礎語法，建議先閱讀 SQL 語法大全完整教學。

SQL 進階功能總覽

進階功能的價值

功能	主要用途	效益
預存程序	封裝商業邏輯	效能提升、程式碼重用、安全性
觸發器	自動化資料處理	維護資料一致性、自動記錄異動
交易控制	確保資料完整性	避免部分更新、支援回滾
CTE	簡化複雜查詢	提升可讀性、支援遞迴查詢
Cursor	逐列處理資料	處理無法批次操作的情境

學習順序建議

1. 交易控制：最基礎也最重要，任何資料異動都該懂
2. 預存程序：最常用的進階功能
3. CTE：簡化複雜查詢的利器
4. 觸發器：自動化處理的好幫手
5. Cursor：最後學，因為通常有更好的替代方案

---

預存程序 Stored Procedure

什麼是預存程序？

預存程序是預先編譯並儲存在資料庫中的 SQL 程式碼區塊。你可以把它想像成資料庫版的「函數」。

預存程序的優勢：

優勢	說明
效能提升	預先編譯，執行速度更快
減少網路傳輸	只傳參數，不傳整段 SQL
程式碼重用	寫一次，到處呼叫
安全性	可只給執行權限，不給資料表直接存取權限
維護性	商業邏輯集中管理

CREATE PROCEDURE 基本語法

-- 建立簡單的預存程序
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM employees;
END;

-- 執行預存程序
EXEC GetAllEmployees;

帶參數的預存程序

-- 建立帶輸入參數的預存程序
CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentName NVARCHAR(50)
AS
BEGIN
    SELECT name, salary, hire_date
    FROM employees
    WHERE department = @DepartmentName;
END;

-- 執行
EXEC GetEmployeesByDepartment @DepartmentName = '工程部';

輸出參數 OUTPUT

-- 建立帶輸出參數的預存程序
CREATE PROCEDURE GetDepartmentStats
    @DepartmentName NVARCHAR(50),
    @EmployeeCount INT OUTPUT,
    @AvgSalary DECIMAL(10,2) OUTPUT
AS
BEGIN
    SELECT
        @EmployeeCount = COUNT(*),
        @AvgSalary = AVG(salary)
    FROM employees
    WHERE department = @DepartmentName;
END;

-- 執行並取得輸出值
DECLARE @Count INT, @Avg DECIMAL(10,2);
EXEC GetDepartmentStats
    @DepartmentName = '工程部',
    @EmployeeCount = @Count OUTPUT,
    @AvgSalary = @Avg OUTPUT;

SELECT @Count AS 員工人數, @Avg AS 平均薪資;

預設值與可選參數

CREATE PROCEDURE SearchEmployees
    @Name NVARCHAR(50) = NULL,
    @MinSalary DECIMAL(10,2) = 0,
    @Department NVARCHAR(50) = NULL
AS
BEGIN
    SELECT *
    FROM employees
    WHERE (@Name IS NULL OR name LIKE '%' + @Name + '%')
      AND salary >= @MinSalary
      AND (@Department IS NULL OR department = @Department);
END;

-- 只傳部分參數
EXEC SearchEmployees @MinSalary = 50000;
EXEC SearchEmployees @Department = '工程部', @MinSalary = 60000;

錯誤處理 TRY...CATCH

CREATE PROCEDURE TransferMoney
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- 扣款
        UPDATE accounts
        SET balance = balance - @Amount
        WHERE id = @FromAccount;

        -- 檢查餘額是否足夠
        IF (SELECT balance FROM accounts WHERE id = @FromAccount) < 0
        BEGIN
            RAISERROR('餘額不足', 16, 1);
        END

        -- 入帳
        UPDATE accounts
        SET balance = balance + @Amount
        WHERE id = @ToAccount;

        COMMIT TRANSACTION;
        PRINT '轉帳成功';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT '轉帳失敗: ' + ERROR_MESSAGE();
    END CATCH
END;

更多預存程序的進階技巧，請參考 SQL 預存程序完整教學。

---

觸發器 Trigger

什麼是觸發器？

觸發器是一種特殊的預存程序，會在特定事件（INSERT、UPDATE、DELETE）發生時自動執行。

觸發器的使用場景：

• 自動記錄異動日誌（Audit Log）
• 維護衍生欄位（如更新時間）
• 實作複雜的商業規則
• 同步更新相關資料表

觸發器類型

類型	執行時機	說明
AFTER	事件完成後	最常用，適合記錄日誌
INSTEAD OF	取代原本事件	適合複雜驗證、View 的 DML

AFTER 觸發器範例

-- 建立異動日誌表
CREATE TABLE audit_log (
    id INT IDENTITY(1,1) PRIMARY KEY,
    table_name NVARCHAR(50),
    action NVARCHAR(10),
    record_id INT,
    old_values NVARCHAR(MAX),
    new_values NVARCHAR(MAX),
    changed_by NVARCHAR(50),
    changed_at DATETIME DEFAULT GETDATE()
);

-- 建立 AFTER UPDATE 觸發器
CREATE TRIGGER trg_employees_update
ON employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, old_values, new_values, changed_by)
    SELECT
        'employees',
        'UPDATE',
        i.id,
        (SELECT d.name, d.salary, d.department FROM deleted d WHERE d.id = i.id FOR JSON PATH),
        (SELECT i.name, i.salary, i.department FOR JSON PATH),
        SYSTEM_USER
    FROM inserted i;
END;

INSTEAD OF 觸發器範例

-- 建立 View
CREATE VIEW v_active_employees AS
SELECT id, name, salary, department
FROM employees
WHERE status = 'active';

-- 讓 View 可以 INSERT
CREATE TRIGGER trg_v_active_employees_insert
ON v_active_employees
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO employees (name, salary, department, status)
    SELECT name, salary, department, 'active'
    FROM inserted;
END;

觸發器的注意事項

效能影響：

• 觸發器在每次 DML 操作時都會執行
• 複雜的觸發器會顯著影響效能
• 觸發器中避免使用 Cursor

除錯困難：

• 觸發器是「隱形」的，不容易追蹤
• 建議在觸發器中加入適當的日誌

避免觸發器連鎖：

• 觸發器 A 觸發了觸發器 B，B 又觸發 A
• 可能造成無限迴圈

-- 檢查是否被觸發器呼叫（避免連鎖）
IF TRIGGER_NESTLEVEL() > 1
    RETURN;

交易控制 Transaction

為什麼需要交易控制？

想像一個銀行轉帳的情境：

1. 從 A 帳戶扣款 1000 元
2. 將 1000 元存入 B 帳戶

如果步驟 1 成功，但步驟 2 失敗（例如 B 帳戶不存在），會發生什麼事？

沒有交易控制：A 帳戶少了 1000 元，但 B 帳戶沒有增加，錢就「消失」了。

有交易控制：系統會自動將 A 帳戶的扣款取消，回到原本的狀態。

ACID 特性

交易必須滿足四個特性，稱為 ACID：

特性	英文	說明
原子性	Atomicity	全部成功或全部失敗
一致性	Consistency	交易前後資料保持一致
隔離性	Isolation	交易之間互不干擾
持久性	Durability	完成的交易永久保存

基本交易語法

-- 開始交易
BEGIN TRANSACTION;

-- 執行操作
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- 確認交易（永久生效）
COMMIT TRANSACTION;

-- 或取消交易（回滾所有變更）
-- ROLLBACK TRANSACTION;

交易配合錯誤處理

BEGIN TRY
    BEGIN TRANSACTION;

    -- 扣款
    UPDATE accounts SET balance = balance - 1000 WHERE id = 1;

    -- 模擬錯誤
    IF (SELECT balance FROM accounts WHERE id = 1) < 0
        RAISERROR('餘額不足', 16, 1);

    -- 入帳
    UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

    -- 全部成功，確認交易
    COMMIT TRANSACTION;
    PRINT '交易成功';
END TRY
BEGIN CATCH
    -- 發生錯誤，回滾交易
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    PRINT '交易失敗: ' + ERROR_MESSAGE();
END CATCH

隔離等級 Isolation Level

隔離等級決定交易之間如何互相影響。等級越高，資料一致性越好，但併發效能越差。

隔離等級	髒讀	不可重複讀	幻讀	說明
READ UNCOMMITTED	可能	可能	可能	最低，可讀取未提交資料
READ COMMITTED	不會	可能	可能	SQL Server 預設
REPEATABLE READ	不會	不會	可能	鎖定已讀取的資料列
SERIALIZABLE	不會	不會	不會	最高，完全隔離

-- 設定隔離等級
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
-- 你的操作
COMMIT TRANSACTION;

死結 Deadlock

當兩個交易互相等待對方釋放資源時，就會發生死結。

死結範例：

交易 A：鎖定資料表 X，等待資料表 Y
交易 B：鎖定資料表 Y，等待資料表 X
→ 雙方永遠等待，造成死結

避免死結的方法：

• 所有交易以相同順序存取資料表
• 保持交易時間短
• 使用適當的索引
• 避免在交易中進行使用者互動

---

需要資料庫架構設計協助？

根據調查，60% 的資料庫效能問題與交易設計不當有關。錯誤的隔離等級選擇可能導致資料不一致或嚴重的效能瓶頸。

CloudInsight 資料庫架構服務

• 交易設計諮詢：分析商業流程，設計最佳交易邊界
• 併發問題診斷：找出死結和鎖定等待的根因
• 隔離等級優化：平衡資料一致性與效能
• 預存程序審查：檢視現有程式碼的效能與安全性

我們的專長

• 處理過 TB 級資料庫的交易優化
• 金融、電商等高併發場景經驗
• 提供程式碼層級的具體建議

👉 預約免費資料庫架構諮詢，解決你的併發與效能問題

---

Cursor 游標使用

什麼是 Cursor？

Cursor 讓你可以逐列處理查詢結果，而不是一次處理整個結果集。

使用時機：

• 需要逐列進行複雜計算
• 無法用單一 SQL 完成的操作
• 需要呼叫外部程式或預存程序

注意：Cursor 效能通常較差，應優先考慮用 SET-based 操作取代。

Cursor 基本語法

-- 宣告變數
DECLARE @EmployeeId INT, @Name NVARCHAR(50), @Salary DECIMAL(10,2);

-- 宣告 Cursor
DECLARE employee_cursor CURSOR FOR
    SELECT id, name, salary FROM employees WHERE department = '工程部';

-- 開啟 Cursor
OPEN employee_cursor;

-- 讀取第一筆
FETCH NEXT FROM employee_cursor INTO @EmployeeId, @Name, @Salary;

-- 逐列處理
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 處理每一筆資料
    PRINT '員工: ' + @Name + ', 薪資: ' + CAST(@Salary AS NVARCHAR);

    -- 讀取下一筆
    FETCH NEXT FROM employee_cursor INTO @EmployeeId, @Name, @Salary;
END

-- 關閉並釋放 Cursor
CLOSE employee_cursor;
DEALLOCATE employee_cursor;

Cursor 的替代方案

大多數情況下，可以用更有效率的方式取代 Cursor：

範例：批量更新（不要用 Cursor）

-- ❌ 使用 Cursor（效能差）
DECLARE @Id INT;
DECLARE cur CURSOR FOR SELECT id FROM employees WHERE salary < 30000;
OPEN cur;
FETCH NEXT FROM cur INTO @Id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE id = @Id;
    FETCH NEXT FROM cur INTO @Id;
END
CLOSE cur;
DEALLOCATE cur;

-- ✅ 使用 SET-based 操作（效能好）
UPDATE employees SET salary = salary * 1.1 WHERE salary < 30000;

---

CTE 通用表格運算式

什麼是 CTE？

CTE（Common Table Expression）是一種暫時的具名結果集，只存在於單一查詢的執行期間。

CTE 的優勢：

• 提升複雜查詢的可讀性
• 支援遞迴查詢
• 可以在同一查詢中多次參考

CTE 基本語法

-- 基本 CTE
WITH high_salary_employees AS (
    SELECT id, name, department, salary
    FROM employees
    WHERE salary > 70000
)
SELECT department, COUNT(*) AS high_earners
FROM high_salary_employees
GROUP BY department;

多個 CTE

WITH
-- 第一個 CTE：高薪員工
high_salary AS (
    SELECT * FROM employees WHERE salary > 70000
),
-- 第二個 CTE：各部門統計
dept_stats AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
-- 主查詢：結合兩個 CTE
SELECT h.name, h.salary, d.avg_salary AS dept_avg
FROM high_salary h
JOIN dept_stats d ON h.department = d.department;

遞迴 CTE

遞迴 CTE 適合處理階層式資料，如組織架構、分類樹。

-- 查詢員工的管理階層
WITH employee_hierarchy AS (
    -- 錨點：最高層主管
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 遞迴：找出下屬
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy h ON e.manager_id = h.id
)
SELECT name, level
FROM employee_hierarchy
ORDER BY level, name;

實戰案例：訂單處理系統

需求說明

建立一個訂單處理的預存程序，需要：

1. 檢查庫存是否足夠
2. 建立訂單記錄
3. 扣除庫存
4. 記錄異動日誌
5. 任何步驟失敗都要回滾

完整實作

CREATE PROCEDURE CreateOrder
    @CustomerId INT,
    @ProductId INT,
    @Quantity INT,
    @OrderId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CurrentStock INT;
    DECLARE @UnitPrice DECIMAL(10,2);

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 步驟 1：檢查庫存（使用 UPDLOCK 避免併發問題）
        SELECT @CurrentStock = stock_quantity, @UnitPrice = price
        FROM products WITH (UPDLOCK)
        WHERE id = @ProductId;

        IF @CurrentStock IS NULL
        BEGIN
            RAISERROR('商品不存在', 16, 1);
        END

        IF @CurrentStock < @Quantity
        BEGIN
            RAISERROR('庫存不足，目前庫存: %d', 16, 1, @CurrentStock);
        END

        -- 步驟 2：建立訂單
        INSERT INTO orders (customer_id, product_id, quantity, unit_price, total_amount, status, created_at)
        VALUES (@CustomerId, @ProductId, @Quantity, @UnitPrice, @UnitPrice * @Quantity, 'pending', GETDATE());

        SET @OrderId = SCOPE_IDENTITY();

        -- 步驟 3：扣除庫存
        UPDATE products
        SET stock_quantity = stock_quantity - @Quantity
        WHERE id = @ProductId;

        -- 步驟 4：記錄日誌（可由觸發器處理，這裡示範手動記錄）
        INSERT INTO order_logs (order_id, action, details, created_at)
        VALUES (@OrderId, 'CREATE', '訂單建立成功，數量: ' + CAST(@Quantity AS NVARCHAR), GETDATE());

        -- 全部成功
        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- 記錄錯誤
        INSERT INTO error_logs (procedure_name, error_message, created_at)
        VALUES ('CreateOrder', ERROR_MESSAGE(), GETDATE());

        -- 重新拋出錯誤
        THROW;
    END CATCH
END;

使用方式

DECLARE @NewOrderId INT;

EXEC CreateOrder
    @CustomerId = 1,
    @ProductId = 100,
    @Quantity = 5,
    @OrderId = @NewOrderId OUTPUT;

SELECT @NewOrderId AS 新訂單編號;

---

常見問題 FAQ

Q1: 什麼時候應該使用預存程序？

預存程序適合以下情境：需要封裝複雜的商業邏輯、需要重複使用的 SQL 操作、對效能有較高要求（預先編譯）、需要控制資料存取權限（只給執行權限）。如果只是簡單的 CRUD 操作，直接使用 ORM 或 SQL 語句可能更方便維護。

Q2: 觸發器會影響資料庫效能嗎？

會的。觸發器在每次 DML 操作時都會執行，如果觸發器內的邏輯複雜或涉及大量資料操作，會明顯影響效能。建議：保持觸發器邏輯簡單、避免在觸發器中使用 Cursor、避免觸發器連鎖呼叫、考慮用預存程序取代非必要的觸發器。

Q3: 什麼是資料庫死結？如何避免？

死結是兩個或多個交易互相等待對方釋放資源的情況。例如：交易 A 鎖定資料表 X 等待 Y，交易 B 鎖定資料表 Y 等待 X。避免方法：所有交易以相同順序存取資源、保持交易時間短、使用適當的索引減少鎖定範圍、設定合理的鎖定超時時間。

---

延伸閱讀

掌握進階功能後，建議繼續深入以下主題：

進階專題：

• SQL 預存程序完整教學：建立與最佳實踐
• SQL 效能優化指南：查詢調校與索引策略
• SQL Window Functions 教學：ROW_NUMBER、RANK 實戰

基礎回顧：

• SQL 語法大全：SELECT、JOIN、WHERE 完整教學
• SQL 子查詢完整教學：Subquery 語法與應用
• SQL 函數完整指南：字串、日期、聚合函數總整理

環境與工具：

• SQL Server 完整指南：安裝、設定、管理
• SSMS 完整教學：SQL Server Management Studio 使用指南

---

進階 SQL 開發培訓

根據 Stack Overflow 調查，精通預存程序和交易控制的開發者，平均薪資比只會基礎 SQL 的開發者高出 25%。

CloudInsight SQL 進階培訓課程

• 預存程序實戰：從基礎到錯誤處理、效能優化
• 交易控制深入：ACID 特性、隔離等級、死結處理
• 觸發器應用：設計模式、效能考量、最佳實踐
• 效能調校：執行計畫分析、索引策略、查詢優化

課程特色

• 使用真實企業案例教學
• 課後提供 30 天技術諮詢
• 結業頒發培訓證書

👉 預約免費諮詢，為團隊規劃進階 SQL 培訓課程

---

參考資料

1. Microsoft, "Transact-SQL Stored Procedures" (2024)
2. Microsoft, "CREATE TRIGGER (Transact-SQL)" (2024)
3. Microsoft, "Transaction Statements (Transact-SQL)" (2024)
4. Microsoft, "WITH common_table_expression" (2024)
5. SQL Server Documentation, "Locking and Row Versioning" (2024)