MySQL 存儲過程完整指南：建立、調用、控制流、游標、異常處理、函數大全

為什麼使用存儲過程

• ✅ **預編譯**：首次執行優化生成執行計劃，後續直接複用，減少解析優化開銷。
• ✅ **網路流量**：客戶端僅發送 CALL proc()，大批量 SQL 不走網路。
• ✅ **安全性**：通過 GRANT EXECUTE 控制存取，隱藏表結構與業務邏輯。
• ✅ **復用性**：多應用/多語言共享同一邏輯，修改集中在資料庫層。

建立語法核心

DELIMITER //
CREATE PROCEDURE `proc_name`(
  IN  p_in   INT,
  OUT p_out  VARCHAR(100),
  INOUT p_io DATE
)
BEGIN
  -- 宣告區
  DECLARE v_cnt INT DEFAULT 0;
  DECLARE v_name VARCHAR(50);
  
  -- 邏輯區
  SELECT COUNT(*) INTO v_cnt FROM users WHERE id = p_in;
  IF v_cnt > 0 THEN
    SELECT name INTO v_name FROM users WHERE id = p_in;
    SET p_out = v_name;
  ELSE
    SET p_out = 'NOT_FOUND';
  END IF;
  SET p_io = DATE_ADD(p_io, INTERVAL 1 DAY);
END//
DELIMITER ;

關鍵點

• DELIMITER // 避免分號提前結束定義，結尾 DELIMITER ; 恢復。
• 參數模式：IN（預設）、OUT、INOUT。
• 區域變量：DECLARE var TYPE [DEFAULT val]; 必須在 BEGIN 後、首條 SQL 前。
• 賦值：SET var = expr; 或 SELECT col INTO var FROM ...

調用與查看

-- 調用
SET @p_in = 1; SET @p_io = CURDATE();
CALL proc_name(@p_in, @p_out, @p_io);
SELECT @p_out, @p_io;

-- 查看定義
SHOW CREATE PROCEDURE proc_name;

-- 列出庫內所有過程
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'mydb';

-- 狀態
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

控制流語句

條件：IF / CASE

IF condition THEN ... ELSEIF ... ELSE ... END IF;

CASE var
  WHEN 1 THEN ...
  WHEN 2 THEN ...
  ELSE ...
END CASE;

迴圈：WHILE / REPEAT / LOOP

-- WHILE：先判斷
WHILE v < 10 DO ... END WHILE;

-- REPEAT：後判斷（至少執行一次）
REPEAT ... UNTIL v >= 10 END REPEAT;

-- LOOP：無條件，配合 LEAVE/ITERATE
my_loop: LOOP
  IF v = 3 THEN SET v = v + 1; ITERATE my_loop; END IF;
  IF v >= 10 THEN LEAVE my_loop; END IF;
  ...
END LOOP;

游標

DECLARE cur CURSOR FOR SELECT id, name FROM users WHERE status = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
  FETCH cur INTO v_id, v_name;
  IF done THEN LEAVE read_loop; END IF;
  -- 處理邏輯
END LOOP;
CLOSE cur;

異常處理

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  INSERT INTO error_log(msg) VALUES ('SQL Exception in proc_name');
  ROLLBACK;
END;

DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
  INSERT INTO warn_log(msg) VALUES (CONCAT('Warning: ', @@warning_count));
END;

條件值：SQLSTATE、SQLWARNING、NOT FOUND、SQLEXCEPTION、MySQL 錯誤碼。

常用內建函數速查

存儲函數

DELIMITER //
CREATE FUNCTION `fn_greet`(p_name VARCHAR(50)) RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
  RETURN CONCAT('Hello, ', p_name, '!');
END//
DELIMITER ;

SELECT fn_greet('World');  -- Hello, World!

函數必須 RETURNS 類型，體內至少一條 RETURN。

修改與刪除

ALTER PROCEDURE proc_name [characteristic ...];
DROP PROCEDURE IF EXISTS proc_name;