MySQL储存过程编程教程－第四章

kider

第四章 程序块，条件表达式和迭代编程
这一章描述了在MySQL语言程序创建过程中作用域的控制和流程控制

在MySQL中，和所有的块结构语言一样，成组的语句可以编辑为块。块可以出现在任何正规的单个语句出现的地方，并且块可以包含他自己的变量，游标和处理结构声明。

MySQL存储程序编程语言支持两种不同的流程控制语句：条件控制语句和迭代（循环）语句。你所写的几乎任何一个代码片段都需要条件控制，这是一种基于条件的指导你程序执行流程的能力。一般我们使用IF-THEN-ELSE和CASE语句来完成这一切。

迭代控制结构或者说是循环体，允许你将同一段代码重复执行。MySQL提供了三种不同的循环控制：

简单循环

一直执行直到你使用LEAVE语句明确的声明终止循环为止

REPEAT UNTIL循环

当表达式返回真是继续执行

WHILE循环

一直循环直到表达式为真



4.1存储程序的块结构
大多数MySQL存储程序由一个或多个程序块构成（唯一的例外就是只有单个语句构成的存储程序）。每个程序块都有BEGIN语句开始，由END语句结束，所以最简单的存储程序就是由程序定义声明（CREATE PROCEDURE, CREATE FUNCTION或CREATE TRIGGER）后面跟随一个包含要执行代码的块构成的

    CREATE {PROCEDURE|FUNCTION|TRIGGER} program_name

BEGIN

program_statements

END;

使用程序块有两个原因：

将逻辑相关的代码部分放在一起

举例来说，一个错误处理声明（见第六章对于错误处理的解释）可以包含一个能够执行多行命令的块。在块中的所有所有语句都将在错误处理被调用是被执行

控制变量和其他对戏那个的作用域

你可以在一个块中定义一个变量，这样在块的外部就无法看到这个变量。其次，你可以在一个块的内部定义一个覆盖块外部同名变量的变量

提示：一个混合语句由一个BEGIN-END块构成，这个块其的作用就是闭合一个或多个存储程序指令

4.1.1 块的结构
一个块由多种不同的声明（比如说：变量，游标，错误处理单元）和程序代码（比如：赋值，条件语句，循环）。它们之间的顺序如下：

1.变量和条件声明。变量早在第三章就讨论过，而条件声明将在第六章讨论。

2.游标声明，将在第五章讨论

3.处理单元声明，将在第六章讨论

4.程序代码

如果你违反这个顺序，比如说当一个DECLARE语句位于SET语句之后，MySQL将会在创建你的程序代码时产生一个错误信息。错误信息并不总会指出你没有按照顺序使用语句，所以注意培养自己声明事物的正确顺序很重要。

提示：在块中语句的顺序必须是变量（Variables）和条件，接下来是游标（Cursors），然后是异常处理（Exception handlers），而最后是其他语句（Other）。我们可以使用下面的藏头诗来助记：“Very Carefully Establish Order”（千万小心创建顺序）。

你也可以给你的块命名一个标签（label）。标签必须同时出现在BEGIN语句之前和END语句之后。命名一个标签具有以下的优势：

• 这有助于改善代码的可读性，比如说者可以让你快速的找到与BEGIN语句匹配的END语句
• 这允许你使用LEAVE语句终止程序块的执行（见本章后面部分关于这种语句的描述）。

所以这种块结构的最简单形式如下：

   
[label:] BEGIN

variable and condition declarations]

cursor declarations

handler declarations


program code


END[label];

4.1.2. 嵌套块

如果每个存储程序都只包含一个块，那么块结构就会相当难以维护，虽然很多程序只包括一个最起码的块，并且吧所有的程序代码都闭合在这个主块中。正如早已提醒过的，在块中声明的变量在块的外部不可用，但是对于此块中定义的嵌套块却是可见的。你可以在块中覆盖定义“外部”变量，并且你可以在不影响外部变量的情况下操作这个内部变量

