MySQL 存儲過程進階：變量聲明、賦值與條件處理機制

變量系統

1. 局部變量聲明 (DECLARE)

DECLARE var_name [, var_name ...] type [DEFAULT value];

-- 示例
DECLARE last_day_start DATE;
DECLARE v_payment DECIMAL(10,2) DEFAULT 0.00;
DECLARE v_count INT DEFAULT 0;

• 作用域：BEGIN ... END 塊內，支持嵌套塊
• 必須在 BEGIN 後、其他語句前聲明
• 類型同表列類型 (INT, VARCHAR, DATE, DECIMAL 等)

2. 變量賦值

直接賦值 (SET)

SET var_name = expr [, var_name = expr ...];

-- 示例
SET last_day_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
SET v_count = v_count + 1, v_payment = v_payment + 100.50;

查詢結果賦值 (SELECT ... INTO)

SELECT col_name [, ...] INTO var_name [, ...] FROM table_expr [WHERE ...];

-- 示例
DECLARE v_payment DECIMAL(10,2);
SELECT IFNULL(SUM(amount), 0) INTO v_payment FROM payment WHERE staff_id = 2;

• 查詢必須返回**恰好 1 行**，否則報錯
• 配合 LIMIT 1 或聚合函數保證單行

3. 用戶變量 (@var) vs 局部變量

條件處理機制

1. 條件定義 (DECLARE CONDITION)

DECLARE condition_name CONDITION FOR condition_value;

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

-- 示例：定義重複鍵錯誤條件
DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';
-- 或
DECLARE duplicate_key CONDITION FOR 1062;

2. 條件處理器 (DECLARE HANDLER)

DECLARE handler_type HANDLER FOR condition_value [, ...] sp_statement;

handler_type:
    CONTINUE  -- 執行完處理語句，繼續後續代碼
  | EXIT      -- 執行完處理語句，退出當前 BEGIN...END 塊
  | UNDO      -- (保留字，MySQL 暫未實現)

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING      -- 所有 01xxx 警告
  | NOT FOUND       -- 02xxx (游標無數據、SELECT INTO 無行)
  | SQLEXCEPTION    -- 所有其他錯誤
  | mysql_error_code

3. 實戰示例

DELIMITER $$

CREATE PROCEDURE process_payment(IN p_staff_id INT)
BEGIN
    DECLARE v_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE v_count INT DEFAULT 0;
    DECLARE duplicate_key CONDITION FOR 1062;
    DECLARE CONTINUE HANDLER FOR duplicate_key 
        SET @dup_warn = 1;  -- 記錄警告繼續執行
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction rolled back' AS msg;
    END;

    START TRANSACTION;
    
    SELECT SUM(amount) INTO v_amount FROM payment WHERE staff_id = p_staff_id;
    SELECT COUNT(*) INTO v_count FROM payment WHERE staff_id = p_staff_id;
    
    -- 業務邏輯...
    
    INSERT INTO payment_summary (staff_id, total_amount, txn_count, summary_date)
    VALUES (p_staff_id, v_amount, v_count, CURDATE())
    ON DUPLICATE KEY UPDATE 
        total_amount = total_amount + v_amount,
        txn_count = txn_count + v_count;
    
    COMMIT;
END $$

DELIMITER ;

聲明順序規則 (嚴格)！

常用 SQLSTATE 代碼速查