MySQL存储过程之：`存储过程`中的`条件`控制：`IF…THEN…ELSE`和`CASE`语句。

MySQL存储过程中的条件控制：IF…THEN…ELSE 和 CASE 语句

大家好，今天我们来深入探讨MySQL存储过程中非常重要的组成部分：条件控制语句。掌握这些语句，可以让我们编写出更加灵活、智能的存储过程，处理各种复杂业务逻辑。我们将重点讲解 IF...THEN...ELSE 和 CASE 这两种常用的条件控制结构，并结合实际案例进行分析。

1. IF…THEN…ELSE 语句

IF...THEN...ELSE 语句是最基本的条件判断结构，它根据条件的真假来执行不同的代码块。其基本语法如下：

IF condition THEN
  -- 如果条件为真，则执行这里的代码
ELSEIF condition2 THEN
  -- 如果condition为假，condition2为真，则执行这里的代码
ELSE
  -- 如果所有条件都为假，则执行这里的代码
END IF;

其中：

• condition：是一个布尔表达式，结果为 TRUE、FALSE 或 NULL。
• THEN：关键字，表示条件为真时执行的代码块的开始。
• ELSEIF：可选的关键字，用于添加额外的条件判断。可以有多个 ELSEIF 分支。
• ELSE：可选的关键字，表示所有条件都为假时执行的代码块的开始。
• END IF：关键字，表示 IF 语句块的结束。

重要说明：

• ELSEIF 在 MySQL 中也可以写成 ELSE IF，两者效果一样。
• IF 语句可以嵌套使用，即在一个 IF 语句块中包含另一个 IF 语句。
• 如果 condition 的结果为 NULL，则被视为 FALSE。

示例 1：判断用户年龄并输出不同信息

假设我们有一个 users 表，包含 id、name 和 age 字段。现在要编写一个存储过程，根据用户的年龄输出不同的信息。

DROP PROCEDURE IF EXISTS sp_get_user_info;

DELIMITER //

CREATE PROCEDURE sp_get_user_info(IN user_id INT)
BEGIN
  DECLARE user_age INT;

  SELECT age INTO user_age FROM users WHERE id = user_id;

  IF user_age < 18 THEN
    SELECT '未成年人' AS message;
  ELSEIF user_age >= 18 AND user_age < 60 THEN
    SELECT '成年人' AS message;
  ELSE
    SELECT '老年人' AS message;
  END IF;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_get_user_info(1);  -- 假设用户ID为1

在这个例子中，我们首先声明一个变量 user_age，然后从 users 表中查询指定用户的年龄，并将其赋值给 user_age。接着，使用 IF...THEN...ELSEIF...ELSE 语句判断 user_age 的值，并根据不同的年龄段输出不同的信息。

示例 2：更新商品库存，并判断库存是否充足

假设我们有一个 products 表，包含 id、name 和 stock 字段。现在要编写一个存储过程，用于更新商品的库存，并判断库存是否充足。

DROP PROCEDURE IF EXISTS sp_update_stock;

DELIMITER //

CREATE PROCEDURE sp_update_stock(IN product_id INT, IN quantity INT)
BEGIN
  DECLARE current_stock INT;

  -- 获取当前库存
  SELECT stock INTO current_stock FROM products WHERE id = product_id;

  -- 判断库存是否充足
  IF current_stock >= quantity THEN
    -- 更新库存
    UPDATE products SET stock = stock - quantity WHERE id = product_id;
    SELECT '更新成功' AS message;
  ELSE
    SELECT '库存不足' AS message;
  END IF;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_update_stock(1, 10);  -- 假设商品ID为1，减少数量为10

在这个例子中，我们首先获取当前库存，然后判断库存是否大于等于要减少的数量。如果库存充足，则更新库存并输出“更新成功”；否则，输出“库存不足”。

示例 3：嵌套 IF 语句，实现更复杂的逻辑

DROP PROCEDURE IF EXISTS sp_check_order;

DELIMITER //

