MySQL/MariaDB 流程控制语句

共计 6930 个字符，预计需要花费 18 分钟才能阅读完成。

本文目录：
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 循环结构提供了直接定义退出循环条件的功能。

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

其中 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 ;