让我们举些例子来说明这些原理

在Example 4-1中，我们在一个块中创建了一个变量，这个变量在快的外部是不可用的，所以这个例子产生了一个错误

Example 4-1.在块中定义的变量在块外部不可见

1. 
   
2. mysql> CREATE PROCEDURE nested_blocks2(  )
   
3. BEGIN
   
4.         DECLARE my_variable varchar(20);
   
5.         SET my_variable='This value was set in the outer block';
   
6.         BEGIN
   
7.                 SET my_variable='This value was set in the inner block';
   
8.         END;
   
9.         SELECT my_variable, 'Changes in the inner block are visible in the outer block';
   
10. END;
    
11. $$
    
12. 
    
13. 
    
14. Query OK, 0 rows affected (0.00 sec)
    
15. 
    
16. 
    
17. 
    
18. 
    
19. mysql> CALL nested_blocks2(  )
    
20. //
    
21. 
    
22. 
    
23. +---------------------+-----------------------------------------------------------+
    
24. | my_variable        | Changes in the inner block are visible in the outer block |
    
25. +---------------------+-----------------------------------------------------------+
    
26. | This value was set  |                                                           |
    
27. |  in the inner block | Changes in the inner block are visible in the outer block |
    
28. +---------------------+-----------------------------------------------------------+
    
29. 1 row in set (0.00 sec)
    
30. 
    
31. 
    
32. Query OK, 0 rows affected (0.01 sec)
    
33. 
    

复制代码

在Example 4-2中，我们在“内部”块中修改了一个由“外部”块声明的变量。这个修改对于内部块之外的作用域是可见的

Example 4-2.内部块的变量可以覆盖外部块定义的变量

1. mysql> CREATE PROCEDURE nested_blocks3(  )BEGIN        DECLARE my_variable varchar(20);        SET my_variable='This value was set in the outer block';        BEGIN                DECLARE my_variable VARCHAR(20);                SET my_variable='This value was set in the inner block';        END;        SELECT my_variable, 'Can''t see changes made in the inner block';END;//  Query OK, 0 rows affected (0.00 sec)  mysql> CALL nested_blocks3(  )$$  +---------------------------+-------------------------------------------+| my_variable               | Can't see changes made in the inner block |+---------------------------+-------------------------------------------+| This value was set in the |                                           ||   outer block             | Can't see changes made in the inner block |+---------------------------+-------------------------------------------+1 row in set (0.00 sec)  Query OK, 0 rows affected (0.00 sec)
   

复制代码

提示：请避免在内部块中覆盖定义外部块中已存在的变量

在我们最后一个嵌套块的例子中（Example 4-4）我们使用了块标签并使用LEAVE语句来终止块的执行。我们将会在本章的随后部分讨论LEAVE语句的使用，但现在，我们已经可以向你指明当你是用块标签的场合下，你可以随时使用LEAVE语句来终止一个块的执行。

Example 4-4.使用LEAVE语句来离开（终止）一个标签快

1. 
   
2. mysql> CREATE PROCEDURE nested_blocks5(  )
   
3. outer_block: BEGIN
   
4.         DECLARE l_status int;
   
5.         SET l_status=1;
   
6.         inner_block: BEGIN
   
7.                 IF (l_status=1) THEN
   
8.                         LEAVE inner_block;
   
9.                 END IF;
   
10.                 SELECT 'This statement will never be executed';
    
11.         END inner_block;
    
12.         SELECT 'End of program';
    
13. END outer_block$$
    
14. 
    
15. 
    
16. Query OK, 0 rows affected (0.00 sec)
    
17. 
    
18. 
    
19. mysql> CALL nested_blocks5(  )$$
    
20. 
    
21. 
    
22. +----------------+
    
23. | End of program |
    
24. +----------------+
    
25. | End of program |
    
26. +----------------+
    
