摘要:在本教程中,您将学习如何使用 MySQL CASE
语句在存储过程中构造复杂的条件语句。
除了IF
语句之外,MySQL 还提供了一种称为CASE
语句的替代条件语句,用于在存储过程中构造条件语句。 CASE
语句使代码更具可读性和效率。
CASE
语句有两种形式:简单CASE
和搜索CASE
语句。
请注意,如果要将 if-else 逻辑添加到 SQL 语句中,请使用与本教程中描述的CASE
CASE
。
简单的CASE
语句
以下是简单CASE
语句的基本语法:
CASE case_value
WHEN when_value1 THEN statements
WHEN when_value2 THEN statements
...
[ELSE else-statements]
END CASE;
Code language: SQL (Structured Query Language) (sql)
在此语法中,简单的CASE
语句按顺序将case_value
与when_value1
、 when_value2
进行比较,直到发现其中一个相等。当CASE
发现case_value
等于when_value
时,它会执行相应THEN
子句中的statements
。
如果CASE
找不到任何等于case_value
的when_value
,则在ELSE
子句可用的情况下,它会执行ELSE
子句中的else-statements
。
当ELSE
子句不存在并且CASE
找不到任何等于case_value
的when_value
时,它会发出错误:
Case not found for CASE statement
Code language: PHP (php)
请注意, case_value
可以是文字值或表达式。 statements
可以是一个或多个 SQL 语句,并且不能有零个语句。
为了避免case_value
不等于任何when_value
时出现错误,您可以在ELSE
子句中使用空的BEGIN END
块,如下所示:
CASE case_value
WHEN when_value1 THEN ...
WHEN when_value2 THEN ...
ELSE
BEGIN
END;
END CASE;
Code language: SQL (Structured Query Language) (sql)
简单的CASE
语句测试相等性 ( =
),不能用它来测试与NULL
相等性;因为NULL
= NULL
返回FALSE
。
简单的CASE
语句示例
以下存储过程说明了如何使用简单的CASE
语句:
DELIMITER $$
CREATE PROCEDURE GetCustomerShipping(
IN pCustomerNUmber INT,
OUT pShipping VARCHAR(50)
)
BEGIN
DECLARE customerCountry VARCHAR(100);
SELECT
country
INTO customerCountry FROM
customers
WHERE
customerNumber = pCustomerNUmber;
CASE customerCountry
WHEN 'USA' THEN
SET pShipping = '2-day Shipping';
WHEN 'Canada' THEN
SET pShipping = '3-day Shipping';
ELSE
SET pShipping = '5-day Shipping';
END CASE;
END$$
DELIMITER ;
Code language: PHP (php)
怎么运行的。
GetCustomerShipping()
存储过程接受两个参数: pCustomerNumber
作为IN
参数, pShipping
作为OUT
参数。
在存储过程中:
首先,通过输入客户编号从customers
表中选择客户所在的国家/地区。
其次,使用简单的CASE
语句根据客户所在的国家/地区确定运输时间。如果客户位于USA
,则运送时间为2-day shipping
。如果客户位于Canada
,运送时间为3-day shipping
。来自其他国家的客户有5-day shipping
。
下面的流程图演示了确定运输时间的 CASE 语句的逻辑:
该语句调用存储过程并传递客户号码 112:
CALL GetCustomerShipping(112,@shipping);
Code language: SQL (Structured Query Language) (sql)
以下语句返回客户 112 的发货时间:
SELECT @shipping;
Code language: SQL (Structured Query Language) (sql)
这是输出:
+----------------+
| @shipping |
+----------------+
| 2-day Shipping |
+----------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
搜索CASE
语句
简单的CASE
语句仅允许您将一个值与一组不同的值进行比较。
要执行更复杂的匹配(例如范围),可以使用搜索的CASE
语句。搜索的CASE
语句与IF
语句等效,但它比IF
语句更具可读性。
以下是搜索的CASE
语句的基本语法:
CASE
WHEN search_condition1 THEN statements
WHEN search_condition1 THEN statements
...
[ELSE else-statements]
END CASE;
Code language: SQL (Structured Query Language) (sql)
在此语法中,搜索的CASE
计算WHEN
子句中的每个search_condition
,直到找到计算结果为TRUE
条件,然后执行相应的THEN
子句statements
。
如果没有search_condition
计算为TRUE
,则CASE
将执行ELSE
子句中的else-statements
(如果ELSE
子句可用)。
与简单的CASE
语句类似,如果您不指定ELSE
子句并且没有条件为TRUE
,MySQL 会引发相同的错误:
Case not found for CASE statement
Code language: PHP (php)
MySQL 也不允许在THEN
或ELSE
子句中包含空statements
。如果您不想处理ELSE
子句中的逻辑,同时又不想在没有search_condition
为 true 的情况下阻止 MySQL 引发错误,则可以在ELSE
子句中使用空的BEGIN END
块。
搜索CASE
语句示例
以下示例演示如何使用搜索CASE
语句根据客户的信用额度查找客户级别SILVER
、 GOLD
或PLATINUM
。
DELIMITER $$
CREATE PROCEDURE GetDeliveryStatus(
IN pOrderNumber INT,
OUT pDeliveryStatus VARCHAR(100)
)
BEGIN
DECLARE waitingDay INT DEFAULT 0;
SELECT
DATEDIFF(requiredDate, shippedDate)
INTO waitingDay
FROM orders
WHERE orderNumber = pOrderNumber;
CASE
WHEN waitingDay = 0 THEN
SET pDeliveryStatus = 'On Time';
WHEN waitingDay >= 1 AND waitingDay < 5 THEN
SET pDeliveryStatus = 'Late';
WHEN waitingDay >= 5 THEN
SET pDeliveryStatus = 'Very Late';
ELSE
SET pDeliveryStatus = 'No Information';
END CASE;
END$$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
怎么运行的。
存储过程GetDeliveryStatus()
接受订单号作为IN
参数,并返回交货状态作为OUT
参数。
首先,计算所需日期和发货日期之间的天数。
其次,使用搜索到的CASE
语句根据等待天数确定递送状态:
- 如果等待天数为零,则交货准时。
- 当等待天数在 1 到 5 之间时,交货会延迟。
- 当等待天数超过5天时,交货就很晚了。
- 如果等待天数为 NULL 或其他,则交货状态为
ELSE
子句中指定的无信息状态。
此语句使用存储过程GetDeliveryStatus()
来获取订单10100
的配送状态:
CALL GetDeliveryStatus(10100,@delivery);
Code language: SQL (Structured Query Language) (sql)
结果如下:
MySQL CASE
与IF
IF
和CASE
语句都允许您根据特定条件执行代码块。在IF
或CASE
之间进行选择有时只是个人喜好的问题。以下是一些准则:
- 当您将单个表达式与一系列唯一值进行比较时,简单的
CASE
语句比IF
语句更具可读性和效率。 - 当您检查基于多个值的复杂表达式时,
IF
语句更容易理解。 - 如果使用
CASE
语句,则必须确保至少有一个CASE
条件匹配。否则,您需要定义一个错误处理程序来捕获错误。请注意,您不必使用IF
语句来执行此操作。 - 在某些情况下,您可以同时使用
IF
和CASE
来使代码更具可读性和效率。
在本教程中,您学习了如何使用两种形式的 MySQL CASE
语句,包括简单CASE
语句和搜索CASE
语句。