CREATE PROCEDURE sp_check_order(IN order_id INT)
BEGIN
  DECLARE order_status VARCHAR(20);
  DECLARE payment_status VARCHAR(20);

  -- 获取订单状态和支付状态（假设从 orders 表中获取）
  SELECT status, payment_status INTO order_status, payment_status
  FROM orders
  WHERE id = order_id;

  IF order_status = 'pending' THEN
    IF payment_status = 'paid' THEN
      SELECT '订单待处理，已支付' AS message;
    ELSE
      SELECT '订单待处理，未支付' AS message;
    END IF;
  ELSEIF order_status = 'shipped' THEN
    SELECT '订单已发货' AS message;
  ELSE
    SELECT '订单已完成或已取消' AS message;
  END IF;

END //

DELIMITER ;

这个例子展示了 IF 语句的嵌套使用，用于处理更复杂的业务逻辑。 首先判断订单状态，然后在 pending 状态下，再判断支付状态。

2. CASE 语句

CASE 语句也用于条件判断，但它比 IF...THEN...ELSE 语句更加灵活，可以处理更复杂的情况。CASE 语句有两种形式：简单 CASE 语句和搜索 CASE 语句。

2.1 简单 CASE 语句

简单 CASE 语句将一个表达式的值与多个可能的值进行比较，并根据匹配的值执行相应的代码块。其基本语法如下：

CASE expression
  WHEN value1 THEN
    -- 如果 expression = value1，则执行这里的代码
  WHEN value2 THEN
    -- 如果 expression = value2，则执行这里的代码
  ...
  ELSE
    -- 如果 expression 与所有 value 都不匹配，则执行这里的代码
END CASE;

其中：

• expression：要进行比较的表达式。
• WHEN：关键字，表示一个可能的值。
• value1、value2 等：要与 expression 进行比较的值。
• ELSE：可选的关键字，表示如果 expression 与所有 value 都不匹配时执行的代码块的开始。
• END CASE：关键字，表示 CASE 语句块的结束。

示例 1：根据用户等级输出不同信息

假设我们有一个 users 表，包含 id、name 和 level 字段（level为字符串类型，例如 ‘A’, ‘B’, ‘C’）。现在要编写一个存储过程，根据用户的等级输出不同的信息。

DROP PROCEDURE IF EXISTS sp_get_user_level_info;

DELIMITER //

CREATE PROCEDURE sp_get_user_level_info(IN user_id INT)
BEGIN
  DECLARE user_level VARCHAR(10);

  SELECT level INTO user_level FROM users WHERE id = user_id;

  CASE user_level
    WHEN 'A' THEN
      SELECT '高级用户' AS message;
    WHEN 'B' THEN
      SELECT '中级用户' AS message;
    WHEN 'C' THEN
      SELECT '普通用户' AS message;
    ELSE
      SELECT '未知用户' AS message;
  END CASE;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_get_user_level_info(1);  -- 假设用户ID为1

在这个例子中，我们首先获取用户的等级，然后使用 CASE 语句根据等级的值输出不同的信息。

2.2 搜索 CASE 语句

搜索 CASE 语句根据条件的真假来执行不同的代码块，类似于 IF...THEN...ELSE 语句。其基本语法如下：

CASE
  WHEN condition1 THEN
    -- 如果 condition1 为真，则执行这里的代码
  WHEN condition2 THEN
    -- 如果 condition2 为真，则执行这里的代码
  ...
  ELSE
    -- 如果所有条件都为假，则执行这里的代码
END CASE;

其中：

• WHEN：关键字，表示一个条件。
• condition1、condition2 等：布尔表达式，结果为 TRUE、FALSE 或 NULL。
• ELSE：可选的关键字，表示如果所有条件都为假时执行的代码块的开始。
• END CASE：关键字，表示 CASE 语句块的结束。

示例 1：判断订单金额并给予不同折扣

假设我们有一个 orders 表，包含 id 和 amount 字段。现在要编写一个存储过程，根据订单金额给予不同的折扣。

DROP PROCEDURE IF EXISTS sp_apply_discount;

DELIMITER //

CREATE PROCEDURE sp_apply_discount(IN order_id INT)
BEGIN
  DECLARE order_amount DECIMAL(10, 2);
  DECLARE discount_rate DECIMAL(5, 2);

  SELECT amount INTO order_amount FROM orders WHERE id = order_id;

  CASE
    WHEN order_amount >= 1000 THEN
      SET discount_rate = 0.10;  -- 10% 折扣
    WHEN order_amount >= 500 THEN
      SET discount_rate = 0.05;  -- 5% 折扣
    ELSE
      SET discount_rate = 0.00;  -- 无折扣
  END CASE;

  -- 更新订单表中的折扣率 (假设 orders 表中有 discount_rate 字段)
  UPDATE orders SET discount_rate = discount_rate WHERE id = order_id;

  SELECT discount_rate AS discount;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_apply_discount(1);  -- 假设订单ID为1