27. 1 row in set (0.00 sec)
    
28. 
    
29. 
    
30. Query OK, 0 rows affected (0.00 sec)
    
31. 
    

复制代码

kider

4.2 条件控制条件控制（流程控制）语句允许你基于变量或表达式来执行代码。正如我们早就说过的，一个表达式可以由任何MySQL字面常量，变量，操作符和函数等能够返回值的元素构成。条件控制表达式允许你根据这些值或表达式返回值的不同而做出不同的行为，而这些值可以引用存储程序的参数，或许是数据库中的数据，或许是变量（如一周中的某一天或者一天中的某个时间）。

MySQL存储程序语言支持两种不同的条件控制表达式：IF和CASE。它们都表现出十分相似的行为，并且通常我们总可以用IF语句来改写CASE语句的代码或者正好相反，通常来说IF和CASE之间的选择总是牵涉到个人的习惯和编程标准。但是，他们在不同的状况下总会表现出他们各自区别与对方的优势

在下面的小小章节中描述了他们各自的语法，提供了用例，最后对这两个语句的正反两方面进行了对比

4.2.1.IF语句
所有的语言总是倾向于提供IF语句的各种变种，MySQL对于IF语句的实现也不意外。存储程序中IF语句的语法是：


IF expression THEN commands

[ELSEIF expression THEN commands ....]

[ELSE commands]

END IF;

4.2.1.1.TRUE或FALSE（或者其他）？
在IF或者ELSEIF语句中的命令只有在与之配合的表达式测试为TRUE时才会执行。例如1=1或2>1的表达式会返回TRUE。表达式1>3将被返回FALSE。

虽然，假如你同时操作一个或多个变量，并且其中的一个变量值为NULL，那么这个表达式的结果就是NULL而不是TRUE或FALSE，这样的话如果你认为代码中的测试条件不是TRUE就是FALSE（或正好相反）,就会产生一些错误的结果，所以，举例来说，在Example 4-5中，如果我们在版本字符串中不能找到‘alpha’或‘beta’，我们就会以为这个发行版是正式的产品。虽然，如果l_version是NULL，那么ELSE条件将总是被执行，但事实上我们任何依据来证明这种断言。

Example 4-5.假设不是TRUE就是FALSE是错误的

1. 
   
2.    IF (INSTR(l_version_string,'alpha')>0) THEN
   
3.          SELECT 'Alpha release of MySQL';
   
4.     ELSEIF (INSTR(l_version_string,'beta')>0) THEN
   
5.          SELECT 'Beta release of MySQL';
   
6.     ELSE
   
7.          SELECT 'Production release of MySQL';
   
8.     END IF;

复制代码

提示：不要假设测试表达式的结果不是TRUE就是FALSE。它在表达式中的任何一个变量为NULL时也可以返回NULL（UNKNONN）。

同样要注意任何返回数字值或字符串的表达式看上去是数字，但测试结果可能是TRUE，FALSE或NULL。规则如下：

• 任何绝对值大于1的数字表达式，在IF或ELSEIF语句的测试结果为TRUE。注意“绝对值”的称呼意味这无论是1还是-1测试结果都为TRUE
• 如果数字值表达式为0，则测试结果为FALSE

4.2.1.2 简单的IF-THEN组合
在最简单的形式中，IF可以被指向一个语句集合，只要其中的一个条件测试为TRUE就执行，这种IF表达式的语法如下：

IF expression THEN

statements
END IF;

三值逻辑 布尔表达式可以返回三个可能的结果，当布尔表达式中的值都是已知时，结果可能是TRUE或FALSE。举例来说，如同下面的表达式无疑会返回真或假 (2 < 3) AND (5 < 10) 有时，你可能并不知晓所有表达式的值，这是因为数据库允许值为NULL或者被遗漏。那么随后会发生什么，从表达式返回的结果可以包含NULL吗？请看例子： 2 < NULL 因为你不知道遗漏的值是哪一个，你所能给的唯一答案就是“我不知道。”这就是最为基本的，所谓的“三值逻辑”，所以你所能得到的可能答案不仅是TRUE和FALSE，还有NULL。 关于三值逻辑的深层次知识，我们推荐C.J.Date的书《Database In Depth》（O'Reilly）为从业者准备的关系理论书籍

