- 流程控制的使用
在存储过程和函数中,可以使用流程控制来控制语句的执行。在MySQL中,可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。
1. IF语句
IF语句用来进行条件判断。根据条件执行不同的语句。其语法的基本形式如下:
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
参数search_condition表示条件判断语句;参数statement_list表示不同条件的执行语句。
---【demo】下面是一个IF语句的示例,代码如下:
IF age>20 THEN SET @count1=@count1+1;
ELSEIF age=20 THEN @count2=@count2+1;
ELSE @count3=@count3+1;
END IF;
该示例根据age与20的大小关系来执行不同的SET语句。如果age值大于20,将count1的值加1;如果age值等于20,就将count2的值加1;其他情况将count3的值加1。IF语句都需要使用END IF来结束。
#demo mysql> use school; #选择数据库school mysql> DELIMITER $$ mysql> create procedure proc_test_if (IN input int, OUT output int) begin if input>20 then set input=input+1; elseif input=20 then set input=input+2; else set input = input+3; end if;
set output = input; end; mysql> $$ mysql> DELIMITER ; |
2. CASE语句
CASE语句可实现比IF语句更复杂的条件判断,其语法的基本形式如下:
CASE case_value
WHEN when_value THEN statement_list
[ WHEN when_value THEN statement_list ]
[ELSE statement_list]
END CASE
其中,参数case_value表示条件判断的变量;参数when_value表示变量的取值;参数statement_list表示不同when_value值的执行语句。
---【demo】下面是一个CASE语句的示例。代码如下:
CASE level
WHEN 20 THEN SET attack = attack + 5;
WHEN 30 THEN SET attack = attack + 10;
WHEN 40 THEN SET attack = attack + 15;
ELSE SET attack = attack + 1;
END CASE
当级别level值为20时,attack值加5;当级别level值为30时,attack值加10;当级别level值为40时,attack值加15;否则,attack + 1。CASE语句使用END CASE结束。
3. LOOP语句
LOOP语句可以使某些特定的语句重复执行,实现一个简单的循环。LOOP语句本身没有停止循环,只有遇到LEAVE语句等才能停止循环。LOOP语句的语句形式如下:
[begin_label:] LOOP
statement_list
END LOOP [end_label]
其中,参数begin_label和参数end_label分别表示循环开始和结束的标志,这两个标志必须相同,而且都可以省略;参数statement_list表示需要循坏执行的语句。
---【demo】下面是一个LOOP语句的示例,代码如下:
add_num:LOOP
SET @count = @count + 1;
END LOOP add_num;
该示例循环执行count加1的操作。因为没有跳出循环的语句,这个循环成了一个死循环。LOOP循环都以END LOOP结束。
4. LEAVE语句
LEAVE语句主要用于跳出循环控制,其语法形式如下:
LEAVE label
其中,参数label表示循环的标志。
---【demo】下面是一个LEAVE语句的示例。代码如下:
add_num: LOOP
SET @count=@count + 1;
Select @count;
IF @count = 100 THEN
LEAVE add_num;
END IF;
END LOOP add_num;
该示例循环执行count值加1的操作。当count的值等于100时,LEAVE语句跳出循环。
5. ITERATE语句
ITERATE语句也是用来跳出循环的语句,但是ITERATE语句是跳出本次循环,然后直接进入下一次循环,ITERATE语句的语法形式如下:
ITERATE label
其中,参数label表示循环的标志。
---【demo】下面是一个ITERATE语句的示例。代码如下:
add_num1:LOOP
Set @count = @count +1
IF @count=100 THEN
LEAVE add_num1
ELSE IF MOD(@count, 3) = 0 then
ITERATE add_num1;
Select * from student;
END LOOP add_num1;
该示例循环执行count加1的操作,count的值为100时结束循环。如果count的值能够整除3,就跳出本次循环,不再执行下面的SELECT语句。
注意: LEAVE语句和ITERATE语句都用来跳出循环语句,但是两者的功能是不一样的。LEAVE语句是跳出整个循环,然后执行循环后面的程序,和C++ break 相似。ITERATE语句是跳出本次循环,然后进入下一次循环,和C++ continue 相似。使用这两个语句时一定要区分清楚。
6. REPEAT语句
REPEAT语句是有条件控制的循环语句。当满足特定条件时,就会跳出循环语句。REPEAT语句的基本语法形式如下:
[begin_label:] REPEAT
statement_list;
UNTIL search_condition
END REPEAT [end_label]
其中,参数statement_list表示循环的执行语句;参数search_condition表示结束循环的条件,满足该条件时循环结束。
---【demo】下面是一个REPEAT语句的示例。代码如下:
REPEAT
SET @count=@count+1;
UNTIL @count=100
END REPEAT;
该示例循环执行count加1的操作,count值为100时结束循环。REPEAT循环都用END REPEAT结束。
7. WHILE语句
WHILE语句也是有条件控制的循环语句,但WHILE语句和REPEAT语句是不一样的。WHILE语句是当满足条件时执行循环内的语句。WHILE语句的基本语法形式如下:
[begin_label:] WHILE search_condition DO
Statement_list
END WHILE [end_label]
其中,参数statement_condition表示循环执行的条件,满足该条件时循环执行;参数statement_list表示循环的执行语句。
---【demo】下面是一个WHILE语句的示例。代码如下:
WHILE @count<100 DO
SET @count = @count + 1;
END WHILE;
流程控制综合运用
【demo】循环访问光标操作,访问光标中的所有记录,代码如下:
mysql> use school; #选择数据库school mysql> DELIMITER $$ mysql> create procedure query_all_students (IN sid int, OUT cname varchar(128), OUT cid int) BEGIN declare tmp_name varchar(128); #必须定义在声明光标之前 declare tmp_cid int; declare done int default 0; declare cur_student CURSOR FOR SELECT name, class_id FROM student ; declare continue handler for not found set done = 1; #将结束标志绑定到游标上 open cur_student; read_loop:LOOP #循环读取 fetch cur_student into tmp_name, tmp_cid; IF done=1 then Leave read_loop; END IF; select tmp_name, tmp_cid; #打印从光标中获取到的值 END LOOP read_loop; close cur_student; set cname = tmp_name, cid = tmp_cid; END; mysql> $$ mysql> DELIMITER ; |
【demo】在学生表中插入一条记录,并返回记录的自增长id
mysql> use school; #选择数据库school mysql> DELIMITER $$ mysql> create procedure fetch_insert_student_id (IN p_name varchar(128), in p_class_id int, IN p_sex char(1), OUT rid int) BEGIN Insert into student (name, class_id, sex) values(p_name, p_class_id, p_sex); select last_insert_id() as rid; END; mysql> $$ mysql> DELIMITER ; |
查看存储过程
存储过程创建以后,用户可以通过SHOW STATUS语句来查看存储过程的状态,也可以通过SHOW CREATE语句来查看存储过程的定义。用户也可以通过查询information_schema数据库下的Routines表来查看存储过程的信息。本节将详细讲解查看存储过程的状态与定义的方法。
- SHOW STATUS语句查看存储过程
在MySQL中,可以通过SHOW STATUS语句。其基本语法形式如下:
SHOW PROCEDURE STATUS [ like ‘pattern’ ] ;
其中,参数PROCEDURE表示查询存储过程;参数LIKE 'pattern'用来匹配存储过程的名称。
- 使用SHOW CREATE语句查看存储过程的定义
在MySQL中,可以通过SHOW CREATE语句查看存储过程的状态,语法形式如下:
SHOW CREATE PROCEDURE proc_name
其中,参数PROCEDURE表示查询存储过程;参数proc_name表示存储过程的名称。
---【demo】查询名为proc_delete_student的存储过程的状态,代码如下,执行结果如下图所示。
SHOW CREATE PROCEDURE proc_delete_student \G
- 从information_schema.Routine表中查看存储过程的信息
存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下:
SELECT * FROM information_schema.Routines
Where ROUTINE_NAME = ‘proc_name’;
其中,字段ROUTINE_NAME是Routines 存储存储过程和函数的列名称;参数proc_name表示存储过程或函数的名称。
---【demo】下面从Routines表中查询名为proc_delete_student的存储过程信息,具体SQL代码如下,执行结果如下图所示。
select routine_definition from information_schema.Routines where routine_name='proc_delete_student';
存储过程的删除
在MySQL中删除存储过程通过SQL语句DROP完成:
DROP PROCEDURE proc_name;
在上述语句中,关键字DROP PROCEDURE用来表示实现删除存储过程,参数proc_name表示所要删除的存储过程名称。
---【demo】执行SQL语句DROP PROCEDURE,删除存储过程对象proc_delete_student,具体步骤如下:
DROP PROCEDURE proc_delete_student;