您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 預存程序完整入門指南：建立、參數、流程控制與優勢分析

編輯：MySQL 綜合教學

MySQL 預存程序完整入門指南

預存程序是預先編譯並儲存在資料庫中的 SQL 程式碼單元，可接受參數、包含流程控制邏輯，並能透過名稱重複呼叫。MySQL 5.0+ 開始支援，大幅提升資料庫程式設計的靈活性與效能。

一、預存程序核心優勢

增強 SQL 能力： 支援流程控制（IF、CASE、LOOP、WHILE、REPEAT），可完成複雜判斷與運算
模組化重用： 建立一次，多處呼叫；DBA 可獨立修改邏輯不影響應用程式碼
執行效能優： 首次執行經優化器分析生成執行計畫快取，後續直接複用，避免重複編譯優化
減少網路流量： 呼叫端僅傳送 CALL proc_name(params)，而非大量 SQL 文本
安全性控管： 透過 GRANT EXECUTE ON proc TO user 授權，隱藏底層表結構與 SQL 細節

二、建立預存程序語法

CREATE PROCEDURE 程序名 ([參數列表])
[特性 ...]
BEGIN
    -- 程序體：SQL 陳述式 + 流程控制
END;

關鍵點：分隔符變更

DELIMITER //

CREATE PROCEDURE demo_proc (OUT s INT)
BEGIN
    SELECT COUNT(*) INTO s FROM my_table;
END //

DELIMITER ;

MySQL 預設以 ; 為陳述式結束，程序體內含多個 ; 需先以 DELIMITER // 變更分隔符，結束後恢復 DELIMITER ;。

三、參數模式詳細說明

模式	關鍵字	方向	用途
輸入	IN	呼叫端 → 程序	傳入條件值、設定參數
輸出	OUT	程序 → 呼叫端	回傳計算結果、狀態碼
雙向	INOUT	雙向	傳入初值，程序修改後回傳

參數範例

CREATE PROCEDURE calc_bonus (
    IN  emp_id    INT,
    IN  base_sal  DECIMAL(10,2),
    OUT bonus     DECIMAL(10,2),
    INOUT tax_rate DECIMAL(3,2)
)
BEGIN
    SET bonus = base_sal * 0.15;
    SET tax_rate = tax_rate * 1.05;  -- 調整稅率
END;

四、流程控制結構

條件判斷： IF ... THEN ... ELSEIF ... ELSE ... END IF / CASE ... WHEN ... THEN ... ELSE ... END CASE
迴圈： WHILE ... DO ... END WHILE / REPEAT ... UNTIL ... END REPEAT / LOOP ... END LOOP（需配合 LEAVE 跳出）
標籤與跳出： label_name: BEGIN ... LEAVE label_name; ... END

五、變數宣告與賦值

DECLARE var_name data_type [DEFAULT default_value];
SET var_name = expression;
SELECT column INTO var_name FROM table WHERE ...;

六、呼叫與刪除

-- 呼叫
CALL proc_name(arg1, arg2, ...);

-- 刪除
DROP PROCEDURE IF EXISTS proc_name;

七、權限管理

GRANT EXECUTE ON PROCEDURE db.proc_name TO 'user'@'host';
REVOKE EXECUTE ON PROCEDURE db.proc_name FROM 'user'@'host';

八、完整實用範例：分頁查詢預存程序

DELIMITER //

CREATE PROCEDURE sp_page_query (
    IN  p_table      VARCHAR(64),
    IN  p_page_num   INT,
    IN  p_page_size  INT,
    OUT p_total_rows INT
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_sql    TEXT;
    
    -- 計算總筆數
    SET @sql_count = CONCAT('SELECT COUNT(*) INTO @total FROM ', p_table);
    PREPARE stmt FROM @sql_count;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET p_total_rows = @total;
    
    -- 分頁查詢
    SET v_offset = (p_page_num - 1) * p_page_size;
    SET v_sql = CONCAT('SELECT * FROM ', p_table, ' LIMIT ', v_offset, ', ', p_page_size);
    PREPARE stmt FROM v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

預存程序是資料庫開發的重要工具，善用可提升系統效能、安全性與維護性。