kider

Example 4-6 展示了简单的IF语句

Example 4-6. IF语句的简单例子

1. 
   
2. IF sale_value > 200 THEN
   
3. 
   
4.     CALL apply_free_shipping(sale_id);
   
5. END IF
   
6. ;
   

复制代码

我们可以THEN和END IF子句中包含多个语句，就像Example 4-7

Example 4-7. IF语句中的多个语句

1. 
   
2. 
   
3. IF sale_value > 200 THEN
   
4.     CALL apply_free_shipping(sale_id);
   
5.     CALL apply_discount(sale_id,10);
   
6. END IF;
   

复制代码

正如Example 4-8所展示的那样，我们同样可以在IF语句中包含任何可执行语句，比如循环结果，SET语句和别的IF语句（当然，正如你即将看到的一样，我们竟可能在这种形式中避免使用嵌套IF语句）。

Example 4-8.嵌套IF语句

1. 
   
2. 
   
3. IF sale_value > 200 THEN
   
4.     CALL apply_free_shipping(sale_id);
   
5.     CALL apply_discount(sale_id,10);
   
6. END IF;
   

复制代码

正如Example 4-9所示，没有必要为IF语句断行，在这个例子中的所有IF语句都被MySQL一视同仁。

Example 4-9.IF语句的候选格式

1. 
   
2. 
   
3. IF sale_value > 200 THEN CALL apply_free_shipping(sale_id); END IF;
   
4. 
   
5. 
   
6. IF sale_value > 200
   
7. THEN
   
8.     CALL apply_free_shipping(sale_id);
   
9. END IF;
   
10. 
    
11. 
    
12. IF sale_value > 200 THEN
    
13.     CALL apply_free_shipping(sale_id);
    
14. END IF;
    

复制代码

大体上把非常简单的IF语句放在同一行上是没有问题的，但是对于复杂的或者嵌套的IF结构而言这却不是一种很好的编程实践（风格）。举例来说，以下这种格式更易读，易理解，易维护：



IF sale_value > 200 THEN

CALL apply_free_shipping(sale_id);

IF sale_value > 500 THEN

CALL apply_discount(sale_id,20);

END IF;

END IF;

或者是：



IF sale_value > 200 THEN CALL apply_free_shipping(sale_id); IF sale_value >

500 THEN CALL apply_discount(sale_id,20);END IF;END IF;

某些程序员喜欢将THEN子句放在独立的一行上，如下所示：



IF sale_value > 200

THEN

CALL apply_free_shipping(sale_id);

END IF;

但这更像是个人的喜好或者编程标准

提示：对于一个非凡的IF语句而言，使用缩排和格式化来确保你的IF语句的逻辑更容易理解

4.2.1.3. IF-THEN-ELSE语句

为你的IF语句增加ELSE条件允许你在假设IF条件非真时执行你所需的代码，我们还是要再次强调一下非真并不总代表假。如果IF语句条件测试为假，那么ELSE语句任然会被执行；这当你无法保证IF条件中出现NULL值时可能会带来微妙的错误。

IF-THEN-ELSE块有如下语法：


IF expression THEN

statements that execute if the expression is TRUE

ELSE

statements that execute if the expression is FALSE or NULL

END IF;

所以我们在Example 4-10中，我们在销售额低于$200时对某个订单进行托运，否则我们就对其进行折扣处理（并且不处理托运）

IF sale_value <200 THEN

CALL apply_shipping(sale_id);
ELSE

CALL apply_discount(sale_id);
END IF;

4.2.1.4. IF-THEN-ELSEIF-ELSE语句

