MySQL 存儲過程邏輯控制實戰：IF 條件判斷與 CASE 分支語句詳解

一、IF 條件判斷

語法結構

IF expression THEN
    statements;
[ELSEIF expression THEN
    statements;] ...
[ELSE
    statements;]
END IF;

實戰案例：動態計算含稅價格

-- 測試表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    price DECIMAL(10,2),
    tax_rate DECIMAL(4,3) DEFAULT 0.1
);

INSERT INTO products (name, price, tax_rate) VALUES
('Sofa', 1250.00, 0.2),
('Cake', 10.00, 0.05);

-- 存儲過程：根據參數返回含稅/不含稅價格
DELIMITER //
CREATE PROCEDURE GetProductPrice(
    IN p_product_name VARCHAR(50),
    IN p_include_tax BOOLEAN,
    OUT p_final_price DECIMAL(10,2)
)
BEGIN
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_tax_rate DECIMAL(4,3);
    
    SELECT price, tax_rate INTO v_price, v_tax_rate
    FROM products WHERE name = p_product_name;
    
    IF p_include_tax THEN
        SET p_final_price = v_price * (1 + v_tax_rate);
    ELSE
        SET p_final_price = v_price;
    END IF;
END//
DELIMITER ;

-- 測試調用
CALL GetProductPrice('Sofa', TRUE, @price);
SELECT @price;  -- 1500.00 (1250 * 1.2)

CALL GetProductPrice('Cake', FALSE, @price);
SELECT @price;  -- 10.00

二、CASE 分支語句

語法結構（類似 switch-case）

CASE case_expression
    WHEN when_expression THEN statements;
    [WHEN when_expression THEN statements;] ...
    [ELSE statements;]
END CASE;

實戰案例：按產品類型動態設置稅率

-- 擴展表增加類型
ALTER TABLE products ADD COLUMN category ENUM('dessert','dairy','furniture') AFTER name;

UPDATE products SET category='furniture' WHERE name='Sofa';
UPDATE products SET category='dessert' WHERE name='Cake';

-- 存儲過程：CASE 根據類型設置稅率
DELIMITER //
CREATE PROCEDURE GetPriceByCategory(
    IN p_product_name VARCHAR(50),
    OUT p_final_price DECIMAL(10,2)
)
BEGIN
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_category VARCHAR(20);
    DECLARE v_tax_rate DECIMAL(4,3) DEFAULT 0;
    
    SELECT price, category INTO v_price, v_category
    FROM products WHERE name = p_product_name;
    
    CASE v_category
        WHEN 'dessert' THEN SET v_tax_rate = 0.05;
        WHEN 'dairy' THEN SET v_tax_rate = 0.10;
        WHEN 'furniture' THEN SET v_tax_rate = 0.20;
        ELSE SET v_tax_rate = 0;
    END CASE;
    
    SET p_final_price = v_price * (1 + v_tax_rate);
END//
DELIMITER ;

-- 測試
CALL GetPriceByCategory('Sofa', @price);
SELECT @price;  -- 1500.00

CALL GetPriceByCategory('Cake', @price);
SELECT @price;  -- 10.50

三、IF vs CASE 選擇指南

CASE 表達式用法（可用在 SELECT 中）

SELECT name,
    CASE category
        WHEN 'dessert' THEN '甜點'
        WHEN 'dairy' THEN '奶制品'
        WHEN 'furniture' THEN '家具'
        ELSE '其他'
    END AS category_cn,
    price * (1 + 
        CASE category
            WHEN 'dessert' THEN 0.05
            WHEN 'dairy' THEN 0.10
            WHEN 'furniture' THEN 0.20
            ELSE 0
        END
    ) AS price_with_tax
FROM products;

四、進階控制流語句

• LOOP ... END LOOP：無條件循環，需 LEAVE 跳出
• WHILE condition DO ... END WHILE：前置判斷循環
• REPEAT ... UNTIL condition END REPEAT：後置判斷循環
• ITERATE label：類似 continue，跳過本輪剩餘
• LEAVE label：類似 break，退出標籤塊

五、調試技巧

-- 1. 變量輸出調試
SELECT v_tax_rate INTO @debug_var;
SELECT @debug_var;

-- 2. 信息表查看
SHOW CREATE PROCEDURE GetProductPrice;
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='GetProductPrice';

-- 3. 錯誤處理
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1 @err_code = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;
    SELECT CONCAT('Error ', @err_code, ': ', @err_msg);
END;

存儲過程邏輯控制核心：**IF 處理條件分支，CASE 處理枚舉分支，CASE 表達式內嵌 SQL**。善用 DELIMITER 定義邊界，DECLARE 管理變量作用域，HANDLER 實現錯誤捕獲，編寫健壯可維護的數據庫邏輯層。