【MySQL存储过程】流程控制的使用详解

目录

    引言    

        🍀IF语句

        🍀CASE语句

        🍀LOOP语句

        🍀LEAVE语句

        🍀ITERATE语句

        🍀REPEAT语句

        🍀WHILE语句

    流程控制综合运用

---

引言    

    在存储过程和函数中，可以使用流程控制来控制语句的执行。在MySQL中，可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

IF语句

     IF语句用来进行条件判断。根据条件执行不同的语句。其语法的基本形式如下：

IF search_condition THEN statement_list       
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]                           
END  IF

• 参数search_condition表示条件判断语句；
• 参数statement_list表示不同条件的执行语句。

例如 下面是一个IF语句的示例，代码如下：

delimiter $$
create procedure proc_age(in input int,out output int)
begin
    if input>20 then 
        set output=input+1;
    elseif input=20 then 
        set output=input+2;
    else 
        set output=input+3;
    end if;
end;
$$
delimiter ;

    该示例根据input与20的大小关系来执行不同的SET语句。如果input值大于20，将output的值加1；如果input值等于20，就将output的值加1；其他情况将output的值加1。IF语句都需要使用END IF来结束。 

    调用存储过程

call proc_age(23,@out);
select @out;
call proc_age(4,@out);
select @out;

    执行结果

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值的执行语句。

    下面是一个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结束。 

LOOP语句

    LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。LOOP语句本身没有停止循环，只有遇到LEAVE语句等才能停止循环。LOOP语句的语句形式如下： 

[begin_label:] LOOP             
statement_list                  
END LOOP [end_label] 

• 参数begin_label和参数end_label分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；
• 参数statement_list表示需要循坏执行的语句。  

    下面是一个LOOP语句的示例：

add_num:LOOP                 
     SET @count = @count + 1; 
END LOOP add_num;   

    该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。LOOP循环都以END LOOP结束。 

LEAVE语句

    LEAVE语句主要用于跳出循环控制（相当于C/C++ 的break），其语法形式如下： 

LEAVE label   

• 参数label表示循环的标志。 

    下面是一个LEAVE语句的示例： 

delimiter $$
create procedure proc_loop(in input int,out ouput int)
begin
    add_num:loop
        set input=input+1;
        if input=100 then
            leave add_num;
        end if;
    end loop add_num;
    set ouput=input;
end;
$$  
delimiter ;  

    该示例循环执行count值加1的操作。当count的值等于100时，LEAVE语句跳出循环。 

    调用存储过程：

call proc_loop(2,@num);
select @num;

ITERATE语句

    ITERATE语句也是用来跳出循环的语句，但是ITERATE语句是跳出本次循环，然后直接进入下一次循环（相当于C/C++ 的continue），ITERATE 语句的语法形式如下：

ITERATE label

• 参数label表示循环的标志。 

set @count = 1;
delimiter $$
create procedure proc_iterate()
begin
    add_num:loop
        set @count = @count+1;
        if @count = 4 then
            leave add_num;
        elseif mod(@count,2) =0 then
            iterate add_num;
        else
            select * from student;
        end if;
    end loop add_num;
end;
$$
delimiter ;

    该示例循环执行count加1的操作，count的值为4时结束循环。如果count的值能够整除2，就跳出本次循环，不再执行下面的SELECT语句。 

    调用存储过程：

call proc_iterate();

REPEAT语句

    REPEAT语句是有条件控制的循环语句（相当于C/C++ 的do ...while）。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

[begin_label:] REPEAT        

     statement_list;       

     UNTIL search_condition

END REPEAT [end_label]      

• 参数statement_list表示循环的执行语句；
• 参数search_condition表示结束循环的条件，满足该条件时循环结束。

   下面是一个REPEAT语句的示例。代码如下： 

REPEAT                        
     SET @count=@count+1;   
     UNTIL @count=100        
END REPEAT;         

    该示例循环执行count加1的操作，count值为100时结束循环。REPEAT循环都用END REPEAT结束。

WHILE语句

    WHILE语句也是有条件控制的循环语句，但WHILE语句和REPEAT语句是不一样的。WHILE语句是当满足条件时执行循环内的语句。WHILE语句的基本语法形式如下：

[begin_label:] WHILE search_condition DO   

        Statement_list                      

END WHILE [end_label]                     

• 参数statement_condition表示循环执行的条件，满足该条件时循环执行；
• 参数statement_list表示循环的执行语句。

    下面是一个WHILE语句的示例。代码如下：

WHILE @count<100 DO       
    SET @count = @count + 1;
END WHILE;

流程控制综合运用

1.下面是一张学生表，创建一个存储过程，输入班级编号，输出该班级的学生

delimiter $$
create procedure proc_query_student(in cid int)
begin
    declare temp_name varchar(64);  #必须定义在声明光标之前
    declare temp_id int;
    declare temp_sex enum('M','F');
    declare done int default 0;  #定义结束标志
    declare cur_student cursor for select name,id,sex from student where class_id=cid;
    declare continue handler for not found set done = 1; #将结束标志绑定到游标上
    open cur_student;
    read_loop:loop  #循环读取游标
        fetch cur_student into temp_name,temp_id,temp_sex;
            if done=1 then
                leave read_loop;
            end if;
        select temp_id,temp_name,temp_sex;  #打印从光标中获取到的值
     end loop read_loop;
    close cur_student;
end;
$$
delimiter ; 

    SQL语句测试及运行结果

call proc_query_student(101);
call proc_query_student(104);

---

2.在上面学生表中插入一条记录，并返回记录的自增长id 

#使用ast_insert_id()方法，获取当前会话的最后一次插入记录的id 

delimiter $$
create procedure proc_insert_student(in p_name varchar(64),in p_class_id int,in p_sex enum('M','F'),out sid int)
begin
    insert into student (name,class_id,sex) values(p_name,p_class_id,p_sex);
    #last_insert_id()方法可以取到当前会话的最后一次插入记录的id
    set sid=last_insert_id() ;
end ;
$$
delimiter ;

SQL语句测试

call proc_insert_student('王二',101,'M',@sid);