IF语句的完整语法允许你定义多个条件。第一个测试为TRUE的条件将被执行。如果没有任何表达式测试为TRUE，那么ELSE子句（如果存在）将被执行。IF-THEN-ELSEIF-ELSE语句的语法是这样的


IF expression THEN

statements that execute if the expression is TRUE

ELSEIF expression THEN

statements that execute if expression1 is TRUE

ELSE

statements that execute if all the preceding expressions are FALSE or NULL

END IF;

你可以在其中放置任意多的ELSEIF条件

条件之间并非互相排斥。这意味着，不止一个条件可能被测试为真。而第一个被测试为真的条件将会得到执行。创建叠加的条件看起来会很有用，但你排放条件时必须非常小心。举例来说，考虑一下Example 4-11的IF-ELSEIF语句。

Example 4-11.使用叠加条件的IF-ELSEIF块的例子

1. 
   
2. 
   
3. IF (sale_value>200) THEN
   
4.     CALL free_shipping(sale_id);
   
5. ELSEIF (sale_value >200 and customer_status='PREFERRED') THEN
   
6.     CALL free_shipping(sale_id);
   
7.     CALL apply_discount(sale_id,20);
   
8. END IF;
   

复制代码

这段代码的意图非常明确：对于订购超过$200的客户提供免费托运，并且给与优先客户以20%的折扣，虽然因为所有超过$200的订购将使第一个条件测试为真，但是对于ELSEIF中订购超过$200的测试将永远得不到执行，这意味着优先的客户将不能得到他们的折扣，客户没有得到折扣意味着我们的存储过程程序员就得不到年终的奖金！

有一大堆的方法可以让这个语句变得更严谨：其中的一种是，我们可以将ELSEIF条件分支移入IF子句中来确保它首先得到测试，或者，我们可以将一个包含sale_value>200条件的IF语句嵌套与IF子句中来测试客户的状态，就像Example 4-12所展示的那样

Example 4-12. 两种纠正前面例子中逻辑错误的方面

1. 
   
2. 
   
3. /* Reordering the IF conditions */
   
4. IF (sale_value >200 and customer_status='PREFERED') THEN
   
5.         CALL free_shipping(sale_id);
   
6.         CALL apply_discount(sale_id,20);
   
7. ELSEIF (sale_value>200) THEN
   
8.         CALL free_shipping(sale_id);
   
9. 
   
10. END IF;
    
11. 
    
12. 
    
13. /* Nesting the IF conditions */
    
14. 
    
15. 
    
16. IF (sale_value >200) THEN
    
17.     CALL free_shipping(sale_id);
    
18.     IF (customer_satus='PREFERRED') THEN
    
19.        CALL apply_discount(sale_id,20);
    
20.     END IF;
    
21. END IF:
    

复制代码

在Example 4-12中的两种替换方案几乎都是完美有效的。一般来说我们总是希望尽可能避免嵌套IF语句，但是当存在大量的sale_value大于200的条件分支需要我们去处理，那么对sale_value进行一次测试，然后对其他个别的条件进行测试就变得有意义。所以我们的业务规则就是给订购超过$200的客户以免费托运，并且让我们的客户忠实度程序基于客户的状态来给出不同的折扣率。这种在IF-ELSEIF块中的单个逻辑就像Example 4-13所演示的一样。

Example 4-13.IF块伴随着大量的冗长的条件

1. 
   
2. 
   
3. IF (sale_value >200 and customer_status='PLATINUM') THEN
   
4.     CALL free_shipping(sale_id);      /* Free shipping*/
   
5.     CALL apply_discount(sale_id,20);  /* 20% discount */
   
6. 
   
7. 
   
8. ELSEIF (sale_value >200 and customer_status='GOLD') THEN
   
9.     CALL free_shipping(sale_id);     /* Free shipping*/
   
10.     CALL apply_discount(sale_id,15); /* 15% discount */
    
