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

MySQL存儲過程入門教程（一）：核心概念與基本操作詳解

編輯：MySQL 綜合教學

MySQL存儲過程入門教程（一）：核心概念與基本操作詳解

自MySQL 5.0版本起，存儲過程正式成為數據庫的核心特性之一，極大地增強了MySQL的數據處理能力。本文將系統講解MySQL存儲過程的基礎知識，幫助讀者快速上手。

什麼是存儲過程？

存儲過程是一組為了完成特定功能的SQL語句集合，經過編譯後存儲在數據庫中。用戶通過指定存儲過程名稱並提供參數（如有）來調用執行。它不僅可被應用程序單次調用執行，還支持變量聲明、參數輸入輸出、返回狀態值以及嵌套調用等高級特性。

存儲過程的主要優勢

減少網絡通信： 尤其當存儲過程包含大量SQL語句時，一次調用即可替代多次網絡往返，性能提升顯著。
執行速度更快： 創建時已完成解析優化，首次執行後駐留內存，後續調用可直接從內存讀取。
增強安全性： 通過授予執行權限而非直接表權限，可有效防止SQL注入，實現精細的數據訪問控制。
業務邏輯封裝： 將業務邏輯集中在數據庫層，便於維護且執行效率高。

缺點方面，存儲過程的可移植性較弱（遷移數據庫需修改代碼），且需要投入時間學習特定語法。

開發工具推薦使用 MySQL Query Browser（官方工具）進行存儲過程的開發與管理。

1. 定義存儲過程結束符

由於存儲過程包含多條以分號結尾的SQL語句，必須先自定義一個不衝突的結束符（如 //），使用語法：DELIMITER //。

2. 創建存儲過程

DELIMITER //
CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
    SELECT 'Hello World !';
END//

關鍵字解析：

CREATE PROCEDURE：創建存儲過程，後接名稱。
LANGUAGE SQL：指定使用SQL語言（默認）。
DETERMINISTIC：聲明相同輸入必產生相同輸出，優化器可利用此特性；默認為 NOT DETERMINISTIC。
SQL SECURITY DEFINER/INVOKER：指定權限檢查時機。DEFINER（默認）為創建者權限，INVOKER為調用者權限。
COMMENT：註釋說明。
BEGIN ... END：存儲過程主體。

3. 調用存儲過程

使用 CALL 命令：CALL stored_procedure_name(param1, param2, ...);。

4. 修改與刪除存儲過程

修改特征： ALTER PROCEDURE proc_name [characteristic ...]。修改主體需先刪除再重建。
刪除： DROP PROCEDURE IF EXISTS proc_name;。

5. 存儲過程參數模式

IN：輸入參數（默認，可省略）。
OUT：輸出參數。
INOUT：輸入輸出參數。

示例：

-- IN 參數
CREATE PROCEDURE `proc_IN` (IN var1 INT)
BEGIN SELECT var1 + 2 AS result; END//

-- OUT 參數
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN SET var1 = 'This is a test'; END//

-- INOUT 參數
CREATE PROCEDURE `proc_INOUT` (INOUT var1 INT)
BEGIN SET var1 = var1 * 2; END//

6. 變量定義與使用

語法：DECLARE var_name DATA_TYPE [DEFAULT default_value];

DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;

聲明後可在 BEGIN...END 塊中賦值、查詢、插入等操作。

7. 流程控制語句

MySQL支持 IF、CASE、WHILE、REPEAT、LOOP、ITERATE、LEAVE 等結構。

IF 語句

IF variable1 = 0 THEN
    SELECT variable1;
END IF;

IF param1 = 0 THEN
    SELECT 'Parameter value = 0';
ELSE
    SELECT 'Parameter value <= 0';
END IF;

CASE 語句（兩種寫法）

-- 簡單 CASE
CASE variable1
    WHEN 0 THEN INSERT INTO table1 VALUES (param1);
    WHEN 1 THEN INSERT INTO table1 VALUES (variable1);
    ELSE INSERT INTO table1 VALUES (99);
END CASE;

-- 搜索 CASE
CASE
    WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1);
    WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1);
    ELSE INSERT INTO table1 VALUES (99);
END CASE;

WHILE 循環

WHILE variable1 < 10 DO
    INSERT INTO table1 VALUES (param1);
    SET variable1 = variable1 + 1;
END WHILE;

8. 游標使用

游標用於逐行處理查詢結果集。語法步驟：

DECLARE cursor_name CURSOR FOR SELECT ...; 聲明游標。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var = 1; 定義遍歷結束處理。
OPEN cursor_name; 打開游標。
FETCH cursor_name INTO var_list; 取數據到變量。
CLOSE cursor_name; 關閉游標。

CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
BEGIN
    DECLARE a, b, c INT;
    DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    OPEN cur1;
    SET b = 0; SET c = 0;
    WHILE b = 0 DO
        FETCH cur1 INTO a;
        IF b = 0 THEN SET c = c + a; END IF;
    END WHILE;
    CLOSE cur1;
    SET param1 = c;
END //

注意：游標只能向前遍歷，不可更新，用完必須關閉。