mysql循环判断结构

Mysql循环判断结构

在自定义函数或者存储过程中经常要用到的循环判断结构:

if判断:

  if 条件 then xxx;
  [elseif 条件 then xxx;]
  [else xxx;]
  end if;

case when 判断

  case 
  when 条件 then xxx;
  when 条件  then xxx;
  else xxx;
  end case;

while循环

[标签名:]while 条件 do
   xxx
  end while [标签名];

离开循环(必须加标签名)

leave 标签名;

跳过本次循环(必须加标签名):

iterate 标签名;

repeat循环

[标签名:]repeat
  xxxxx
  until 条件
  end repeat [标签名];

repeat与while相比至少执行一次

loop循环

[标签:]loop 
  xxxx
  if 条件 then leave [标签]; end if;
  end loop [标签];

loop循环必须手动leave,不然会无限循环下去

案例:实现偶数累加,如传入10,则2+4+6+8+10;
while实现:

begin
declare a int default 0;
declare b int default 0;
while_name:while a<=n DO
  if mod(a,2)=1 then set a:=a+1; ITERATE while_name ; end if;
  set b:=b+a;
  set a:=a+1;
  end while while_name;
return b;
end$

repeat实现

begin
declare a int default 0;
declare b int default 0;
repeat_name:repeat
  if mod(a,2)=1 then set a:=a+1; ITERATE repeat_name ; end if;
  set b:=b+a;
  set a:=a+1;
  until a>n
  end repeat repeat_name;
return b;
end$

loop实现

begin
declare a int default 0;
declare b int default 0;
loop_name:loop
  if a>n then leave loop_name ;end if ;
  if mod(a,2)=1 then set a:=a+1; ITERATE loop_name ; end if;
  set b:=b+a;
  set a:=a+1;
  end loop loop_name;
return b;
end$