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

---

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

BEGINDECLARE s int ;-- CASE ...WHEN  THEN...ELSE...END CASEset s = param+1;    CASE s mod 2        WHEN 0 THEN        select CONCAT(param,' is 奇数');        ELSE        select CONCAT(param,' is 偶数');    end CASE;END

【实例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;

---

【3】while ··do·· end while

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;

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语句的意义是离开循环。

begin  -- LOOP_LABLE:loop ....leave LOOP_LABLE.....end loop; declare v int;  set v=7;  LOOP_LABLE:loop //LABLES标号     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。

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存储过程详解点击查看
【http://blog.csdn.net/j080624/article/details/72331013】