在这个例子中，我们首先获取订单金额，然后使用搜索 CASE 语句根据金额的大小设置不同的折扣率。

示例 2：根据日期计算季度

DROP PROCEDURE IF EXISTS sp_get_quarter;

DELIMITER //

CREATE PROCEDURE sp_get_quarter(IN input_date DATE)
BEGIN
  DECLARE quarter INT;
  DECLARE month INT;

  SET month = MONTH(input_date);

  CASE
    WHEN month BETWEEN 1 AND 3 THEN
      SET quarter = 1;
    WHEN month BETWEEN 4 AND 6 THEN
      SET quarter = 2;
    WHEN month BETWEEN 7 AND 9 THEN
      SET quarter = 3;
    WHEN month BETWEEN 10 AND 12 THEN
      SET quarter = 4;
    ELSE
      SET quarter = NULL; -- 处理无效月份的情况
  END CASE;

  SELECT quarter AS quarter_of_year;

END //

DELIMITER ;

CALL sp_get_quarter('2023-05-15');

这个例子演示了如何使用 CASE 语句根据日期计算季度。

3. IF…THEN…ELSE 与 CASE 的选择

IF...THEN...ELSE 和 CASE 语句都可以用于条件判断，那么在实际开发中，应该如何选择呢？

• 简单的条件判断： 如果只需要判断一个条件，或者多个条件之间是互斥的，可以使用 IF...THEN...ELSE 语句。

• 复杂的条件判断： 如果需要判断多个条件，并且这些条件之间可能存在交叉，或者需要根据一个表达式的值与多个可能的值进行比较，可以使用 CASE 语句。CASE 语句通常比嵌套的 IF 语句更易读，更容易维护。

• 性能考虑： 在大多数情况下，IF...THEN...ELSE 和 CASE 语句的性能差异可以忽略不计。但是，在某些特殊情况下，例如需要判断大量的条件，CASE 语句可能会比 IF...THEN...ELSE 语句更有效率。

总的来说，选择哪种语句取决于具体的业务需求和个人偏好。建议在编写存储过程时，优先考虑代码的可读性和可维护性。

4. 条件控制中的 NULL 值处理

在条件判断中，NULL 值的处理需要特别注意。因为 NULL 值既不等于 TRUE，也不等于 FALSE，它表示一个未知的值。

• IF 语句中的 NULL 值： 在 IF 语句中，如果 condition 的结果为 NULL，则被视为 FALSE。

• CASE 语句中的 NULL 值： 在 CASE 语句中，需要使用 IS NULL 或 IS NOT NULL 来判断一个表达式是否为 NULL。

示例：处理 NULL 值的条件判断

DROP PROCEDURE IF EXISTS sp_handle_null;

DELIMITER //

CREATE PROCEDURE sp_handle_null(IN input_value INT)
BEGIN
  IF input_value IS NULL THEN
    SELECT '输入值为 NULL' AS message;
  ELSE
    SELECT '输入值不为 NULL' AS message;
  END IF;

  CASE
    WHEN input_value IS NULL THEN
      SELECT 'CASE: 输入值为 NULL' AS message;
    ELSE
      SELECT 'CASE: 输入值不为 NULL' AS message;
  END CASE;
END //

DELIMITER ;

CALL sp_handle_null(NULL);
CALL sp_handle_null(10);

这个例子展示了如何在 IF 语句和 CASE 语句中处理 NULL 值。 需要使用 IS NULL 运算符来判断变量是否为 NULL。

总结：选择合适的控制语句编写更健壮的存储过程

本文详细介绍了MySQL存储过程中常用的条件控制语句 IF...THEN...ELSE 和 CASE。我们通过大量的示例，讲解了它们的基本语法、使用场景和注意事项。希望通过今天的学习，大家能够掌握这些语句，编写出更加灵活、智能的存储过程，提升数据库应用的开发效率和质量。