MySQL中存储过程中的控制语句使用详解

【1】if …then …elseif …then…else…end if

示例如下：

BEGIN
	#Routine body goes here...
  IF pinout = 0 THEN
	SELECT COUNT(*) FROM c_user  into pinout;
  ELSEIF pinout =1 THEN
    set pinout = 1;
  ELSE 
	set pinout =2;
  END IF;
END

MySQL中同样支持流程控制函数，如IF。

SELECT last_name,commission_pct,
IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;

完整存储过程实例如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_count`(in sex_id INT,OUT user_count INT)
BEGIN
	#if ...else   
    IF sex_id = 0 THEN
    SELECT COUNT(*) FROM p_user WHERE p_user.sex="女" into user_count;
    ELSE
    SELECT COUNT(*) FROM p_user WHERE p_user.sex="男" INTO user_count;
    END IF;
END

---

【2】case …when …then…else…end case

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc2`(in param int)
begin
DECLARE s int ;
-- CASE ...WHEN  THEN...ELSE...END CASE
set s = param+1;
CASE s mod 2
	when 0 THEN
	select CONCAT(param,' is 奇数');
	ELSE
	select CONCAT(param,' is 偶数');
	
end CASE;
end

在select中应用

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

实例2

SELECT 
	(CASE day_week 
	WHEN  1 THEN '星期天' 
	WHEN  2 THEN '星期一'
	WHEN  3 THEN '星期二'
	WHEN  4 THEN '星期三'
	WHEN  5 THEN '星期四'
	WHEN  6 THEN '星期五'
	WHEN  7 THEN '星期六' 
	ELSE null END) as day_week,
 count(DISTINCT(if(gs.month_year=1,gs.order_num,null))) AS 'Janurary',
 count(DISTINCT(if(gs.month_year=2,gs.order_num,null))) AS 'February',
 count(DISTINCT(if(gs.month_year=3,gs.order_num,null))) AS 'March',
 count(DISTINCT(if(gs.month_year=4,gs.order_num,null))) AS 'April',
 count(DISTINCT(if(gs.month_year=5,gs.order_num,null))) AS 'May',
 count(DISTINCT(if(gs.month_year=6,gs.order_num,null))) AS 'June',
 count(DISTINCT(if(gs.month_year=7,gs.order_num,null))) AS 'July',
 count(DISTINCT(if(gs.month_year=8,gs.order_num,null))) AS 'August',
 count(DISTINCT(if(gs.month_year=9,gs.order_num,null))) AS 'September',
 count(DISTINCT(if(gs.month_year=10,gs.order_num,null))) AS 'October',
 count(DISTINCT(if(gs.month_year=11,gs.order_num,null))) AS 'November',
 count(DISTINCT(if(gs.month_year=12,gs.order_num,null))) AS 'December'

from goods_sale gs where gs.leaguer_code != 'null'  GROUP BY day_week;

---

在mybatis中使用

<select id="queryDataList" resultType="java.util.HashMap">
   select enodeb_name,
          max(eqpt_max_me_load) as eqpt_max_me_load,
          max(eqpt_mean_me_load) as eqpt_mean_me_load ,
          CASE
              WHEN #{timeGroupByType} = '1' THEN DATE_FORMAT(report_time, '%Y-%m-%d %H:%i')
              WHEN #{timeGroupByType} = '2' THEN DATE_FORMAT(report_time, '%Y-%m-%d %H')
              WHEN #{timeGroupByType} = '3' THEN DATE_FORMAT(report_time, '%Y-%m-%d')
              WHEN #{timeGroupByType} = '4' THEN WEEK(report_time,1)
              WHEN #{timeGroupByType} = '5' THEN DATE_FORMAT(report_time, '%Y-%m')
           END as reportTime
   from tb_load_performance
   where enodeb_id =#{enodebId}
     and report_time  <![CDATA[ >= ]]> #{beginTime}
     and report_time <![CDATA[ <= ]]> #{endTime}
   group by reportTime
</select>

【3】while ··do·· end while

如下在存储过程中应用while do:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`()
begin 
declare var int; 
-- WHILE ...DO...END WHILE...
set var=0; 
while var<6 do 
insert into t_user(age) value(var); 
set var=var+1; 
end while; 
end

---

【4】repeat··until·· end repeat

相当于do …while;

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc4`()
begin  
-- repeat ...end repeat 
 declare v int; 
 set v=0; 
 repeat 
	 insert into t_user(age) values(v); 
	 set v=v+1; 
	 until v>=5 
 end repeat; 
 end

---

【5】loop ·····end loop

loop循环不需要初始条件，这点和while循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环,使用leave LOOP_LABLE终止。

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`()
begin 
 -- LOOP_LABLE:loop ....leave LOOP_LABLE.....end loop;
 declare v int; 
 set v=7; 
 LOOP_LABLE:loop 
	 insert into t_user(age) values(v); 
	 set v=v+1; 
	 if v >=20 then 
		leave LOOP_LABLE; 
	 end if; 
 end loop; 
end

需要说明的是LABLES标号：

标号可以用在begin，repeat，while或者loop语句前，语句标号只能在合法的语句前面使用。

可以跳出循环，使运行指令达到复合语句的最后一步。

标号用在begin前示例如下：

create procedure proc11(in v_num int,out o_result int)
zero_back:begin

	IF v_num <> 4 THEN	
			set o_result = -1;
			LEAVE zero_back; -- 跳出函数,不再向下执行
    END IF;
    insert into t_user(age) values(v_num);
    select count(*) into o_result from t_user;
end

leave 相当于break，跳出循环。根据标号位置不同，甚至可以跳出函数。

---

【6】ITERATE迭代

通过引用复合语句的标号,来重新开始复合语句；相当于continue。

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc5`()
begin 
-- LOOP_LABLE:loop...ITERATE LOOP_LABLE;...leave LOOP_LABLE;....end loop;
 declare v int; 
 set v=0; 

 LOOP_LABLE:loop 

 if v=3 then  
	 set v=v+1; 
	 
	 ITERATE LOOP_LABLE; -- 等于3时，v+1,进行下一次循环，不会插入数据
 end if; 

 insert into t_user(age) values(v); 
 set v=v+1; 
 if v>=5 then 
 leave LOOP_LABLE; 
 end if; 
 end loop; 
end

result as follows :

更多详细解释请参看：MySQL存储过程详解