MySQL-5.7 高阶语法及流程控制-白红宇

MySQL-5.7 高阶语法及流程控制

阅读量：4983 次

发布时间：2019-06-12

本文共 8936 字，大约阅读时间需要 29 分钟。

1.标签语句

[begin_label:] BEGIN    [statement_list]END [end_label][begin_label:] LOOP    statement_listEND LOOP [end_label][begin_label:] REPEAT    statement_listUNTIL search_conditionEND REPEAT [end_label][begin_label:] WHILE search_condition DO    statement_listEND 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 CASEOr: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_listEND 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_listUNTIL search_conditionEND 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_listEND 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)

转载于:https://www.cnblogs.com/tongxiaoda/p/8022858.html

你可能感兴趣的文章

Sublime html <head>自动补全