本文目录:
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 ;
来源: http://www.linuxidc.com/Linux/2018-04/151733.htm