SQL 儲存程序（Stored Procedure）入門

Facebook LinkedIn Tweet

更新日期： 2025 年 3 月 4 日

本文為 SQL 關聯資料庫基本介紹系列文，第 7 篇：

在資料庫開發中，當我們需要執行重複性的 SQL 操作，例如計算報表、批量更新數據，或者封裝一系列的 SQL 查詢時。

單純依靠 SELECT、INSERT、UPDATE、DELETE ，可能會讓程式碼變得冗長且難以維護。

這時候，我們可以使用 儲存程序（Stored Procedure） 來封裝一組 SQL 指令，讓這些操作能夠在需要時被輕鬆執行，而不用每次都手動輸入一長串 SQL 語法。

什麼是儲存程序（Stored Procedure）？

儲存程序（Stored Procedure） 是 預先寫好並儲存在資料庫中的 SQL 指令集合，可以接受參數、執行查詢、回傳結果，甚至包含控制流程（如 IF、LOOP）。

它類似於程式語言中的「函式（Function）」，但儲存程序主要用於 SQL 環境，適合用來封裝複雜的業務邏輯，減少重複撰寫相同的 SQL 查詢，提高可讀性與效能。

儲存程序在資料庫開發中扮演著關鍵角色，透過它，我們可以優化效能、提升程式碼的可維護性、增強安全性，並確保交易的完整性。

提高效能

儲存程序是在 資料庫端執行，相較於應用程式每次發送 SQL 查詢到資料庫，儲存程序可以減少網路傳輸的開銷，並透過資料庫內部的最佳化機制來提升執行速度。

🔹 為什麼儲存程序比單獨的 SQL 語句快？

減少 SQL 查詢的網路傳輸成本
- 在傳統的應用程式中，每次查詢資料時，都需要將 SQL 語句從應用程式傳送到資料庫，並等待結果返回。
- 若應用程式需要執行多個 SQL 語句，這些來回的網路請求會增加延遲，影響效能。
- 儲存程序則在資料庫內部執行整個流程，減少了來回傳輸的次數，提升效能。
SQL 查詢計劃（Execution Plan）快取
- 當我們執行單獨的 SQL 語句時，資料庫每次都需要解析 SQL，並生成一個最佳執行計劃。
- 使用儲存程序時，SQL 查詢計劃可以被資料庫快取，這表示後續的執行會更快，因為資料庫不需要重新解析和最佳化查詢計劃。

🔹 示例：比較 SQL 查詢與儲存程序的效能

📌 傳統方式（多次發送 SQL 查詢）

SELECT customer_name FROM customers WHERE customer_id = 101;
SELECT order_total FROM orders WHERE customer_id = 101;
SELECT last_login FROM users WHERE customer_id = 101;

這三個查詢需要傳送三次請求給資料庫，影響效能。

📌 使用儲存程序（單次請求）

DELIMITER //
CREATE PROCEDURE GetCustomerDetails(IN customer_id INT)
BEGIN
    SELECT customer_name FROM customers WHERE customer_id = customer_id;
    SELECT order_total FROM orders WHERE customer_id = customer_id;
    SELECT last_login FROM users WHERE customer_id = customer_id;
END //
DELIMITER ;

CALL GetCustomerDetails(101);

這樣只需要一次請求，所有結果都會在資料庫端計算完成後一次性返回，提高效能！

提高程式的可讀性與維護性

程式碼的可讀性和維護性對於開發人員來說非常重要，儲存程序可以將多個 SQL 查詢封裝成一個邏輯單位，讓程式碼更整潔，維護更容易。

🔹 儲存程序如何提升可讀性？

減少 SQL 語法的重複
- 如果我們在應用程式的不同部分需要使用相同的 SQL 查詢，直接在應用程式中撰寫 SQL 會導致重複程式碼（Code Duplication）。
- 使用儲存程序可以將這些 SQL 查詢封裝起來，避免在不同地方重複撰寫相同的 SQL 語法。
更容易維護和修改
- 如果某個查詢邏輯需要變更，只需要修改儲存程序，而不用去調整應用程式內的所有 SQL 語句。

🔹 示例：重複 SQL 查詢的問題

📌 傳統方式（每次都要寫 SQL，難以維護）

SELECT customer_name FROM customers WHERE customer_id = 101;
-- ...
SELECT customer_name FROM customers WHERE customer_id = 202;
-- ...
SELECT customer_name FROM customers WHERE customer_id = 303;

