MySQL 存儲過程與存儲函數完整開發指南

MySQL 存儲例程深度解析：語法、參數模式、流程控制、動態 SQL、最佳實踐與實戰案例

存儲過程和存儲函數（統稱存儲例程）是數據庫邏輯封裝的核心機制。本文系統講解 MySQL 存儲例程的創建、調用、參數傳遞、變量聲明、流程控制、嵌套調用、管理維護，並提供三個完整實戰案例：參數模式演示、質數計算與數據遷移、學生成績統計報表。

一、核心概念區分

• 存儲過程 (PROCEDURE)：用 CALL 調用，無返回值，通過 OUT/INOUT 參數返回數據，可執行 DDL/DML/DCL。
• 存儲函數 (FUNCTION)：用 SELECT 調用，必須返回單一值（RETURNS），不可執行事務控制、DDL 等副作用操作。
• 統稱存儲例程 (Stored Routine)。

二、基本語法結構

1. 存儲過程

CREATE PROCEDURE 過程名 ([參數列表])
[特性 ...]
過程體

2. 存儲函數

CREATE FUNCTION 函數名 ([參數列表])
RETURNS 返回類型
[特性 ...]
函數體

3. 參數模式

4. 過程體/函數體

BEGIN
  -- 聲明區
  DECLARE 變量 類型 [DEFAULT 值];
  -- 執行區
  SQL語句;
  流程控制語句;
END

5. 常用特性

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

三、參數模式實戰演示

IN 參數（值傳遞）

CREATE PROCEDURE pr_param_in(IN id INT)
BEGIN
  IF id IS NOT NULL THEN SET id = id + 1; END IF;
  SELECT id AS id_inner;
END;

SET @id = 10;
CALL pr_param_in(@id);
SELECT @id AS id_out;  -- 結果：id_inner=11, id_out=10（外部不變）

OUT 參數（引用返回）

CREATE PROCEDURE pr_param_out(OUT id INT)
BEGIN
  SELECT id AS id_inner_1;  -- NULL
  IF id IS NOT NULL THEN SET id = id + 1;
  ELSE SELECT 1 INTO id; END IF;
  SELECT id AS id_inner_3;
END;

SET @id = 10;
CALL pr_param_out(@id);
SELECT @id AS id_out;  -- 結果：id_inner_1=NULL, id_inner_3=1, id_out=1（外部被修改）

INOUT 參數（雙向）

CREATE PROCEDURE pr_param_inout(INOUT id INT)
BEGIN
  SELECT id AS id_inner_1;  -- 10
  IF id IS NOT NULL THEN SET id = id + 1; SELECT id AS id_inner_2;
  ELSE SELECT 1 INTO id; END IF;
  SELECT id AS id_inner_3;
END;

SET @id = 10;
CALL pr_param_inout(@id);
SELECT @id AS id_out;  -- 結果：id_inner_1=10, id_inner_2=11, id_inner_3=11, id_out=11

四、變量聲明與賦值

-- 聲明（必須在 BEGIN 後、首條執行語句前）
DECLARE x VARCHAR(254);
DECLARE y INT DEFAULT 0;

-- 賦值方式 1
SET x = 'test';
SET y = 155;

-- 賦值方式 2（查詢賦值）
SELECT 155 INTO y;
SELECT COUNT(*) INTO y FROM table;

五、流程控制語句

1. 條件判斷

-- IF-ELSEIF-ELSE
IF condition1 THEN stmt1;
ELSEIF condition2 THEN stmt2;
ELSE stmt3;
END IF;

-- CASE
CASE
  WHEN condition1 THEN stmt1;
  WHEN condition2 THEN stmt2;
  ELSE stmt3;
END CASE;

2. 循環控制

-- LOOP + ITERATE/LEAVE
[label:] LOOP
  stmts;
  IF cond THEN ITERATE label; END IF;  -- 續下輪
  IF cond THEN LEAVE label; END IF;    -- 跳出循環
END LOOP;

-- REPEAT (類 do-while)
REPEAT stmts; UNTIL condition END REPEAT;

-- WHILE
WHILE condition DO stmts; END WHILE;

六、循環存儲過程實例：質數篩選與數據遷移

DELIMITER $$
DROP PROCEDURE IF EXISTS `pro_prime2` $$
CREATE PROCEDURE `pro_prime2`(IN num INT)
BEGIN
  DECLARE i, j, x, y INT DEFAULT 0;
  SELECT yywID INTO j FROM text1;  -- 從表獲取起始值
  SELECT 'count ', j;              -- 顯示計數

  WHILE i < num DO
    SET x = 2;
    pp1: WHILE x <= SQRT(j) DO     -- 使用內置函數 SQRT
      IF j % x = 0 THEN
        SET y = 1;
        LEAVE pp1;                 -- 跳出內層循環標籤
      ELSE
        SET x = x + 1;
      END IF;
    END WHILE;

    IF y = 1 THEN
      SET y = 0;
    ELSE
      SET i = i + 1;
      INSERT INTO text2 VALUES(j); -- 寫入質數
    END IF;
    SET j = j + 1;                 -- 自增檢測下一數
  END WHILE;