11. 
    
12. ELSEIF (sale_value >200 and customer_status='SILVER') THEN
    
13.     CALL free_shipping(sale_id);     /* Free shipping*/
    
14.     CALL apply_discount(sale_id,10); /* 10% discount */
    
15. 
    
16. 
    
17. ELSEIF (sale_value >200 and customer_status='BRONZE') THEN
    
18.     CALL free_shipping(sale_id);    /* Free shipping*/
    
19.     CALL apply_discount(sale_id,5); /* 5% discount*/
    
20. 
    
21. ELSEIF (sale_value>200) THEN
    
22.     CALL free_shipping(sale_id);    /* Free shipping*/
    
23. 
    
24. END IF;
    

复制代码

在这个例子中不变的重复着的sale_value条件和free_shipping调用，实际上渐渐的破坏了代码的可读性，并且是效率降低（见第22章）。如果使用嵌套的IF结构来清晰的给每个超过$200的客户以免费托运，而后，根据仅客户忠诚度给与相应的折扣会显得更好。Example 4-14展示了嵌套IF的实现版本。

Example 4-14.使用嵌套IF来避免呈长的测试条件

1. 
   
2. 
   
3. IF (sale_value > 200) THEN
   
4.     CALL free_shipping(sale_id);    /*Free shipping*/
   
5. 
   
6. 
   
7.     IF (customer_status='PLATINUM') THEN
   
8.         CALL apply_discount(sale_id,20); /* 20% discount */
   
9. 
   
10. 
    
11.     ELSEIF (customer_status='GOLD') THEN
    
12.         CALL apply_discount(sale_id,15); /* 15% discount */
    
13. 
    
14. 
    
15.     ELSEIF (customer_status='SILVER') THEN
    
16.         CALL apply_discount(sale_id,10); /* 10% discount */
    
17. 
    
18. 
    
19.     ELSEIF (customer_status='BRONZE') THEN
    
20.         CALL apply_discount(sale_id,5); /* 5% discount*/
    
21.     END IF;
    
22. 
    
23. 
    
24. END IF;
    

复制代码

4.2.2. CASE语句

CASE语句是一种条件执行或者流程控制的可替换形式。任何CASE语句能做的事都可以用IF语句完成（或者正好相反），但是CASE语句通常更可读并且在处理多个测试条件时更有效，特别是当所有的条件都输出比对同一个表达式时

4.2.2.1. 简单CASE语句

CASE语句有两种形式。第一种形式通常被称为简单CASE语句，用来作为多个表达式的输出相比对


CASE expression

WHEN value THEN

statements

[WHEN value THEN

statements ...]

[ELSE


statements]

END CASE;

这种语法在检测若干清晰的表达式输出集合时会很有用。举例来说，我们可以使用简单CASE语句来检查前一个样例中的客户忠诚度状态，就像Example 4-15所示

Example 4-15.简单CASE表达式的例子

1. 
   
2. 
   
3. CASE customer_status
   
4.     WHEN 'PLATINUM'  THEN
   
5.         CALL apply_discount(sale_id,20); /* 20% discount */
   
6. 
   
7.     WHEN 'GOLD' THEN
   
8.         CALL apply_discount(sale_id,15); /* 15% discount */
   
9. 
   
10.     WHEN 'SILVER' THEN
    
11.         CALL apply_discount(sale_id,10); /* 10% discount */
    
12. 
    
13. 
    
14.     WHEN 'BRONZE' THEN
    
15.         CALL apply_discount(sale_id,5); /* 5% discount*/
    
16. END CASE;
    

复制代码

就像IF命令一样，你可以指定多个WHEN语句并且你可以指定一个ELSE子句来执行其他条件未得到满足时的情况

当然，你必须清醒的认识到CASE语句将会在没有任何条件匹配的情况下产生异常，这意味着在Example 4-15中如果customer_status不是'PLATINUM', 'GOLD', 'SILVER'或 'BRONZE' 其中的任何一个，那么接下来就会发生运行时异常：