每次查詢都需要重寫 SQL，容易出錯。

📌 使用儲存程序（封裝邏輯，易於維護）

CREATE PROCEDURE GetCustomerName(IN customer_id INT)
BEGIN
    SELECT customer_name FROM customers WHERE customer_id = customer_id;
END;

執行：

CALL GetCustomerName(101);

現在，只要修改儲存程序內部的邏輯，就能應用到所有查詢，讓維護更容易！

增強安全性

儲存程序也能用來提升資料庫的安全性，透過權限管理來限制使用者的存取權限，並且降低 SQL 注入攻擊的風險。

🔹 1. 限制使用者直接操作資料表

通常我們不希望一般使用者直接操作資料表，以免造成資料遺失或修改錯誤。
可以只給他們執行儲存程序的權限，而不能直接存取資料表。

📌 示例：只允許使用者執行儲存程序，而不能直接查詢資料表

GRANT EXECUTE ON PROCEDURE GetCustomerDetails TO 'user1';
REVOKE SELECT, INSERT, UPDATE, DELETE ON customers FROM 'user1';

✅ 這樣 user1 只能透過 CALL GetCustomerDetails() 取得數據，而不能直接存取 customers 表。

🔹 2. 防止 SQL 注入攻擊

SQL 注入是許多網站面臨的安全風險，使用儲存程序時，參數會被事先解析，避免惡意 SQL 語句影響查詢邏輯。

📌 不安全的 SQL 查詢（可能會遭受 SQL 注入攻擊）

query = "SELECT * FROM users WHERE username = '" + user_input + "'";

如果 user_input 來自外部輸入，攻擊者可以輸入 admin' -- 來繞過密碼檢查，登入系統。

📌 安全的儲存程序（防止 SQL 注入）

