資料庫預存程序入門：定義、優勢與實戰範例

前言

預存程序是資料庫伺服器端預編譯的 SQL 程式碼模組，封裝複雜業務邏輯、提升效能與安全性。本文釐清定義、列舉四大優勢，並給出增刪改查基本範本。

一、定義

將常用或複雜的 SQL 作業預先撰寫、編譯並以特定名稱儲存於資料庫中；後續呼叫只需 EXEC 程序名，資料庫引擎直接執行，無需重複解析編譯。

二、四大核心優勢

1. 效能：僅首次編譯，後續重用執行計畫，減少網路傳輸與解析開銷
2. 封裝事務：多表 UPDATE/INSERT/DELETE 結合 BEGIN TRAN / COMMIT / ROLLBACK 保證原子性
3. 重用性：前後端共享邏輯，減少重複代碼
4. 安全性：可獨立授權 EXECUTE 權限，隱藏資料表結構，防 SQL 注入

三、基本語法（T-SQL / SQL Server 風格）

CREATE PROCEDURE 程序名
    @參數1 型別, @參數2 型別 ...
AS
BEGIN
    -- SQL 陳述式
END
GO

四、實戰範例

1. 新增學生

CREATE PROCEDURE sp_AddStudent
    @Name VARCHAR(20), @Sex CHAR(2), @Age INT
AS
    INSERT INTO Student (Name, Sex, Age) VALUES (@Name, @Sex, @Age);
GO

2. 修改學生

CREATE PROCEDURE sp_UpdateStudent
    @Name VARCHAR(20), @Sex CHAR(2), @Age INT, @ID INT
AS
    UPDATE Student SET Name=@Name, Sex=@Sex, Age=@Age WHERE ID=@ID;
GO

3. 刪除學生

CREATE PROCEDURE sp_DeleteStudent @ID INT
AS
    DELETE FROM Student WHERE ID=@ID;
GO

4. 查詢（支援分頁）

CREATE PROCEDURE sp_GetStudents
    @Page INT = 1, @PageSize INT = 20
AS
    SELECT * FROM Student
    ORDER BY ID
    OFFSET (@Page-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;
GO

五、呼叫方式

-- 位置參數
EXEC sp_AddStudent 'Tom', 'M', 20;

-- 命名參數（順序可互換）
EXEC sp_AddStudent @Name='Tom', @Age=20, @Sex='M';

六、進階主題預覽

• 輸出參數 OUTPUT 回傳標量值
• 動態 SQL sp_executesql 應對靈活查詢條件
• TRY...CATCH 錯誤處理與事務回滾
• 計畫快取參數嗅探與 OPTION (RECOMPILE)
• CLR 整合（C# 撰寫預存程序）

七、最佳實務建議

• 命名規範：sp_<動詞><實體>，如 sp_GetUserById
• 避免 SELECT *，明確列出欄位
• 適度使用索引提示，避免鎖死計畫
• 版控管理：預存程序腳本納入 Git，CI/CD 自動部署
• 監控：定期檢視 sys.dm_exec_procedure_stats 找出慢查詢