存储过程之流程控制语句

大家好，又见面了，我是全栈君。

阅读目录：通过条件、循环语句，对处理程序进行流程控制

条件控制

　　　　IF条件：条件为真，执行

　　　　CASE条件：匹配到，执行

循环控制

　　　　WHILE循环：先判断后执行

　　　　REPEAT循环：先执行后判断

　　　　LOOP循环(死循环)

　　　　LEAVE语句(离开)

　　　　ITERATE语句：迭代，再次循环

　　　　RETURN语句：返回

注意：MySQL不支持FOR循环

一、条件控制：if语句、case语句

1、IF语句

IF search_condition_1 THEN statement_list_1
    [ELSEIF search_condition_2 THEN statement_list_2] ...
    [ELSE statement_list_n]
END IF

　　如果条件search_condition_1为true，则执行相应的then子句后面的语句列表statement_list_1；

　　如果条件search_condition_1不为true，则判断ELSEIF子句中的条件search_condition_2是否为true，如果为true，则执行相应的then子句后面的语句列表statement_list_2；

　　……

　　如果所有的条件都不为true，则执行ELSE子句后面的语句。

例1：创建过程，判断两个输入参数哪一个大

mysql> DELIMITER $$ mysql> CREATE PROCEDURE difference( -> 　　IN p1 INTEGER, -> 　　IN p2 INTEGER, -> 　　OUT p3 INTEGER) -> BEGIN -> 　　IF p1 > p2 THEN SET p3 = 1; -> 　　  ELSEIF p1= p2 THEN SET p3 = 2; -> 　　  ELSE SET p3 = 3; -> END IF; -> END $$ mysql> DELIMITER ; mysql> call difference(12,56,@ax); mysql> select @ax; +------+
| @ax  |
+------+
|    3 |
+------+

解析：输入的第一个参数对应p1，第二个对应p2，@ax用来接收输出变量p3；如果p1>p2，输出1；如果p1=p2，输出2；其他情况，输出3。

例2：创建过程，表示出players表和penalties表哪一个行数更多—>IF条件中允许包含标量子查询

mysql> create procedure `TENNIS`.`largest`(out t char(10)) -> begin -> 　　if (select count(*) from PLAYERS)>(select count(*) from PENALTIES) -> 　　then     -> 　　　　set t='PLAYERS'; ->    elseif (select count(*) from PLAYERS)=(select count(*) from PENALTIES) -> then     -> 　　　　set t='equal'; ->    else
    -> 　　　　set t='PENALTIES'; ->    end if; -> end $$ mysql> delimiter ; mysql> call largest(@lgt); mysql> select @lgt; +---------+
| @lgt    |
+---------+
| PLAYERS |
+---------+

2、CASE语句

1）simple case：简易case语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

case_value是一个表达式，该值和每个when子句中的when_value值进行相等比较：

　　①如果和某个when子句中的when_value值相等，则执行相应的then子句后面的语句statement_list；

　　②如果没有when_value值相等，则执行else子句后面的statement_list。

mysql> DELIMITER $$ mysql> CREATE PROCEDURE p1() -> BEGIN -> 　　DECLARE v INT DEFAULT 3; -> 　　CASE v ->       WHEN 2 THEN SELECT v; ->       WHEN 3 THEN SELECT 0; -> ELSE -> BEGIN -> END -> 　　END CASE; -> END $$ mysql> DELIMITER ; mysql> call p1(); +---+
| 0 |
+---+
| 0 |
+---+

2）searched case：检索型case语句

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

对于每个when子句，判断后面的布尔表达式search_condition是否为true：

　　①如果某个when子句的条件为true，则执行相应的then子句后面的语句statement_list；

　　②如果所有的when子句的条件都不为true，则执行else后面的语句statement_list。

mysql> DELIMITER $$ mysql> CREATE PROCEDURE p2( -> IN p1 INTEGER, -> IN p2 INTEGER, -> OUT p3 INTEGER) -> BEGIN -> CASE ->          WHEN p1>p2 THEN SET p3=1; ->          WHEN p1=p2 THEN SET p3=2; ->          ELSE SET p3 = 3; -> END CASE; -> END$$ mysql> DELIMITER ; mysql> call p2(123,321,@ax); mysql> select @ax; +------+
| @ax  |
+------+
|    3 |
+------+

注意：

