mysql之存储过程全面解析

1.什么是存储过程

存储过程和函数是事先经过编译并存储在数据库中的一些sql语句，调用存储过程可以简化我们的开发。存储过程和函数的区别在于，函数是有返回值的，存储过程是没有返回值的。

2.存储过程的语法

//创建存储过程
delimiter$
create procedure p1()
begin
	语句集合
end$

查看存储过程：

show procedure status$

调用存储过程：

call p1()$

3.存储过程中的变量，表达式，控制语句

变量定义：

格式 declare 变量名 变量类型 [default 默认值]
变量赋值：

set 变量名:=变量名+10;

控制语句：

if 条件 then

语句

else

语句

end if;

例子：

delimiter$
create procedure p1()
begin
	declare num1 int default 20;
	if num1>10 then 
		set num1:=num1+1;
	else
		set num1:=num1+10;
	end if;
	select * from goods where num =num1;
end$

4.存储过程之参数传递

例子：条件查询

create procedure p2(in num1 int,in name varchar(20))
begin
	if num1 is not null then
		if name is not null then
			select * from goods where num=num1 and name=name;
		else
			select * from goods where num=num1;
		end if;
	else
		if name is not null then
			select * from goods where name=name;
		else
			select * from goods;
		end if;
	end if;
end$

call p3(9,'dog')$

结果：

5.存储过程之循环条件

类似于java中的while语句

create procedure p3()
begin
	declare total int default 0;
	declare num int default 0;
	while num<=10 do
		set total:=total+num;
		set num:=num+1;
	end while;
	select total;
end$

结果：

6.存储过程之输出参数

完成了之前的数字累加功能的输出：

delimiter$
create procedure p3(in n int,out total int)
begin
	declare num int default 0;
	set total :=0;
	while num<=n do
		set total:=total+num;
		set num:=num+1;
	end while;
end$

调用存储过程：

call p3(10,@total)$

打印结果：

select @total$

结果：

7.存储过程之输入输出参数

create procedure p4(inout num int)
begin
	set num:=num+20;
end$

设置参数变量，调用存储过程：

set @currentNum =10$

call p4(@currentNum)$

由于输入和输出都是变量，所以要先设置参数变量再传值，不可以直接传值。

打印结果：

select @currentNum$

结果：

8.存储过程之case语句

类似于java中的switch。。。case。。default语句

delimiter$
create procedure p5(in pos int)
begin
	case pos
	when 1 then select 'still flying';
	when 2 then select 'fall in the sea';
	when 3 then select 'in the island';
	else select 'I dont know';
	end case;
end$

结果：

9.存储过程之repeat语句

类似于java中的do。。。while()语句

create procedure p6()
begin
	declare i int default 1;
	repeat
		select i;
		set i :=i+1;
	until i>4 end repeat;
end$

结果：

10.存储过程之游标

在存储过程或者函数中，可以使用游标对结果集进行循环的处理，其中光标的使用包括声明declare、打开OPEN、取值FETCH和关闭CLOSE。

create procedure p6()
begin
	declare r_gid int;
	declare r_name varchar(20);
	declare r_num int;
	declare getgoods cursor for select gid,name,num from goods;
	open getgoods;
	fetch getgoods into r_gid,r_name,r_num;
	select r_name,r_num;
	close getgoods;
end$

结果：