mysql case实例：带条件检测的case语句

mysql case语句的用法举例，如下：

mysql> DELIMITER //
mysql> CREATE FUNCTION myFunction(delivery_day INT(1),preferred INT(1)) //创建mysql函数
    -> RETURNS INT(2)
    -> BEGIN
    ->
    -> DECLARE shipping_cost INT(2) DEFAULT 0;
    ->
    -> CASE  //带条件检测的case语句
    -> WHEN preferred = 1 THEN
    ->         SET shipping_cost = 2;
    -> WHEN delivery_day = 1 THEN
    ->         SET shipping_cost = 20;
    -> WHEN delivery_day = 2 THEN
    ->         SET shipping_cost = 15;
    -> WHEN delivery_day = 3 THEN
    ->         SET shipping_cost = 10;
    -> ELSE
    ->         SET shipping_cost = 5;
    -> END CASE;
    -> RETURN shipping_cost;
    ->
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> select myFunction(1,1);
+-----------------+
| myFunction(1,1) |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> select myFunction(2,2);
+-----------------+
| myFunction(2,2) |
+-----------------+
|              15 |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> select myFunction(3,3);
+-----------------+
| myFunction(3,3) |
+-----------------+
|              10 |
+-----------------+
1 row in set (0.00 sec)

mysql> drop function myFunction; //删除mysql自定义函数
Query OK, 0 rows affected (0.00 sec)