存儲過程入門：創建帶 IN/OUT/INOUT 參數的 MySQL 存儲過程完整示例

MySQL 存儲過程從零開始：語法、參數模式、調用與調試

存儲過程將業務邏輯下沉到數據庫層，減少網絡往返、防 SQL 注入、提升封裝性。本文以 person 表為例，完整演示創建、調用、參數傳遞、元數據查看。

表結構

CREATE TABLE person (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255),
  age INT,
  password VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. 無參數存儲過程

CREATE PROCEDURE sp_hello()
SELECT 'Hello, Stored Procedure!';

CALL sp_hello();

2. IN 參數（輸入，默認）

CREATE PROCEDURE sp_find_by_id(IN p_id INT)
BEGIN
    SELECT * FROM person WHERE id = p_id;
END;

SET @id = 2;
CALL sp_find_by_id(@id);

3. OUT 參數（輸出）

CREATE PROCEDURE sp_get_count(OUT p_cnt INT)
BEGIN
    SELECT COUNT(*) INTO p_cnt FROM person;
END;

CALL sp_get_count(@cnt);
SELECT @cnt AS total;

4. IN + OUT 參數

CREATE PROCEDURE sp_find_info(
    IN p_id INT,
    OUT p_name VARCHAR(255),
    OUT p_age INT
)
BEGIN
    SELECT username, age INTO p_name, p_age FROM person WHERE id = p_id;
END;

SET @id = 2;
CALL sp_find_info(@id, @name, @age);
SELECT @name, @age;

5. INOUT 參數（輸入並輸出）

CREATE PROCEDURE sp_get_age(INOUT p_id INT)
BEGIN
    SELECT age INTO p_id FROM person WHERE id = p_id;
END;

SET @id = 1;
CALL sp_get_age(@id);
SELECT @id;  -- 返回 age

6. 進階特性速覽

• 變量聲明：DECLARE v_name VARCHAR(50) DEFAULT 'test';
• 流程控制：IF ... THEN ... ELSE ... END IF;、CASE ... WHEN ... THEN ... END CASE;、WHILE ... DO ... END WHILE;、REPEAT ... UNTIL ... END REPEAT;、LOOP ... END LOOP;、ITERATE/LEAVE。
• 異常處理：DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @err = 1;
• 游標：DECLARE cur CURSOR FOR SELECT ...; OPEN cur; FETCH cur INTO ...; CLOSE cur;
• 動態 SQL：PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

7. 元數據查看

SHOW CREATE PROCEDURE sp_find_by_id;
SHOW PROCEDURE STATUS WHERE Db = 'test';

8. 刪除存儲過程

DROP PROCEDURE IF EXISTS sp_find_by_id;

最佳實踐清單

• 命名規範：sp__（如 sp_order_create）。
• 參數強類型，避免隱式轉換。
• 過程內顯式事務控制（START TRANSACTION / COMMIT / ROLLBACK），不依賴調用端。
• 大邏輯拆分為多個小過程，CALL 組合，便於單元測試。
• 監控 information_schema.ROUTINES 記錄變更歷史。

總結

掌握 IN/OUT/INOUT 三種參數模式與 BEGIN...END 複合語句，即可編寫 90% 業務存儲過程。配合流程控制、異常處理、動態 SQL，可實現複雜業務邏輯的數據庫端封裝。