MySQL/MariaDB 流程控制语句

本文目录： 1.BEGIN...END 2.true和false 3.if结构 4.case结构 5.loop、leave和iterate 6.repeat循环 7.while循环

MySQL/MariaDB中的符合语句结构有：BEGIN...END，if，case，while，loop，repeat，cursor，condition，handler。

在MySQL中，它们都只能在存储程序(procedure,function,trigger)内部使用，但在MariaDB中，从10.1.1版本之后，BEGIN...END,IF,CASE,LOOP,REPEAT,WHILE可以在存储程序的外部使用。但实际上，除了begin、case和if能正常单独定义在存储程序之外，loop、repeat、while都是鸡肋，因为无法给单独定义的结构打标签，只能无限循环而无法退出。

1.BEGIN...END

[label:] BEGIN [NOT ATOMIC]     [statement_list] END [label]

begin...end默认只能在存储程序内部使用，此时可以使用label为begin...end打上标签。可以在end处使用标签来结束begin...end。如果end后指定了label,则end处的label必须和begin前的label完全相同。

可以使用NOT ATOMIC关键字让BEGIN在存储程序外部使用，但此时不能为begin...end打标签，且一定注意先修改delimiter，免得begin...end中的语句中途终止。

例如，下面三个begin...end语句块：

delimiter $$ begin not atomic        /* 在存储程序外部单独使用begin...end */     declare a int;     set a=3;     select a; end$$

create or replace procedure p1() my_label: begin          /* 为begin打上标签 */     declare b int;     set b=3;     select b; end$$                  /* 可以不使用标签结束begin */

create or replace procedure p2() my_label: begin     declare c int;     set c=3;     select c; end my_label$$        /* 使用的结束标签必须和开始标签一致 */ delimiter ; call p1; call p2;

在begin...end中，除了可以写一些SQL语句，还可以使用declare定义变量、条件、处理器和游标。但它们之间有顺序要求，顺序如下：

• DECLARE local variables;
• DECLARE CONDITIONs;
• DECLARE CURSORs;
• DECLARE HANDLERs;

还可以begin嵌套，内层的begin可以使用外层begin定义的变量。

delimiter $$ CREATE PROCEDURE t() BEGIN   DECLARE x TINYINT UNSIGNED DEFAULT 1;   BEGIN       DECLARE x CHAR(2) DEFAULT '02';       DECLARE y TINYINT UNSIGNED DEFAULT 10;       SELECT x, y;   END;   SELECT x; END; delimiter ;

2.true和false

在MySQL和MariaDB中，true和false是无条件的真、假。

true和1等价，false和0等价，且它们不区分大小写。任何时候，都可以互换它们，但任何时候，它们的结果都换转换为数值的1或0。即使将数据类型定义为int，也可以为其传递true字符串。

例如：

mysql> select true,false; +------+-------+ | TRUE | FALSE | +------+-------+ |    1 |    0 | +------+-------+ begin not atomic     declare a int;     set a=true;     select a; end$$ +------+ | a    | +------+ |    1 | +------+

3.if语句

IF search_condition THEN statement_list     [ELSEIF search_condition THEN statement_list] ...     [ELSE statement_list] END IF

其中search_condition是条件判断语句，statement_list是要执行的语句。

在MySQL中，if语句必须作为语句块写在存储过程或函数等程序结构中。在MariaDB 10.1.1之后，可以独立使用，但注意修改delimiter。

-- 独立使用if结构 delimiter $$ if 1>2 then     select 'true'; else     select 'false'; end if$$ delimiter ;

-- 在存储过程中使用 DELIMITER $$ CREATE or replace PROCEDURE proc1(a INT,OUT i INT) BEGIN     IF a > 0 THEN         SET @a1 = a + 100;     ELSEIF a = 0 THEN         SET @a1 = a + 200;     ELSE         SET @a1 = a + 300;     END IF ;     SET i:=@a1; END$$ DELIMITER ;

-- 测试if结构 CALL proc1(1,@a); CALL proc1(0,@b); CALL proc1(-1,@c);

查看结果。

SELECT @a,@b,@c;     @a      @b      @c  ------  ------  --------   101    200      299

注意，还有一个if(expr,true_value,false_value)函数，如果expr为真，则返回true_value，否则返回false_value，所以这里的if函数和if流程控制语句是不同的。例如：

MariaDB [test]> select if(1>2,'true','false') as a; +-------+ | a    | +-------+ | false | +-------+ MariaDB [test]> select if(1<2,'true','false') as a; +------+ | a    | +------+ | true | +------+

4.case语句

在MySQL中，有case表达式和case语句两种结构。

mysql> help case topics:   CASE OPERATOR   CASE STATEMENT

它们的区别有：

• case表达式使用end关键字作为结束符，而case语句使用end case作为结束符；
• case表达式有可选项else，省略它的时候其默认值为null，case语句也有可选项else，但是省略它的时候没有默认值，且else语句中不能是null；
• case表达式可以在任何地方使用(例如在select中)，而case语句只能在存储过程或函数这样类型的编程结构中使用。

mysql> help case operator CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

Examples: mysql> SELECT CASE 1 WHEN 1 THEN 'one'     ->    WHEN 2 THEN 'two' ELSE 'more' END;         -> 'one' mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;         -> 'true' mysql> SELECT CASE BINARY 'B'     ->    WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;         -> NULL mysql> help case statement CASE case_value     WHEN when_value THEN statement_list     [WHEN when_value THEN statement_list] ...     [ELSE statement_list] END CASE

Or:

CASE     WHEN search_condition THEN statement_list     [WHEN search_condition THEN statement_list] ...     [ELSE statement_list] END CASE

