MySQL 存儲過程變量與條件處理深度實戰

MySQL 存儲過程變量與條件處理深度實戰

變量與條件處理是編寫健壯存儲過程的基石。本文通過完整可運行示例，系統講解 DECLARE/SET/SELECT ... INTO 三種賦值方式、會話變量與局部變量區別、條件定義與處理器（CONTINUE/EXIT/UNDO）的實戰模式。

一、變量聲明與賦值三板斧

1. DECLARE 局部變量（僅在 BEGIN...END 內有效）

CREATE PROCEDURE sp_demo(IN p_dept INT, OUT p_cnt INT)
BEGIN
  DECLARE v_name VARCHAR(20) DEFAULT 'Chen';  -- 無 @ 符號
  DECLARE v_id INT DEFAULT 0;
  -- 業務邏輯
  SELECT COUNT(*) INTO p_cnt FROM employee WHERE depid = p_dept;
END;

2. SET 賦值（靈活、可定義會話變量）

-- 給已聲明局部變量賦值
SET v_name = 'Wang';

-- 直接定義並賦值會話變量（帶 @，跨存儲過程可見）
SET @session_var = 100;

3. SELECT ... INTO 賦值（查詢結果直接入變量）

SELECT COUNT(*) INTO v_id FROM employee WHERE depid = p_dept AND name = v_name;
-- 也可直接賦給 OUT 參數
SELECT COUNT(*) INTO p_cnt FROM employee WHERE depid = p_dept;

二、條件處理器：讓錯誤可控

核心語法

DECLARE handler_type HANDLER FOR condition_value sp_statement
handler_type: CONTINUE | EXIT | UNDO
condition_value:
  SQLSTATE '23000'        -- 具體 SQL 狀態碼
  | 1062                  -- MySQL 錯誤碼
  | condition_name        -- 自定義條件名
  | SQLWARNING            -- 01 開頭狀態碼
  | NOT FOUND             -- 02 開頭狀態碼
  | SQLEXCEPTION          -- 其餘所有錯誤

實戰模式對比

完整事務控制範本

CREATE PROCEDURE sp_batch_insert()
BEGIN
  DECLARE v_err INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_err = 1;
  
  START TRANSACTION;
  INSERT INTO t1 VALUES (1, 'a');
  INSERT INTO t1 VALUES (2, 'b');  -- 假設此處主鍵衝突
  INSERT INTO t1 VALUES (3, 'c');
  
  IF v_err = 1 THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;
END;

三、常見陷阱與最佳實踐

1. 局部變量名勿加 @： 加 @ 會被解析為會話變量，導致作用域混亂。
2. DECLARE 必須置於 BEGIN 之後、所有業務語句之前，否則報錯。
3. SELECT ... INTO 若查無結果，變量保持原值且觸發 NOT FOUND 條件，建議配合 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=1; 處理。
4. 生產環境禁止使用 UNDO handler（MySQL 尚未實現）。
5. 條件處理器聲明順序決定優先級：SQLSTATE > mysql_error_code > SQLEXCEPTION，具體優先匹配。

掌握變量作用域與條件處理器組合拳，能讓存儲過程從「能跑」進化為「可控、可觀測、可恢復」的生產級組件。