CREATE PROCEDURE GetUser(IN user_name VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE username = user_name;
END;

執行：

CALL GetUser('admin');

✅ 參數 user_name 會被資料庫解析，不會影響 SQL 語法，避免 SQL 注入攻擊！

支援交易控制

在許多應用場景中，資料庫操作需要確保數據一致性，例如：

銀行轉帳（確保 A 扣款成功，B 才會收到款項）。
訂單處理（確保付款成功後才會更新庫存）。

儲存程序內部可以使用 BEGIN TRANSACTION、COMMIT 和 ROLLBACK 來確保數據的一致性。

📌 示例：銀行轉帳

CREATE PROCEDURE TransferMoney(IN from_acc INT, IN to_acc INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;
    COMMIT;
END;

✅ 這確保轉帳交易要嘛完全成功，要嘛完全取消，避免資金錯誤！

如何建立與執行儲存程序？

不同的資料庫（如 MySQL、PostgreSQL、SQL Server）在語法上略有不同，但基本概念相同。

建立儲存程序

在 MySQL 中，可以使用 CREATE PROCEDURE 來建立儲存程序，例如：

DELIMITER //

CREATE PROCEDURE GetEmployeeCount()
BEGIN
    SELECT COUNT(*) AS TotalEmployees FROM employees;
END //

DELIMITER ;

📌 解釋：

CREATE PROCEDURE GetEmployeeCount() → 定義一個儲存程序，名稱為 GetEmployeeCount。
BEGIN ... END → 內部包含 SQL 指令。
SELECT COUNT(*) FROM employees; → 查詢 employees 表中有多少員工。
DELIMITER // → 避免 ; 被誤判為語法結束（MySQL 特有）。

執行儲存程序

儲存程序建立後，可以使用 CALL 來執行：

CALL GetEmployeeCount();

這將返回員工總數。

建立帶參數的儲存程序

儲存程序可以接受輸入參數（IN）、輸出參數（OUT）、輸入輸出參數（INOUT），讓它更具彈性。

🔹 示例：計算某部門的員工數

DELIMITER //

CREATE PROCEDURE GetDepartmentEmployeeCount(IN department_id INT)
BEGIN
    SELECT COUNT(*) AS TotalEmployees
    FROM employees
    WHERE dept_id = department_id;
END //

DELIMITER ;

📌 執行時傳入參數

CALL GetDepartmentEmployeeCount(101);

✅ 這會回傳 dept_id = 101 的員工數。

修改與刪除儲存程序

修改儲存程序 在 MySQL 中，無法直接修改儲存程序，需要先刪除後重新建立：

DROP PROCEDURE IF EXISTS GetEmployeeCount;

然後重新建立新的版本。

刪除儲存程序

DROP PROCEDURE GetEmployeeCount;

儲存程序的應用場景

儲存程序在數據處理、批次運算、交易控制等場景特別有用，以下是幾個常見應用：

產生報表

企業可能需要每天統計銷售總額、用戶活躍數等，這時可以使用儲存程序來生成報表：

CREATE PROCEDURE GenerateSalesReport()
BEGIN
    SELECT department, SUM(sales_amount) AS TotalSales
    FROM sales
    GROUP BY department;
END;

執行：

CALL GenerateSalesReport();

✅ 這樣就能快速獲得每個部門的總銷售額。

自動更新數據

例如，當客戶達到一定消費金額時，自動升級 VIP 等級：

CREATE PROCEDURE UpgradeVIP()
BEGIN
    UPDATE customers
    SET membership_level = 'VIP'
    WHERE total_spent > 10000;
END;

執行：

CALL UpgradeVIP();

✅ 這會將所有消費超過 10,000 的客戶升級為 VIP。

確保交易一致性

當執行銀行轉帳時，需要確保兩筆交易要嘛都成功，要嘛都失敗：

DELIMITER //

CREATE PROCEDURE TransferMoney(IN from_acc INT, IN to_acc INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK; -- 若發生錯誤，回滾交易
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;
    COMMIT;
END //

DELIMITER ;

執行：

CALL TransferMoney(101, 202, 500);

✅ 這確保轉帳的兩筆 SQL 操作要嘛一起成功，要嘛一起取消，避免資金錯誤。

儲存程序（Stored Procedure）使用的語言：Procedural Language（PL）

儲存程序（Stored Procedure）是一組封裝的 SQL 指令，用於執行特定的邏輯流程，例如查詢數據、批次更新或交易處理。

不同的資料庫系統使用不同的 Procedural Language（PL，程序式語言） 來撰寫儲存程序，這些語言提供 變數宣告、流程控制（如 IF、LOOP）、異常處理 等功能。

各資料庫的 Procedural Language

資料庫	使用的 Procedural Language
MySQL	SQL/PSM（SQL Persistent Stored Modules）
PostgreSQL	PL/pgSQL（類似 PL/SQL）
Oracle	PL/SQL（Procedural Language for SQL）
Microsoft SQL Server	T-SQL（Transact-SQL）
IBM Db2	SQL PL（SQL Procedural Language）
MariaDB	SQL/PSM（類似 MySQL）

各 PL 的語法範例

1. MySQL – SQL/PSM

MySQL 儲存程序使用 SQL/PSM，可以宣告變數、控制流程（IF、LOOP）。

DELIMITER $$

CREATE PROCEDURE GetEmployee(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = emp_id;
END $$

DELIMITER ;

DELIMITER $$：改變語法終結符，避免與 ; 衝突。
BEGIN ... END：封裝儲存程序內的邏輯。

2. PostgreSQL – PL/pgSQL

PostgreSQL 使用 PL/pgSQL，類似 PL/SQL，可以用 DECLARE 宣告變數。

CREATE OR REPLACE FUNCTION GetEmployee(emp_id INT)
RETURNS TABLE(employee_id INT, name TEXT, salary DECIMAL) AS $$
BEGIN
    RETURN QUERY SELECT * FROM employees WHERE employee_id = emp_id;
END;
$$ LANGUAGE plpgsql;

DECLARE：用來宣告變數。
RETURN QUERY：回傳查詢結果。
LANGUAGE plpgsql：指定語言為 PL/pgSQL。

3. Oracle – PL/SQL

Oracle 使用 PL/SQL（Procedural Language for SQL），支援 EXCEPTION 來處理錯誤。

CREATE OR REPLACE PROCEDURE GetEmployee (emp_id IN NUMBER)
IS
    emp_name VARCHAR2(100);
BEGIN
    SELECT name INTO emp_name FROM employees WHERE employee_id = emp_id;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
/

IS：開始宣告區塊。
EXCEPTION：處理異常狀況。

4. SQL Server – T-SQL（Transact-SQL）

SQL Server 使用 T-SQL，可用 DECLARE 宣告變數。

CREATE PROCEDURE GetEmployee
    @emp_id INT
AS
BEGIN
    SELECT * FROM employees WHERE employee_id = @emp_id;
END;

@emp_id：T-SQL 的變數寫法。
AS BEGIN ... END：定義程序內容。

儲存程序（Stored Procedure）會預先儲存運算結果嗎？

儲存程序不會預先儲存運算結果。

它的主要作用是 封裝 SQL 指令與邏輯，但每次執行時都會重新計算，而不會自動快取（Cache）或儲存結果。

執行方式

儲存程序 只儲存 SQL 指令的邏輯，每次執行時都會 重新運算，並不會保留之前的計算結果。
與 物化視圖（Materialized View） 不同，儲存程序不會主動維護運算結果。

何時會重新計算？

每次 CALL 執行儲存程序時，都會重新讀取資料、執行查詢與計算。
例如： CALL GetTotalSalary(@salary);
- 每次呼叫 GetTotalSalary 都會重新計算 SUM(salary)，而不會使用之前的結果。

graph TD;
    A["呼叫儲存程序 (CALL Stored Procedure)"] -->|開始執行| B["讀取資料 (SELECT, JOIN, etc.)"];
    B -->|執行 SQL 運算| C["進行計算 (SUM, AVG, COUNT, etc.)"];
    C -->|不儲存結果| D["返回計算結果"];
    D -->|每次重新計算| A;

儲存程序 vs. 快取機制

特性	儲存程序（Stored Procedure）	查詢快取（Query Cache，如 Redis）
是否儲存結果	❌ 不會	✅ 會（依設定）
執行時機	每次執行時重新計算	只在快取有效時回傳結果
適用場景	複雜商業邏輯	頻繁查詢但少變更的數據

如何讓儲存程序「快取」結果？

使用暫存表（Temporary Table）或快取表

若希望 避免每次重新計算，可以將運算結果儲存到資料表內：

CREATE TABLE salary_cache (
    cache_id INT PRIMARY KEY AUTO_INCREMENT,
    total_salary DECIMAL(10,2),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

然後修改儲存程序：

DELIMITER $$

CREATE PROCEDURE GetCachedTotalSalary()
BEGIN
    DECLARE cached_salary DECIMAL(10,2);

    -- 檢查快取是否有數據
    SELECT total_salary INTO cached_salary FROM salary_cache ORDER BY last_updated DESC LIMIT 1;

    -- 若無快取，重新計算並存入
    IF cached_salary IS NULL THEN
        INSERT INTO salary_cache (total_salary)
        SELECT SUM(salary) FROM employees;
    END IF;

    -- 回傳快取的薪資總額
    SELECT total_salary FROM salary_cache ORDER BY last_updated DESC LIMIT 1;
END $$

DELIMITER ;

如果快取中有結果，直接回傳，避免重複計算。
如果沒有結果，則重新計算並儲存到 salary_cache 表中。

使用 Redis 等外部快取

如果資料庫支援 Redis，可以在應用程式層（如 Python、Node.js）快取儲存程序的執行結果：

import redis
import mysql.connector

# 連接 Redis
cache = redis.Redis(host='localhost', port=6379, db=0)

# 檢查 Redis 快取
cached_result = cache.get('total_salary')

if cached_result:
    print("使用快取結果：", cached_result)
else:
    # 連接 MySQL 並執行儲存程序
    db = mysql.connector.connect(user='root', password='password', database='company')
    cursor = db.cursor()
    cursor.execute("CALL GetTotalSalary(@salary); SELECT @salary;")
    result = cursor.fetchall()[0][0]

    # 將結果存入 Redis
    cache.setex('total_salary', 3600, result)  # 設定 1 小時快取
    print("計算結果：", result)

如果 Redis 中有結果，直接回傳，避免查詢資料庫。
如果 Redis 無結果，執行儲存程序，並將結果存入快取。

結論

✅ 儲存程序（Stored Procedure）是一組可重複使用的 SQL 指令集合，適合用於批次處理、報表計算和交易控制。

✅ 它可以提高效能、安全性，減少 SQL 語法的重複，提高程式碼的可讀性。

✅ 支援參數傳遞，允許輸入參數（IN）、輸出參數（OUT），並支援交易控制（COMMIT、ROLLBACK）。

✅ 廣泛應用於企業報表、數據處理、交易系統等場景。

透過儲存程序，你可以讓資料庫操作更加高效、靈活且安全！ 💡