MySQL的各种存储过程和流程控制详解实例

1.存储过程详解

• 创建第一个存储过程

create procedure p1()
begin
select now();
end

• 测试第一个存储过程

call p1;

• 创建一个具有变量的存储过程

create procedure p2()
begin
##局部变量
declare a int default 1;
select a;
end

• 测试一个具有变量的存储过程

call p2();

• 顺便介绍一下会话变量

set @m=1;##会话变量
select @m;

• 创建一个查询表数据复制给变量的存储过程

create procedure p3()
begin
declare a int;
select eid into a from employee where ename='小明';
select a;
end

• 测试

call p3();

• 创建定义一个带光标的存储过程

create procedure p4()

begin
##定义接收使用的变量
declare
	v_id int;
declare
	v_name VARCHAR(20);
declare
	v_balance double(9,2);
##定义游标
declare
	cur_employee cursor for select * from employee where eid=1;
	##打开游标
	open cur_employee;
	##抓取游标
		fetch cur_employee into v_id,v_name,v_balance;
	##关闭游标
	close cur_employee;
	##查询
	select v_id,v_name,v_balance;
end

• 测试

call p4;

2.流程控制语句

• 存储过程中的流程控制语句用于将多个sql语句划分或组合成符合业务逻辑的代码块。
• mysql的流程控制语句有7个
  • 1.if语句

create procedure p5(
	a int
)
begin
	if a>0 then
		select '>';
	elseif a=0 then
		select '=';
	else
		select '<';
	end if;
end;

• 测试

call p5(8);## >
call p5(-1);## <
call p5(0);## =

• 2.case语句

create PROCEDURE p6(
	str varchar(20)
)
begin
	case str
		when 'hello' then
			select '打招呼';
		when 'bye' then
			select '说再见';
		else 
			select 'no';
	end case;

end;

• 测试

call p6('hello'); ## 打招呼
call p6('其他'); ## no

• 3.loop语句
• 4.leave语句 结合使用

##测试从0加到10
create procedure p7(
 out r int
)
begin
	declare
		sum int default 0;
	declare
		i int default 0;
	##qq是标签 按照格式随意命名
	qq:loop
		set sum=sum+i;
		set i=i+1;
		
		if i>10 then
		##加过10 离开循环标签
			leave qq;
		end if;
	end loop qq;
	set r=sum;
	select r;
end;

• 测试

call p7(@r);
select @r;

• 5.iterate语句 Iterate语句仅出现在loop,repeat,while循环语句中，其含义表示重新开始此循环

##输出从1-10的偶数
create procedure p8()
begin
	declare
		i int default 0;
	##qq是标签 
		qq:loop
			set i=i+1;
			if i%2=1 then
				iterate qq;
			end if;	
			select i as '偶数';
			if i>=10 then
				leave qq;
			end if;
		end loop qq;
end;

• 测试

call p8();

• 6.repeat语句 相当于do-while循环 至少执行一次

##测试从1-10相加的和
create procedure p9(
	out r int 
)
begin
	declare
		sum int default 0;
	declare
		i int default 0;
	##qq是标签 按照格式随意命名
	rr:repeat
		set sum=sum+i;
		set i=i+1;
		until i>10 end repeat rr;
	set r=sum;
end;

• 测试

call p9(@r);
select @r;

• 7.while语句
  ##测试从1-10相加的和

create procedure p10()
begin
	declare
		sum int default 0;
	declare
		i int default 0;
	while i<=10 do 
	 set sum=sum+i;
	 set i=i+1;
	 end while;
	 select sum as '1-10';
end;

• 测试

call p10();