SQL Stored Procedure 預存程序教學

預存程序 (SQL Stored Procedure)

這篇 SQL Stored Procedure 教學將帶你認識預存程序（Stored Procedure）的基本概念與用法。Stored Procedure 是一組預先編寫並儲存在資料庫中的 SQL 語句集合，可以透過呼叫名稱來重複執行，不需要每次都重新撰寫相同的 SQL 指令。

在實際開發中，許多 SQL 操作會被反覆使用（例如計算員工薪資、產生報表、批次更新資料等）。如果每次都手動撰寫相同的 SQL 語句，不僅效率低落，也容易出錯。Stored Procedure 就是用來解決這個問題的機制，透過將常用的 SQL 邏輯封裝成一個可重複呼叫的程序，搭配參數傳遞，讓資料庫操作更加模組化與高效。

Stored Procedure 優點

使用 Stored Procedure 有以下幾個主要優點：

優點	說明
提升效能	Stored Procedure 在第一次執行時會被編譯並快取執行計畫，後續呼叫直接使用快取，減少編譯開銷
程式碼重用	將常用的 SQL 邏輯封裝後，可在不同應用程式或模組中重複呼叫
降低網路流量	只需傳送呼叫指令與參數，不用傳送完整的 SQL 語句
增強安全性	可以限制使用者只能透過 Stored Procedure 存取資料，而非直接操作資料表
便於維護	修改業務邏輯時只需更新 Stored Procedure，不用修改所有應用程式的程式碼

Stored Procedure 語法 (SQL Stored Procedure Syntax)

CREATE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype, ...)
BEGIN
    -- SQL 語句
    SQL_statement_1;
    SQL_statement_2;
    ...
END;

說明：

關鍵字	說明
CREATE PROCEDURE	建立一個新的預存程序
procedure_name	自訂的預存程序名稱
parameter datatype	傳入的參數名稱與資料型別（可選）
BEGIN…END	包裹預存程序的主體 SQL 語句
CALL / EXEC	呼叫（執行）預存程序的指令

Stored Procedure 用法 (Example)

假設我們有一個員工資料表 employees 如下（若需了解如何建立資料表，請參考 CREATE TABLE 教學）：

id	name	department	salary
1	Alice	Engineering	80000
2	Bob	Marketing	60000
3	Carol	Engineering	75000
4	Dave	Marketing	55000
5	Eve	HR	65000

基本 Stored Procedure：查詢所有員工

建立一個不帶參數的 Stored Procedure，用來查詢所有員工資料：

CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END;

呼叫這個 Stored Procedure：

CALL GetAllEmployees();

執行結果：

id	name	department	salary
1	Alice	Engineering	80000
2	Bob	Marketing	60000
3	Carol	Engineering	75000
4	Dave	Marketing	55000
5	Eve	HR	65000

帶有 IN 參數的 Stored Procedure

建立一個帶有輸入參數的 Stored Procedure，根據部門名稱查詢該部門的員工（若需了解條件篩選，請參考 SQL WHERE 教學）：

CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM employees
    WHERE department = dept_name;
END;

呼叫時傳入部門名稱：

CALL GetEmployeesByDept('Engineering');

執行結果：

id	name	department	salary
1	Alice	Engineering	80000
3	Carol	Engineering	75000

帶有 OUT 參數的 Stored Procedure

建立一個帶有輸出參數的 Stored Procedure，用來取得指定部門的員工人數（若需了解聚合函數，請參考 SQL 聚合函數教學）：

CREATE PROCEDURE CountEmployeesByDept(
    IN dept_name VARCHAR(50),
    OUT emp_count INT
)
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department = dept_name;
END;

呼叫並取得輸出參數的值：

CALL CountEmployeesByDept('Engineering', @count);
SELECT @count AS employee_count;

執行結果：

employee_count
2

帶有 INOUT 參數的 Stored Procedure

INOUT 參數同時作為輸入與輸出使用。以下範例建立一個 Stored Procedure，將傳入的薪資加上指定的加薪幅度後回傳：

CREATE PROCEDURE AdjustSalary(
    INOUT current_salary DECIMAL(10, 2),
    IN raise_percent DECIMAL(5, 2)
)
BEGIN
    SET current_salary = current_salary * (1 + raise_percent / 100);
END;

呼叫並查看結果：

SET @salary = 80000;
CALL AdjustSalary(@salary, 10);
SELECT @salary AS adjusted_salary;

執行結果：

adjusted_salary
88000.00

搭配條件判斷的 Stored Procedure

Stored Procedure 內部可以使用 IF…ELSE 等流程控制語句。以下範例根據員工薪資等級回傳不同的評語（若需了解條件表達式，請參考 SQL CASE 教學）：

CREATE PROCEDURE GetSalaryLevel(
    IN emp_id INT,
    OUT salary_level VARCHAR(20)
)
BEGIN
    DECLARE emp_salary DECIMAL(10, 2);
    
    SELECT salary INTO emp_salary
    FROM employees
    WHERE id = emp_id;
    
    IF emp_salary >= 75000 THEN
        SET salary_level = '高薪';
    ELSEIF emp_salary >= 60000 THEN
        SET salary_level = '中等';
    ELSE
        SET salary_level = '待調整';
    END IF;
END;

呼叫並查看 Alice（id=1，薪資 80000）的薪資等級：

CALL GetSalaryLevel(1, @level);
SELECT @level AS salary_level;

執行結果：

salary_level
高薪

修改與刪除 Stored Procedure

如果需要修改已存在的 Stored Procedure，大部分資料庫需要先刪除再重新建立。使用 DROP PROCEDURE 來刪除：

-- 刪除 Stored Procedure
DROP PROCEDURE IF EXISTS GetAllEmployees;

-- 重新建立修改後的版本
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT id, name, department, salary
    FROM employees
    ORDER BY salary DESC;
END;

呼叫修改後的 Stored Procedure：

CALL GetAllEmployees();

執行結果（依薪資由高到低排序）：

id	name	department	salary
1	Alice	Engineering	80000
3	Carol	Engineering	75000
5	Eve	HR	65000
2	Bob	Marketing	60000
4	Dave	Marketing	55000

不同資料庫的 Stored Procedure 語法差異

不同的資料庫管理系統在建立 Stored Procedure 的語法上有些差異，以下列出常見資料庫的寫法：

資料庫	建立語法	呼叫語法
MySQL	CREATE PROCEDURE name() BEGIN…END;	CALL name();
PostgreSQL	CREATE OR REPLACE FUNCTION name() RETURNS void AS $$ BEGIN…END; $$ LANGUAGE plpgsql;	SELECT name(); 或 CALL name();
SQL Server	CREATE PROCEDURE name AS BEGIN…END;	EXEC name;
Oracle	CREATE OR REPLACE PROCEDURE name IS BEGIN…END;	EXEC name; 或 BEGIN name; END;

以上就是 SQL Stored Procedure 的基本用法介紹。Stored Procedure 能夠將常用的 SQL 邏輯封裝為可重複呼叫的程序，搭配參數傳遞與流程控制，讓資料庫操作更加模組化、安全且高效。建議在實際專案中善用 Stored Procedure 來管理複雜的業務邏輯。

延伸閱讀

ㄚ槌