mysql 存储过程 显示控制_mysql存储过程之控制语句篇

loop的使用 要结合leave 和 iterate

看到名字 都已经大概 猜测到了 意思

leave 就是离开退出循环 而 iterate就是 继续迭代

CREATE PROCEDURE ABC()

BEGIN

DECLARE a INT Default 0 ;

simple_loop: LOOP

SET a=a+1;

select a;

IF a=5 THEN

LEAVE simple_loop;

END IF;

END LOOP simple_loop;

END

create procedure pro

begin

declare a int default 1;

label1: loop

if a<6 then

select a;

set a=a+1;

iterate label1;

end if;

leave label1;

end loop label1;

end

3.REPEAT[begin_label:] REPEAT

statement_list

UNTIL search_condition

END REPEAT [end_label]

REPEAT语句内的语句或语句群被重复，直至search_condition为真。

create procedure pro

begin

declare a int default 3;

repeat

select a;

set a=a+1;

untile a>5 end repeat;

end

4.while

[label:] WHILE 条件 DO--循环处理END WHILE [label];

create procedure pro

begin

declare a int default 4;

while a<10 do

select a;

set a=a+1;

end while;

end

下面是一个摘抄的例子，主要看while如何使用

WHILE (id is not null ) DO

if(month='01'||month='02'||month='03') THEN set season='1';

end if;

if(month='04'||month='05'||month='06') THEN set season='2';

end if;

if(month='07'||month='08'||month='09') THEN set season='3';

end if;

if(month='10'||month='11'||month='12') THEN set season='4';

end if;

update t_industry_finance_instance set season_=season where ID_=id;

FETCH cur1 INTO id,month;

END WHILE;