MySQL存储过程控制语句

MySQL存储过程控制语句

1、case when 语句

语法：
case case_val
when when_val then result
when when_val then result
…
[else result]
end case

case 
	when condition_statement then result 
	when condition_statement then result 
	...
	[else result] 
end case 

create procedure p_case(in num int)
begin
	case num 
			when 1 then select 'man' gender from dual;
			when 2 then select 'woman' gender from dual;
			else 
				begin
				end;
	end case;
end;

call p_case(1);
call p_case(2);

DELIMITER |
CREATE PROCEDURE p(in pi_num int)
BEGIN

CASE pi_num
WHEN 2 THEN SELECT ‘TWO’;
WHEN 3 THEN SELECT ‘THREE’;
ELSE
BEGIN
END;
END CASE;

END;
|
DELIMITER ;

call p(1);
call p(2);
call p(3);

2、if语句

语法：
if condition then result
[elseif condition then result]
[elseif condition then result]
…
[else result]
end if

create procedure p_if(in a int,in b int)
	begin
		declare r varchar(20);
		
		if a > b then set r = ' > ';
			elseif a = b then set r = ' = ';
			else set r = ' < ';
		end if;
		
		set r = concat(a,r,b);
		
		select r from dual;
	end;

call p_if(1,0);
call p_if(1,1);
call p_if(1,2);

3、iterate语句

语法：
iterate label;

iterate迭代只能和loop，repeat，while语句配合使用

4、leave语句

语法：
leave label;

leave退出循环或程序块，只能和beign ... end， loop repeat,while语句配合使用

5、loop语句

语法：
begin_label:loop
statement_list
end loop [end_label];

create procedure p_loop()
	begin
		declare sumResult int default 0;
		declare num int default 1;
		
		loop_label: loop
			set sumResult = sumResult + num;
			set num = num + 1;
			if num <= 10 then iterate loop_label; end if;
		  leave loop_label;
		end loop loop_label;
		
		select sumResult from dual;
		
	end;

call p_loop();

6、repeat语句

语法：
begin_label:repeat
statement_list
until condition
end repeat [end_label]

-- 给n2加1直到 n2>n1
create procedure p_repeat()
	begin
		declare n1 int default 10;
		declare n2 int default 1;
		
		repeat_label: repeat
			set n2 = n2+1;
			until n2 > n1
		end repeat repeat_label;
		
		select n2 from dual;
	end;

call p_repeat();

7、while语句

语法：
begin_lable: while condition do
statement_list
end while [end_label]

drop procedure p_while;
create procedure p_while()

	begin
		declare n1 int default 10;
		
		while_label:while n1 < 0 do 
			set n1 = n1 - 1;
		end while while_label;
		select n1 from dual;
	end;

call p_while();