虽然case表达式和case语句在性质上不一样，但是使用它们的思路是一样的。CASE value WHEN...只能与value进行等同性检查，case when ... then...则要灵活的多。

case operator的使用示例：

MariaDB [test]> select * from Student; +------+----------+------+--------+ | sid  | name    | age  | class  | +------+----------+------+--------+ |    1 | chenyi  |  22 | Java  | |    2 | huanger  |  23 | Python | |    3 | zhangsan |  21 | Java  | |    4 | lisi    |  20 | C#    | |    5 | wangwu  |  21 | Python | |    6 | zhaoliu  |  19 | Java  | |    7 | qianqi  |  22 | C      | |    8 | sunba    |  20 | C++    | |    9 | yangjiu  |  24 | Java  | +------+----------+------+--------+

MariaDB [test]> select name,case when age>21 then 'older' else 'younger' end as oy from Student;    +----------+---------+ | name    | oy      | +----------+---------+ | chenyi  | older  | | huanger  | older  | | zhangsan | younger | | lisi    | younger | | wangwu  | younger | | zhaoliu  | younger | | qianqi  | older  | | sunba    | younger | | yangjiu  | older  | +----------+---------+

case statement的使用示例：

DELIMITER $$ CREATE OR REPLACE PROCEDURE proc1(a INT,OUT i INT) BEGIN     CASE         WHEN a>0 THEN             SET @a1=a+100;         WHEN a=0 THEN             SET @a1=a+200;         ELSE             SET @a1=a+300;     END CASE;     SET i=@a1; END$$ DELIMITER ; -- 调用存储过程进行测试 CALL proc1(1,@x); CALL proc1(0,@y); CALL proc1(-1,@z); -- 查看测试结果 SELECT @x,@y,@z;     @x      @y      @z  ------  ------  --------   101    200      299

5.loop、leave和iterate

loop是循环，leave是跳出整个循环（类似于break），iterate是跳出当前循环进入下一个循环(类似于continue)。

 [begin_label:] LOOP     statement_list END LOOP [end_label]

在loop结构中，使用end loop关键字作为结束标记。

在loop中可以使用标签来标注该循环。如果要标记loop循环，则使用"begin_label:"和"end_label"，但要求begin_label和end_label是完全相同的字符串标记(或者end不用标签直接结束也可以)。

add_num: loop     set @a=@a+1 end loop add_num;

该语句由于没有跳出循环语句，所以将会死循环。

因此，loop一般会配合leave来使用。leave的作用是退出指定标记的语句结构，一般用在循环中用来退出循环(不仅是用在loop循环，其他循环结构也一样)，相当于break。因为begin...end中也能使用标记，所以leave也能用在begin...end中。 例如下面的例子，计算从1加到给定的数。通过if判断退出循环的条件，达到退出条件时使用leave来退出。

delimiter $$ create or replace procedure proc1(n int) begin     declare sum,i int default 0;     loop_label: loop         set sum=sum+i;         if i=n then             leave loop_label;         end if;         set i=i+1;     end loop loop_label;     select sum; end$$ delimiter ;

call proc1(100);  /* 从1加到100 */   sum  --------     5050

iterate和leave的用法一样，只不过iterate是退出当前循环直接进入下一循环。

例如下面的存储过程，它的功能是计算传入数值范围内的奇数和。当超出传入值的范围时直接退出循环，当遇到偶数时直接进入下一循环。

delimiter $$ create or replace procedure proc2(n int) begin     declare i,sum int default 0;     my_loop: loop         set i=i+1;         if i>n then             leave my_loop;         elseif mod(i,2)=0 then             iterate my_loop;         else             set sum=i+sum;         end if;     end loop;     select sum; end$$ delimiter ;

调用存储过程来测试。

CALL proc4(9);   sum  --------       25

虽然在MariaDB中，loop能单独定义在存储程序之外，但是由于定义在外面的时候无法打标签，而退出循环的leave却必须接标签才能退出loop，因此loop定义在存储程序之外完全是一个鸡肋的功能。同理，repeat和while也是一样的。

6.repeat循环

loop循环结构中没有退出循环的条件，只能人为书写条件判断语句来判断何时退出循环。而repeat循环结构提供了直接定义退出循环条件的功能。

<span class="hljs-attr_selector">[begin_label:]</span> <span class="hljs-value">REPEAT</span>
    statement_list
UNTIL search_condition
END <span class="hljs-value">REPEAT</span> <span class="hljs-attr_selector">[end_label]</span>

其中until部分定义的是退出循环的条件。注意，until语句后面不需要结束提示符，给出了结束符反而报错。

例如下面的存储过程中，当a变量等于传入参数i的时候退出循环。

delimiter $$ create or replace procedure proc5(i int) begin     declare a int default 1;     declare sum int default 1;     addnum: repeat         set a=a+1;         if mod(a,2)=1 then             set sum=sum+a;         end if;     until a=i     end repeat addnum;     select sum; end$$ delimiter ; call proc5(10);   sum  --------       25

7.while循环

while循环和repeat循环使用方法一样，但是思路不一样。repeat是满足条件时退出，而while是满足条件时才执行循环中定义的语句。

[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]

例如下面的存储过程，仍然是计算传入值范围内的奇数和。但使用的是while循环。

DROP PROCEDURE IF EXISTS proc6; DELIMITER $$ CREATE PROCEDURE proc6(i INT) BEGIN     SET @a=1,@sum=1;     addnum: WHILE @a<i DO         SET @a=@a+1;         IF MOD(@a,2)=1 THEN             SET @sum=@a+@sum;         END IF;     END WHILE addnum;     SELECT @sum AS SUM; END$$ DELIMITER ;