1.标签语句
[begin_label:] BEGIN
[statement_list]
END [end_label]
[begin_label:] LOOP
statement_list
END LOOP [end_label]
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
- 标签label可以加在begin...end语句以及loop，repeat和while语句上
- 语句中通过iterate和leave来控制流程，iterate表示返回指定标签位置，leave表示跳出标签
实例：
mysql> delimiter //
mysql> create procedure doiterate(in p1 int,out p2 int)
-> begin
-> label1:loop
-> set p1 = p1 + 1;
-> if p1 < 10 then iterate label1;end if;
-> leave label1;
-> end loop label1;
-> set p2=p1;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call doiterate(1,@a);
Query OK, 0 rows affected (0.00 sec)
mysql> select @a;
+------+
| @a |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql> call doiterate(5,@a);
Query OK, 0 rows affected (0.00 sec)
mysql> select @a;
+------+
| @a |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
2.Declare语句
语法：
DECLARE var_name [, var_name] ... type [DEFAULT value]
- Declare语句通常声明本地变量、游标、条件或者handler
- Declare语句只允许出现在begin...end语句中而且必须出现在第一行
- Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler
- 使用default指定变量的默认值，如果没有指定默认值则为NULL
- 声明的变量和被引用的数据表中的字段要区分开
存储过程中的变量
- 本地变量可以通过declare语句声明
- 声明后的变量可以通过select...into var_list进行赋值，或者通过set语句赋值，或者通过定义游标并使用fetch...into var_list赋值
mysql> delimiter //
mysql> create procedure sp1(v_sid int)
-> begin
-> declare xname varchar(64) default 'bob';
-> declare xgender int;
-> select sname,gender into xname,xgender from students where sid=v_sid;
-> select xname,xgender;
-> end//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql>
mysql> call sp1(1);
+--------+---------+
| xname | xgender |
+--------+---------+
| Andrew | 1 |
+--------+---------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
3.流程控制语句
（1）case语句
在存储过程或函数中表明复杂的条件选择语句。
语法：
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_value与when_value依次做相等对比，如果相等则执行对应的后面的SQL语句，否则接着对比；
- 当search_condition满足true/1的结果时，则执行对应的SQL语句，否则执行else对应的SQL语句；
实例：
mysql> delimiter //
mysql> create procedure exp_case(v_sid int)
-> begin
-> declare v int default 1;
-> select gender into v from students where sid=v_sid;
-> case v
-> when 0 then update students set gender=1 where sid=v_sid;
-> when 1 then update students set gender=0 where sid=v_sid;
-> else
-> update students set gender=-1 where sid=v_sid;
-> end case;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> select * from students where sid=1;
+-----+--------+--------+---------+
| sid | sname | gender | dept_id |
+-----+--------+--------+---------+
| 1 | Andrew | 1 | 1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)
mysql> call exp_case(1);
Query OK, 1 row affected (0.03 sec)
mysql> select * from students where sid=1;
+-----+--------+--------+---------+
| sid | sname | gender | dept_id |
+-----+--------+--------+---------+
| 1 | Andrew | 0 | 1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)
另一种写法：
mysql> delimiter //
mysql> create procedure exp_case2(v_sid int)
-> begin
-> declare v int default 1;
-> select gender into v from students where sid=v_sid;
-> case
-> when v=0 then update students set gender=1 where sid=v_sid;
-> when v=1 then update students set gender=0 where sid=v_sid;
-> else
-> update students set gender=-1 where sid=v_sid;
-> end case;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call exp_case2(1);
Query OK, 1 row affected (0.03 sec)
mysql> select * from students where sid=1;
+-----+--------+--------+---------+
| sid | sname | gender | dept_id |
+-----+--------+--------+---------+
| 1 | Andrew | 1 | 1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)
（2）IF语句
在存储过程或函数中表明基础的条件选择语句
语法：
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
- 如果search_condition满足true/1的条件，则执行对应的SQL语句，否则再判断elseif中的search_condition，都不满足则执行else中的SQL语句；
- statement_list中可以包含一个或多个SQL语句
实例：
mysql> delimiter //
mysql> create function SimpleCompare(n int,m int)
-> returns varchar(20)
-> begin
-> declare s varchar(20);
-> if n > m then set s = '>';
-> elseif n = m then set s = '=';
-> else set s = '<';
-> end if;
-> set s = concat(n,'',s,'',m);
-> return s;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> select SimpleCompare(5,6);
+--------------------+
| SimpleCompare(5,6) |
+--------------------+
| 5<6 |
+--------------------+
1 row in set (0.00 sec)
mysql> select SimpleCompare(15,34);
+----------------------+
| SimpleCompare(15,34) |
+----------------------+
| 15<34 |
+----------------------+
1 row in set (0.00 sec)
mysql> select SimpleCompare(78,78);
+----------------------+
| SimpleCompare(78,78) |
+----------------------+
| 78=78 |
+----------------------+
1 row in set (0.00 sec)
IF嵌套：
mysql> delimiter //
mysql> create function verboseCompare(n int,m int)
-> returns varchar(50)
-> begin
-> declare s varchar(50);
-> if n = m then set s = 'equals';
-> else
-> if n > m then set s = 'greater';
-> else set s = 'less';
-> end if;
-> set s = concat('is',s,'than');
-> end if;
-> set s = concat(n,'',s,'',m,'.');
-> return s;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select verboseCompare(4,5);
+---------------------+
| verboseCompare(4,5) |
+---------------------+
| 4islessthan5. |
+---------------------+
1 row in set (0.00 sec)
（3）iterate语句
仅出现在loop，repeat，while循环语句中，表示重新开始此循环。
语法：
ITERATE label
（4）leave语句
表明指定标签的流程控制语句块，通常用在begin...end，以及loop，repeat，while循环汇总；
LEAVE label
实例：
mysql> delimiter //
mysql> create procedure doiterate2(in p1 int,out p2 int)
-> begin
-> label1:loop
-> set p1=p1+1;
-> if p1 < 10 then iterate label1;end if;
-> leave label1;
-> end loop label1;
-> set p2=p1;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
（5）loop语句
在存储过程或函数中表达循环执行的一种方式；
语法：
[begin_label:] LOOP
statement_list
END LOOP [end_label]
实例：
mysql> delimiter //
mysql> create procedure doiterate3(p1 int)
-> begin
-> label1:loop
-> set p1=p1+1;
-> if p1<10 then iterate label1;end if;
-> leave label1;
-> end loop label1;
-> set @x=p1;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
（6）repeat语句
在存储过程或函数中表达循环执行的一种方式；
语法：
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
实例：
mysql> delimiter //
mysql> create procedure doiterate4(p1 int)
-> begin
-> set @x=0;
-> repeat
-> set @x=@x+1;
-> until @x>p1 end repeat;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call doiterate4(1000);
Query OK, 0 rows affected (0.02 sec)
mysql> select @x;
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
（7）while语句
在存储过程或函数中表达循环执行的一种方式；
语法：
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
- 当search_condition返回true时，循环执行SQL语句，直到search_condition为false；
实例：
mysql> delimiter //
mysql> create procedure dowhile()
-> begin
-> declare v1 int default 5;
-> while v1>0 do
-> update students set gender=-1 where sid=v1;
-> set v1=v1-1;
-> end while;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call dowhile();
Query OK, 1 row affected (0.63 sec)
mysql> select * from students;
+-----+--------+--------+---------+
| sid | sname | gender | dept_id |
+-----+--------+--------+---------+
| 1 | Andrew | -1 | 1 |
| 2 | Andy | -1 | 1 |
| 3 | Bob | -1 | 1 |
| 4 | Ruth | -1 | 2 |
| 5 | Mike | -1 | 2 |
| 6 | John | 0 | 3 |
| 7 | Cindy | 1 | 3 |
| 8 | Susan | 1 | 3 |
+-----+--------+--------+---------+
8 rows in set (0.00 sec)
（8）return语句
在函数中，用来终结函数的执行并将指定值返回给调用者；
语法：
RETURN expr
- 在函数中必须有至少一个return语句，当有多个return语句时则表明函数有多种退出方式；
实例：
mysql> delimiter //
mysql> create function doreturn()
-> returns int
-> begin
-> select gender into @a from students where sid = 1;
-> if @a=1 then return 1;
-> elseif @a=0 then return 0;
-> else return 999;
-> end if;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select doreturn();
+------------+
| doreturn() |
+------------+
| 999 |
+------------+
1 row in set (0.00 sec)
mysql> select * from students where sid = 1;
+-----+--------+--------+---------+
| sid | sname | gender | dept_id |
+-----+--------+--------+---------+
| 1 | Andrew | -1 | 1 |
+-----+--------+--------+---------+
1 row in set (0.00 sec)