【1】IF ….THEN….ELSE….END IF

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

【3】WHILE …DO…END WHILE

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 …end repeat

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_LABLE:loop ….leave LOOP_LABLE…..end loop

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

【6】LOOP_LABLE:loop…ITERATE LOOP_LABLE;…leave LOOP_LABLE;….end loop

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; 
 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

详细解释请参看：​​Mysql存储过程详解​​