ERROR 1339 (20000): Case not found for CASE statement

我们可以为此创建一个异常处理单元来忽略这种错误（就像第六章中所描述的一样），但是也许使用一个ELSE子句来确保所有可能的条件都得到处理是一种更好的编程实践。因此，我们或许应该适应前面的例子，给其增加一个ELSE子句来给那些没有遇到过前面这种情况的客户以0折扣率

提示：如果没有任何一个CASE语句与输入的条件相匹配，CASE将会生成一个MySQL 1339错误。你可以为其创建一个错误处理单元来忽略错误，或者在CASE语句中使用ELSE子句确保异常永远不再发生

在简单CASE语句中比对一系列表达式的值显得很有用。虽然，简单的CASE语句并不能轻易的，完全的匹配范围，或者处理更为复杂的包含 多表达式的条件，因此，对于那些更为复杂的“情况”我们可以使用“查询”CASE语句，将在下一节中被描述。

4.2.2.2. "查询" CASE语句

查询CASE语句和IF-ELSEIF-ELSE-END块所具备的功能基本等同，查询CASE语句具有如下语法：


CASE

WHEN condition THEN


statements

[WHEN condition THEN

statements...]

[ELSE

statements]

END CASE;

使用查询CASE结构，我们可以实现早些段落中是用IF来实现的免费托运和折扣率的逻辑。在Example 4-16中我们展示了是用查询CASE语句来是先的销售折扣率和免费托运的逻辑。

Example 4-16. 查询CASE表达式的例子

1. 
   
2. 
   
3. CASE
   
4.     WHEN  (sale_value >200 AND customer_status='PLATINUM') THEN
   
5.         CALL free_shipping(sale_id);     /* Free shipping*/
   
6.         CALL apply_discount(sale_id,20); /* 20% discount */
   
7. 
   
8. 
   
9.     WHEN  (sale_value >200 AND customer_status='GOLD') THEN
   
10.         CALL free_shipping(sale_id);     /* Free shipping*/
    
11.         CALL apply_discount(sale_id,15); /* 15% discount */
    
12. 
    
13. 
    
14.     WHEN (sale_value >200 AND customer_status='SILVER') THEN
    
15.         CALL free_shipping(sale_id);     /* Free shipping*/
    
16.         CALL apply_discount(sale_id,10); /* 10% discount */
    
17. 
    
18. 
    
19.     WHEN (sale_value >200 AND customer_status='BRONZE') THEN
    
20.         CALL free_shipping(sale_id);    /* Free shipping*/
    
21.         CALL apply_discount(sale_id,5); /* 5% discount*/
    
22. 
    
23.     WHEN (sale_value>200)     THEN
    
24.         CALL free_shipping(sale_id);    /* Free shipping*/
    
25. 
    
26. 
    
27. 
    
28. END CASE;
    

复制代码

虽然请始终记住如果没有任何WHERE子句被匹配时，将会产生一个1339错误，这些代码将在订单少于$200或者客户并不在我们的忠诚度程序时会导致致命的错误，因此我们必须插入ELSE子句来保护我们的代码和工作的安全，正如Example 4-17所做的那样。

Example 4-17为我们的查询CASE例子增加一个“哑”ELSE子句

1. 
   
2. 
   
3. CASE
   
4.     WHEN  (sale_value >200 AND customer_status='PLATINUM') THEN
   
5.         CALL free_shipping(sale_id);     /* Free shipping*/
   
6.         CALL apply_discount(sale_id,20); /* 20% discount */
   
7. 
   
8. 
   
9.     WHEN  (sale_value >200 AND customer_status='GOLD') THEN
   
10.         CALL free_shipping(sale_id);     /* Free shipping*/
    
11.         CALL apply_discount(sale_id,15); /* 15% discount */
    
12. 
    
13. 
    
