参考:http://blog.chinaunix.net/u3/93826/showart_1923636.html
控制语句 不论什么编程语言都会设计到
我们先来介绍一下 oracle 的控制语句吧
ifelseelsifwhileforloop
而在mysql中的控制语句是:
ifelseelseifwhilelooprepeatleaveiterate
1.针对if else elseif 实例:
if mobile='13911113222' and psw='720717' then
set useridx = 10008888;
else if mobile='13911113333' and psw='720717' then
set useridx = 10006666;
else
select UserID into useridx from User_ID order by rand() limit 1;
end if;
2.loopleaveiterate 实例:
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;