END $$
DELIMITER ;

-- 執行：CALL pro_prime2(5);
-- text1 初始 yywID=3，text2 為空
-- 結果：text2 獲得 3,5,7,11,13（5 個質數）

七、嵌套調用與分隔符管理

DELIMITER //
CREATE PROCEDURE p1() BEGIN ... END//
CREATE PROCEDURE p2() BEGIN ... END//
CREATE PROCEDURE p3() BEGIN CALL p1(); CALL p2(); END//
DELIMITER ;

關鍵點： 腳本導入時必須用 DELIMITER $$ 臨時修改分隔符，避免過程體內分號被誤認為語句結束。管理工具（如 MySQL Administrator）可直接編輯無需處理分隔符。

八、管理維護命令

• 刪除：DROP PROCEDURE|FUNCTION name;
• 查看狀態：SHOW PROCEDURE|FUNCTION STATUS LIKE 'name'\G
• 查看定義：SHOW CREATE PROCEDURE|FUNCTION db.name

九、實戰案例：學生成績總分平均分統計報表

源表 chengjibiao： NUM, Name, English, Maths, Physics

DELIMITER $$
DROP PROCEDURE IF EXISTS `p1` $$
CREATE PROCEDURE `p1`(N INT)
BEGIN
  DECLARE a,b,c,d,e INT;
  DECLARE f CHAR(100);
  DECLARE g DECIMAL(4,2);
  SET e = 1;

  CREATE TABLE zongping (
    NUM INT, Name CHAR(255), English INT, Maths INT, Physics INT,
    Total INT, Aver DECIMAL(4,2)
  );

  REPEAT
    SELECT English, Maths, Physics, Name INTO a,b,c,f FROM chengjibiao WHERE NUM = e;
    SET d = a + b + c;
    SET g = (a + b + c) / 3;
    INSERT INTO zongping VALUES (e, f, a, b, c, d, g);
    SET e = e + 1;
  UNTIL e = N END REPEAT;

  SELECT * FROM zongping;
  DROP TABLE zongping;  -- 臨時表用完即刪
END $$
DELIMITER ;

-- 調用：CALL p1(11);  -- 10 名學生，N=11 為循環終止條件
-- 結果：動態生成含總分、平均分的報表並顯示

十、最佳實踐建議

1. 業務邏輯下沉： 複雜計算、多表聯動、報表生成適合存儲過程，減少網絡往返。
2. 謹慎使用動態 SQL： PREPARE/EXECUTE 增加靈活性但降低可維護性、防注入需自行處理。
3. 事務控制： 存儲過程中可顯式 START TRANSACTION/COMMIT/ROLLBACK，函數中不可用。
4. 權限最小化： 使用 SQL SECURITY INVOKER 以調用者權限執行，避免 DEFINER 權限過大。
5. 版本控制： 存儲例程代碼納入版本管理，通過遷移腳本部署，而非手動修改。
6. 性能分析： 使用 SHOW PROFILE、EXPLAIN 分析存儲過程內 SQL 性能。

總結

MySQL 存儲例程提供強大的服務端邏輯封裝能力，掌握參數模式（IN/OUT/INOUT）、流程控制、變量作用域、分隔符管理是高效開發的關鍵。實戰中應權衡「邏輯下沉帶來的性能收益」與「維護成本、調試困難、版本控制缺失」的代價，遵循「核心業務邏輯下沉、邊緣邏輯上層處理」原則。

1. 上一篇文章： MySQL 主主循環同步 (Master-Master Replication) 完整實施指南
2. 下一篇文章： MySQL Proxy 讀寫分離 Lua 腳本問題排查與解決方案

MySQL 資料庫相關知識

MySQL 儲存圖片實戰：BLOB 欄位完整操作流程

資料表設計 圖片二進制資料建議使用 BLOB 類型（依大小選

MySQL 建立資料庫的兩種標準方法：命令列工具與程式化介面

建立資料庫是 MySQL 運維與開發的高頻操作。本文對比 m

MySQL 1067 錯誤導致服務無法啟動：InnoDB 損壞後刪除 ibdata1/ib_logfile 重建實戰

MySQL 1067 錯誤急救：InnoDB 系統表空間損壞

JMeter 連接 MySQL 資料庫壓測圖文教程：JDBC 配置與 SQL 取樣器

前言：JMeter 做資料庫壓測，JDBC Request