MYSQL基础 - 流程控制 - 《MySql笔记》

顺序结构
分支结构
- if
- case
循环结构

顺序结构

顺序结构：程序从上往下依次执行。

分支结构

分支结构：程序从两条或多条路径中选择一个条去执行。

if

if(表达式1, 表达式2, 表达式3)

mysql> select if(1=1, 'true', 'false');
+--------------------------+
| if(1=1, 'true', 'false') |
+--------------------------+
| true                     |
+--------------------------+
1 row in set (0.03 sec)

mysql> delimiter $
mysql> create function func_test_if(var_score int) returns char
    -> begin
    -> if var_score >= 90 then return 'A';
    -> elseif var_score < 90 and var_score >= 80 then return 'B';
    -> end if;
    -> end $
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> select func_test_if(93);
+------------------+
| func_test_if(93) |
+------------------+
| A                |
+------------------+
1 row in set (0.07 sec)

case

mysql> select case when 1 = 1 then 'true' else 'false' end;
+----------------------------------------------+
| case when 1 = 1 then 'true' else 'false' end |
+----------------------------------------------+
| true                                         |
+----------------------------------------------+
1 row in set (0.05 sec)

mysql> select case 1 when 1 = 1 then 'true' else 'false' end;
+------------------------------------------------+
| case 1 when 1 = 1 then 'true' else 'false' end |
+------------------------------------------------+
| true                                           |
+------------------------------------------------+
1 row in set (0.04 sec)

循环结构

循环结构：程序在满足一定条件的基础上，重复执行一段代码。
循环控制：

iterate类似于 continue，跳出本次循环继续执行。
leave类似于 break，结束当前循环。
while**
we: while loop_condition
do
loop_list
end while we;
无循环控制： ``` mysql> delimiter $ mysql> create procedure pro_test_while(in var_int int) -> begin -> declare i int default 1; -> while i <= var_int do -> insert into t_user(username) values(‘zhangsan’); -> set i := i + 1; -> end while; -> end $ Query OK, 0 rows affected (0.02 sec) mysql> delimiter ; mysql> call pro_test_while(3); Query OK, 1 row affected (0.03 sec)

mysql> select * from t_user; +——+—————+ | id | username | +——+—————+ | 1 | zhangsan | | 2 | zhangsan | | 3 | zhangsan | +——+—————+ 3 rows in set (0.05 sec)

**存在循环控制：**

mysql> delimiter $ mysql> create procedure pro_test_while_leave(in var_int int) -> begin -> declare i int default 1; -> we: while i <= var_int do -> insert into t_user(username) values(‘zhangsan’); -> if i >= 5 then leave we; -> end if; -> set i := i+1; -> end while we; -> end $ Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ; mysql> call pro_test_while_leave(10); Query OK, 1 row affected (0.05 sec)

mysql> select * from t_user; +——+—————+ | id | username | +——+—————+ | 1 | zhangsan | | 2 | zhangsan | | 3 | zhangsan | | 4 | zhangsan | | 5 | zhangsan | +——+—————+ 5 rows in set (0.05 sec)

```
mysql> delimiter $
mysql> create procedure pro_test_while_iterate(in var_int int)
    -> begin
    -> declare i int default 0;
    -> we: while i <= var_int do
    -> set i := i + 1;
    -> if mod(i, 2) != 0 then iterate we;
    -> end if;
    -> insert into t_user(username) values('lisi');
    -> end while we;
    -> end $
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call pro_test_while_iterate(10);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
|  3 | zhangsan |
|  4 | zhangsan |
|  5 | zhangsan |
|  6 | lisi     |
|  7 | lisi     |
|  8 | lisi     |
|  9 | lisi     |
| 10 | lisi     |
+----+----------+
10 rows in set (0.07 sec)

loop

lp: loop
loop_list
end loop lp;
loop…leave…end loop;

mysql> delimiter $
mysql> create procedure pro_test_loop_leave(in var_int int)
    -> begin
    -> declare i int default 1;
    -> truncate table t_user;
    -> lp: loop
    -> insert into t_user(username) values('zhangsan');
    -> set i := i + 1;
    -> if i > var_int then
    -> leave lp;
    -> end if;
    -> end loop;
    -> end $
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call pro_test_loop_leave(5);
Query OK, 1 row affected (0.09 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
|  3 | zhangsan |
|  4 | zhangsan |
|  5 | zhangsan |
+----+----------+
5 rows in set (0.06 sec)

loop…iterate…end loop;

create procedure pro_test_loop_iterate ( in var_int int ) begin
    declare
        i int default 0;
    truncate table t_user;
    lp :
    loop
        set i := i + 1;

        if i > var_int then
        leave lp;
        end if;

        if mod(i, 2) != 0 then
            insert into t_user(username) values('zhangsan');

        elseif mod(i, 2) = 0 then
            iterate lp;
        end if;

    end loop;
end;

call pro_test_loop_iterate(5);

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
|  3 | zhangsan |
+----+----------+
3 rows in set (0.05 sec)

repeat

rep: repeat
loop_list
until end_condition
end repeat rep;

create procedure pro_test_repeat()
begin
declare i int default 1;
truncate table t_user;
repeat
insert into t_user(username) values('zhangsan');
set i := i + 1;
until i >= 3 end repeat;
end;

call pro_test_repeat();

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
+----+----------+
2 rows in set (0.03 sec)

流程控制