mysql if流程控制语句 mysql流程控制语句包括

转载

kekenai 2023-12-21 15:00:56

文章标签 mysql if流程控制语句 mysql流程控制 mysql MySQL 存储过程 文章分类 MySQL 数据库

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 PROCEDUREproc1(aINT,OUTiINT)BEGINIFa >0THENSET@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;