mysql存储过程之case语句用法实例详解

本文实例讲述了mysql存储过程之case语句用法。分享给大家供大家参考，具体如下：

除了if语句，mysql提供了一个替代的条件语句case。 mysql的 case语句使代码更加可读和高效。废话不多说，我们先来看下简单case语句的语法：

case case_expression
  when when_expression_1 then commands
  when when_expression_2 then commands
  ...
  else commands
end case;

我们可以使用简单case语句来检查表达式的值与一组唯一值的匹配，上述sql中，case_expression可以是任何有效的表达式。我们将case_expression的值与每个when子句中的when_expression进行比较，例如when_expression_1，when_expression_2等。如果case_expression和when_expression_n的值相等，则执行相应的when分支中的命令(commands)。如果when子句中的when_expression与case_expression的值匹配，则else子句中的命令将被执行。其中，else子句是可选的，如果我们省略else子句，并且找不到匹配项，mysql将引发错误。我们来看个使用简单的case语句的例子：

delimiter $$
create procedure getcustomershipping(
 in p_customernumber int(11), 
 out p_shiping    varchar(50))
begin
  declare customercountry varchar(50);
  select country into customercountry
 from customers
 where customernumber = p_customernumber;
  case customercountry
 when 'usa' then
  set p_shiping = '2-day shipping';
 when 'canada' then
  set p_shiping = '3-day shipping';
 else
  set p_shiping = '5-day shipping';
 end case;
end$$

上述sql的工作方式如下：

• getcustomershipping存储过程接受客户编号作为in参数，并根据客户所在国家返回运送时间。
• 在存储过程中，首先，我们根据输入的客户编号得到客户的国家。然后使用简单case语句来比较客户的国家来确定运送期。如果客户位于美国(usa)，则运送期为2天。 如果客户在加拿大，运送期为3天。 来自其他国家的客户则需要5天的运输时间。

来看下确定运输时间的逻辑的流程图：

以下是上述存储过程的测试脚本：

set @customerno = 112;
select country into @country
from customers
where customernumber = @customerno;
call getcustomershipping(@customerno,@shipping);
select @customerno as customer,
    @country  as country,
    @shipping  as shipping;

执行上面代码，得到以下结果：

+----------+---------+----------------+
| customer | country | shipping       |
+----------+---------+----------------+
|      112 | usa     | 2-day shipping |
+----------+---------+----------------+
1 row in set

简单case语句仅允许我们将表达式的值与一组不同的值进行匹配。 为了执行更复杂的匹配，如范围，我们可以使用可搜索case语句。 可搜索case语句等同于if语句，但是它的构造更加可读，来看下它的语法结构：

case
  when condition_1 then commands
  when condition_2 then commands
  ...
  else commands
end case;

上述sql首先会评估求值when子句中的每个条件，直到找到一个值为true的条件，然后执行then子句中的相应命令(commands)。如果没有一个条件为true，则执行else子句中的命令(commands)。如果不指定else子句，并且没有一个条件为true，mysql将发出错误消息。mysql不允许在then或else子句中使用空的命令。 如果我们不想处理else子句中的逻辑，同时又要防止mysql引发错误，则可以在else子句中放置一个空的begin end块。咱们来看下使用可搜索case语句来根据客户的信用额度来查找客户级：silver，gold或platinum的案例：

delimiter $$
create procedure getcustomerlevel(
 in p_customernumber int(11), 
 out p_customerlevel varchar(10))
begin
  declare creditlim double;
  select creditlimit into creditlim
 from customers
 where customernumber = p_customernumber;
  case 
 when creditlim > 50000 then 
  set p_customerlevel = 'platinum';
 when (creditlim <= 50000 and creditlim >= 10000) then
  set p_customerlevel = 'gold';
 when creditlim < 10000 then
  set p_customerlevel = 'silver';
 end case;
end$$

在上面查询语句逻辑中，如果信用额度是：

• 大于50k，则客户是platinum客户。
• 小于50k，大于10k，则客户是gold客户。
• 小于10k，那么客户就是silver客户。

我们可以通过执行以下测试脚本来测试存储过程：

call getcustomerlevel(112,@level);
select @level as 'customer level';

执行上面查询语句，得到以下结果：

+----------------+
| customer level |
+----------------+
| platinum       |
+----------------+
1 row in set

好啦，本次分享就到这里了。