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

MySQL 存儲過程實戰大全：參數模式、游標、流程控制、異常處理與循環構造

編輯：MySQL 綜合教學

MySQL 存儲過程實戰大全

存儲過程是將業務邏輯下沉到數據庫層的核心手段，支援參數傳遞、流程控制、游標遍歷、異常捕獲等完整程式化能力。本文通過大量可運行案例，系統演示 IN/OUT/INOUT 參數、條件判斷、循環、游標、異常處理器、動態 SQL 等核心特性。

基礎環境

USE test1;
CREATE TABLE emp (ename VARCHAR(10), hiredate DATE, sal DECIMAL(10,2), deptno INT(2)) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO emp VALUES ('aaaaa',NULL,NULL,1),('cccccccccc',NULL,NULL,2),('ddddddddd',NULL,NULL,3),('ffffff',NULL,NULL,4),('ggg',NULL,NULL,5),('a1',NULL,NULL,5);

一、參數模式與基本調用

DELIMITER &&
CREATE PROCEDURE num_from_employee (IN input_deptno INT, OUT count_num INT)
READS SQL DATA
BEGIN
  SELECT COUNT(*) FROM emp WHERE deptno = input_deptno;
END &&
DELIMITER ;

CALL num_from_employee(5, @a);  -- 返回 2
CALL num_from_employee(1, @a);  -- 返回 1

IN：輸入參數
OUT：輸出參數（需用戶變數 @var 接收）
INOUT：雙向
READS SQL DATA：聲明特性，優化器參考

二、游標遍歷與流程控制

CREATE TABLE inventory (film_id INT, store_id INT, inventory_in_stock VARCHAR(50)) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO inventory VALUES (1,2,'aaaaaaaa'),(3,4,'bbbb'),(5,6,'cccccccccc'),(7,8,'dddddd'),(9,10,'fff');

DELIMITER $$
CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
READS SQL DATA
BEGIN
  SELECT film_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id;
  SELECT FOUND_ROWS() INTO p_film_count;
END $$
DELIMITER ;

CALL film_in_stock(5,6,@a);  -- 返回 1 行，@a=1

三、異常處理器（DECLARE HANDLER）

CREATE TABLE actor (actor_id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(30), last_name VARCHAR(30)) ENGINE=InnoDB CHARSET=utf8;

-- 無處理器：主鍵衝突直接報錯，後續語句不執行
CREATE PROCEDURE actor_insert()
BEGIN
  SET @x = 1; INSERT INTO actor VALUES (201,'Test',201);
  SET @x = 2; INSERT INTO actor VALUES (1,'Test','1');  -- 衝突拋錯
  SET @x = 3;  -- 不會執行
END;

-- 有 CONTINUE HANDLER：捕獲 23000 (主鍵衝突)，設置標記，繼續執行
CREATE PROCEDURE actor_insert_new()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
  SET @x = 1; INSERT INTO actor VALUES (201,'Test',201);
  SET @x = 2; INSERT INTO actor VALUES (1,'Test','1');  -- 衝突，觸發 handler
  SET @x = 3;  -- 正常執行
END;

CALL actor_insert_new();  -- @x=3, @x2=1

CONTINUE：處理後繼續執行後續語句
EXIT：處理後退出當前 BEGIN...END 塊
條件類型：SQLSTATE、MYSQL_ERRNO、SQLEXCEPTION、SQLWARNING、NOT FOUND

四、游標 + 循環 + 條件判斷綜合案例

CREATE TABLE payment (staff_id INT, amount INT) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO payment VALUES (1,10000),(2,20000),(3,30000),(4,400000),(5,500000);

DELIMITER $$
CREATE PROCEDURE payment_stat()
BEGIN
  DECLARE i_staff_id INT;
  DECLARE d_amount INT;
  DECLARE cur_payment CURSOR FOR SELECT staff_id, amount FROM payment;
  DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;

  SET @x1 = 0; SET @x2 = 0;
  OPEN cur_payment;
  FETCH cur_payment INTO i_staff_id, d_amount;
  WHILE i_staff_id <= 3 DO
    IF i_staff_id < 3 THEN
      SET @x1 = @x1 + i_staff_id;
    ELSE
      SET @x2 = @x2 + d_amount;
    END IF;
    FETCH cur_payment INTO i_staff_id, d_amount;
  END WHILE;
  CLOSE cur_payment;
  SELECT @x1, @x2;
END $$
DELIMITER ;

CALL payment_stat();  -- @x1=3 (1+2), @x2=30000

五、循環構造對比

構造	特點	適用場景
`WHILE condition DO ... END WHILE`	先判斷後執行	已知次數或條件控制
`REPEAT ... UNTIL condition END REPEAT`	先執行後判斷（至少一次）	至少執行一次
`LOOP ... END LOOP` + `LEAVE`/`ITERATE`	無條件無限循環，靈活控制	複雜跳轉邏輯

-- WHILE 示例：輸出 1-30 間偶數
CREATE PROCEDURE addNum()
BEGIN
  DECLARE x INT DEFAULT 0;
  for_loop: LOOP
    SET x = x + 1;
    IF x > 30 THEN LEAVE for_loop; END IF;
    IF MOD(x,2) = 0 THEN SELECT 'num:', x; ITERATE for_loop; END IF;
  END LOOP;
  SELECT 'count:', x;
END;

-- REPEAT 示例
CREATE PROCEDURE repeatPractise()
BEGIN
  SET @v = 0;
  REPEAT SET @v = @v + 1; UNTIL @v >= 5 END REPEAT;
END;