　　①如果在case中，没有一个when子句的比较结果为true，并且没有写else部分，那么就抛出异常：‘Case not found for CASE statement’；

　　②statement_list如果有多条语句，使用begin…end块包围起来（复合语句）。

二、循环控制：while循环、repeat循环、loop循环、leave语句、iterate语句

Tips：循环体结构

　　①条件

　　②SQL语句体

　　③程序体里面需要对条件中的变量进行处理

1、WHILE循环

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

首先判断循环开始条件search_condition是否为true(循环结束条件)：

　　如果为true，则执行循环体中的语句statement_list。每执行完一次，都要重新判断条件search_condition是否为true；

　　如果条件search_condition为false，则循环结束。

特点：

　　先判断，后执行

mysql> DELIMITER $$ mysql> CREATE PROCEDURE do_while(x int) -> BEGIN -> 　　DECLARE v INT DEFAULT 5; -> 　　set v=x; -> 　　WHILE v>0 DO -> 　　　　select v; -> 　　　　SET v=v-1; -> 　　END WHILE; -> END$$ mysql> DELIMITER ; mysql> call do_while(2); +------+
|  v   |
+------+
|    2 |
+------+

+------+
|  v   |
+------+
|    1 |
+------+

2、REPEAT循环

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

反复执行循环体中的语句statement_list，直到until条件search_condition 为true时，循环结束

特点：

　　先执行，后判断

mysql> DELIMITER $$ mysql> CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> 　　SET @x=0; -> 　　REPEAT -> 　　　　SET @x = @x + 1; -> 　　UNTIL @x > p1 END REPEAT; -> END$$ mysql> DELIMITER ; mysql> CALL dorepeat(1000); mysql> SELECT @x; +------+
| @x   |
+------+
| 1001 |
+------+

@x：用户变量，直接使用“set @x=0；”就可以生成这么一个变量，不需要数据类型，在存储过程里定义的用户变量，存储过程外面可以访问，一直被保存。

3、LOOP循环

[begin_label:] LOOP
    statement_list ;
END LOOP [begin_label];

　　反复执行循环体中的语句，直到循环结束；

　　循环的结束使用leave语句。

例：创建过程，等待指定的秒数后结束

mysql> delimiter $$ mysql> create procedure wait_s(in wait_seconds int) -> begin -> 　　declare end_time datetime default now() + interval wait_seconds second;　　#interval是间隔类型关键字     -> 　　wait_loop:loop -> 　　　　if now() > end_time -> 　　　　then -> 　　　　　　leave wait_loop;　　#leave语句表离开
    -> 　　　　end if; -> 　　end loop wait_loop; -> end $$ mysql> delimiter ; mysql> call wait_x(10); ……等10秒，结束……

4、LEAVE语句

LEAVE  label ;

作用：用来退出带标签的语句块或者循环

用处：用在 BEGIN … END中或者循环中 (LOOP, REPEAT, WHILE)

例：创建过程，其中的一个语句块较早的结束

mysql> DELIMITER $$ mysql> CREATE PROCEDURE small_exit(OUT p1 INTEGER,OUT p2 INTEGER) -> BEGIN -> 　　SET p1 = 1; -> 　　SET p2 = 1; -> 　　block1:BEGIN -> 　　　　LEAVE block1; #离开块block1
    -> 　　　　SET p2 = 3; #已离开，不执行
    -> 　　END block1;
    -> 　　SET p1 = 4; #执行
    -> END$$ mysql> DELIMITER ; mysql> call small_exit(@r1,@r2); mysql> select @r1,@r2; +------+------+
| @r1  | @r2  |
+------+------+
|    4 |    1 |
+------+------+

5、ITERATE语句

ITERATE label;

只能出现在循环LOOP、REPEAT和WHILE 中(有标签)

含义：跳出本次循环，开始一次新的循环

mysql> delimiter $$ mysql> CREATE PROCEDURE do_iterate(p1 INT) -> BEGIN -> 　　label_1: LOOP -> 　　　　SET p1 = p1 + 1; -> 　　　　IF p1 < 10 THEN　ITERATE label_1; #开始下一次循环
    -> 　　　　END IF; -> 　　　　LEAVE label_1; -> 　　END LOOP label_1; -> 　　SET @x = p1; -> END$$ mysql> delimiter ; mysql> call do_iterate(1); mysql> select @x; +------+
| @x   |
+------+
|   10 |
+------+