14.     WHEN (sale_value >200 AND customer_status='SILVER') THEN
    
15.         CALL free_shipping(sale_id);     /* Free shipping*/
    
16.         CALL apply_discount(sale_id,10); /* 10% discount */
    
17. 
    
18. 
    
19.     WHEN (sale_value >200 AND customer_status='BRONZE') THEN
    
20.         CALL free_shipping(sale_id);    /* Free shipping*/
    
21.         CALL apply_discount(sale_id,5); /* 5% discount*/
    
22. 
    
23.     WHEN (sale_value>200)     THEN
    
24.         CALL free_shipping(sale_id);    /* Free shipping*/
    
25. 
    
26. 
    
27. 
    
28. END CASE;
    

复制代码

注意因为MySQL在存储程序语言中缺乏NULL语句（不干任何事的），所以我们必须加入一个哑语句，但是这个语句开销几乎为0

就像我们用IF所实现的逻辑一样，我们可以使用嵌套CASE语句来是先出一个逻辑更为清晰的版本。在Example 4-18中我们结合了简单CASE和查询CASE语句，并且包含了一个“not found”的错误处理单元来避免使用ELSE语句。我们使用块来闭合其中的内容，因此我们的处理单元并不会感应到此存储程序中的其他语句

Example 4-18.嵌套CASE语句和一个块作用域的“not found”处理单元

1. 
   
2. 
   
3. BEGIN
   
4.     DECLARE not_found INT DEFAULT 0;
   
5.     DECLARE CONTINUE HANDLER FOR 1339 SET not_found=1;
   
6. 
   
7. 
   
8.     CASE
   
9.         WHEN (sale_value>200) THEN
   
10.             CALL free_shipping(sale_id);
    
11.             CASE customer_status
    
12.                 WHEN 'PLATINUM' THEN
    
13.                     CALL apply_discount(sale_id,20);
    
14.                 WHEN 'GOLD' THEN
    
15.                     CALL apply_discount(sale_id,15);
    
16.                 WHEN 'SILVER' THEN
    
17.                     CALL apply_discount(sale_id,10);
    
18.                 WHEN 'BRONZE' THEN
    
19.                     CALL apply_discount(sale_id,5);
    
20.             END CASE;
    
21.     END CASE;
    
22. 
    
23. 
    
24. END;
    

复制代码

4.2.3 IF和CASE的比较

我们已经看到无论是IF还是CASE语句都能实现相同的流程控制功能。那么哪一个是最好的？在很大程度上，对两者的选择更关乎与个人风格和编程标准，而并非取决于两者所暗示的优势。尽管如此，我们在对CASE和IF进行选择时，可以遵照如下的原则：

一致的风格往往比两者在其各自的特殊场合所显现的微弱优势更重要。我们更希望你能够一致的选择你的CASE或者IF，而不是根据你的心情，天气或者掷色子来随机的决定使用哪一个
当你使用一个表达式来和一列清晰的值相比对时CASE会略显清晰（使用“简单”CASE语句）。
当你基于多个变量对一列复杂表达式进行测试时，IF结构可能会显得更为有好并容易理解
如果你选择CASE，你必须保证至少一个CASE条件得到匹配，或者定义一个错误处理单元来捕获错误，如果没有任何CASE条件得到满足，那么使用IF就没有这种烦恼。

无论你选择哪一个结构，请记住一下要点

如果CASE或IF结构中的条件得到满足，则别的条件将不再得到测试，这意味这你的条件排放顺序需要相当的严格
MySQL存储程序语言使用三值逻辑；因此若是一个语句是非真并不意味着他必定为FALSE，也可能是NULL。
你必须仔细的思考你代码的可读性，优势使用IF或CASE嵌套集可以使代码更可读，更有效。虽然在通常状况下应该避免嵌套的发生，特别是那些嵌套层次很深的代码（这里指的是三层或三层以上）。

lxydyx

cuiyunzhuangzhu