Mysql存储过程与存储函数

1. 简介

存储过程是事先经过编译并存储在数据库中的一段SQL集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL语言层面的代码封装和重用。

2. 存储过程的特点

• 封装和复用
• 可以接受参数，也可以返回数据
• 减少网络交互，效率提升

3. 存储过程操作语法

• 创建存储过程

create procedure 存储过程名称([参数列表])
begin
-- sql语句
end;

call 名称 ([参数]);
1
2
3
4
5
6

下面创建一个存储过程p1

create procedure p1()
begin
  select count(*) from 用户表;
end;
1
2
3
4

下面调用上面的存储过程

call p1;
1

• 查看存储过程

查询指定数据库的存储过程及状态信息

select * from information_schema.routines  where routine_schema='数据库名称';
-- 使用
select * from information_schema.routines  where routine_schema='Mysql_learn';
1
2
3

查询某个存储过程的定义

show create procedure 存储过程名称;
--使用
show create procedure p1;
1
2
3

• 删除存储过程

drop procedure [if exists] 存储过程名称;
1

注意：在命令行中执行创建存储过程的语法时可能会出现问题，因为命令行默认';'就结束sql语句，所以我们要通过关键字delimiter指定sql语句的结束符

delimiter $$;
1

4. 存储过程变量

• 系统变量

系统变量指的是Mysql服务器提供，不是用户定义的，属于服务器层面。分为全局变量(Global，所有会话有效)和会话变量(SESSION)

查看系统变量

-- 查看所有系统变量（默认为session级别）
show [session|global] variables;
-- 可以通过like模糊匹配的方式查找变量
show [session|global] variables like '....';
-- 查看指定变量的值
select @@[session|global].系统变量名;
1
2
3
4
5
6

设置系统变量

set [session|global] 系统变量名=值;
set  @@[session|global] 系统变量名=值;
1
2

• 用户自定义变量

用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用"@变量名"使用就可以。其作用域为当前连接。

赋值

set @var_name= expr [, @var_name=expr]...;
set @var_name :=expr...;
select @var_name :=expr ....;
select 字段名 into @var_name from 表名;
1
2
3
4

使用

select @var_name;
1

set @my_name :="你好";
select @my_name;
1
2

• 局部变量

局部变量是根据需要定义在局部生效的变量，访问之前，需要Declare声明，可用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明的Begin…End块中。

声明

declare 变量名 变量类型[default...];
1

变量类型就是数据库字段类型：int、bigint、char、varchar、date、time等

赋值

set 变量名=值;
set 变量名:=值;
select 字段名 into 变量名 from 表名;
1
2
3

5. 其它语法

• if

if 条件1 then
....
elseif 条件2 then
...
else
...
end if;
1
2
3
4
5
6
7

• 参数

create procedure 名称([in/out/inout 参数名 参数类型])
begin

end;
1
2
3
4

create procedure p2(in score int ,out result varchar(10))
begin
if score > 18 then
  set result := '优秀';
elseif score > 60 then 
  set result := '及格';
else 
  set result := '不及格';
end if;
end;

call p2(68,@result);
select @result;
1
2
3
4
5
6
7
8
9
10
11
12
13

• case

语法一

case case_value
   when when_value1 then statement_list1;
   .....
   else statement_list;
end case;
1
2
3
4
5

create procedure p3(in season int ,out result varchar(10))
begin
   case 
	   when season > 1 and season <= 3 then set result :="第一季度";
		 when season > 4 and season <= 6  then set result :="第二季度";
		 when season > 7 and season <= 9 then set result :="第三季度";
		 when season > 10 and season <= 12 then set result :="第四季度";
		 else set result :="月份不符合标准";
	 end case;
end;

call p3(12,@result);
select @result;
1
2
3
4
5
6
7
8
9
10
11
12
13

语法二

case
   when serch_condition1 then statement_list
   ......
   else statement_list
end case;
1
2
3
4
5

• while循环

while 条件 DO

end while;
1
2
3

• repeat循环

repeat 时有条件的循环控制语句，当满足条件的时候推出循环

repeat
   sql 逻辑
   until 条件
end repeat
1
2
3
4

create procedure p4(in n int)
begin
  declare total int default 0;
	
  repeat 
	   set total := total+n;
	   set n :=n -1;
	 until n<=0
	end repeat;
	select total as '累加和';
end;

call p4(14);
1
2
3
4
5
6
7
8
9
10
11
12
13

• loop循环

loop实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现死循环，loop可以配置以下两个语句使用:

• leave：配合循环使用，退出循环
• iterate：必须在循环中，作用时跳出当前循环剩下的语句，直接进入下一个循环

[begin_label:] loop

end loop [end_label];
leave label; --退出指定标记的循环体
iterate label; --直接进入下一次循环
1
2
3
4
5

CREATE DEFINER=`root`@`localhost` PROCEDURE `p5`(in n int)
begin
  declare total int default 0;
	sum: loop
	   if n<=0 then 
		      leave sum;
	   end if;
		 set total := total+n;
		 set n :=n-1;
	end loop sum;
  SELECT total;
end;
call p5(14);
1
2
3
4
5
6
7
8
9
10
11
12
13

• 游标

游标(CURSOR)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。游标的使用包括游标的声明、Open、Fetch和Close，其语法分别如下：

声明

declare 游标名称 cursor for 查询语句;
1

打开游标

open 游标名称;
1

获取游标记录

fetch 游标名称 into 变量 [,变量];
1

关闭游标

close 游标名称;
1

• 条件处理程序

条件处理程序(handler)可以用来在流程控制结构执行过程中遇到的问题时相应的处理步骤。具体的语法为:

declare handler_action handler for condition_value [,condition_value]... statement;

handler_cation
   continue: 继续执行当前程序
   exit: 终止执行当前程序
condition_value
   sqlstate sqlstate_value:状态码，如02000
   sqlwarning: 所有以01开头的sqlstate代码的简写
   not found: 所有以02开头的sqlstate代码的简写
   sqlexception：所有没有被sqlwarning或not found捕获的sqlstate代码的简写
1
2
3
4
5
6
7
8
9
10

create procedure p6(in uage int)
begin
   -- 普通变量要在游标之前声明
   declare u_id int;
	 declare u_name varchar(100);
	 declare u_age int;
   declare u_cursor cursor for select 用户ID,姓名,年龄 from 用户表 where 年龄 < uage; 
	 declare exit handler for SQLSTATE '02000' close u_cursor;
	 drop table if exists tb_user;
	 create table if not exists tb_user(
	    id int primary key auto_increment,
			user_id int not null,
			user_name varchar(100),
			user_age  int
	 );
	 open u_cursor;
	 -- 这里使用条件处理程序来解决游标遍历完后结束循环的问题
	 while true do
	    fetch u_cursor into u_id,u_name,u_age;
			insert into tb_user values(null,u_id,u_name,u_age);
	 end while;
end;

call p6(27);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

6. 存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是in类型，具体语法如下：

create function 存储函数名称([参数列表])
returns type [characteristic...]
begin 

   return ...,
end;
1
2
3
4
5
6

characteristic说明：

• deterministic：相同的输入参数总是产生相同的结果
• no sql：不包含sql语句
• reads sql date：包含读取数据的语句，但不包含写入数据的语句

-- 从1到n的累加
create function fun1(n int)
returns int deterministic
begin
  declare total int default 0;
	while n>0 do
	  set total :=total+n;
		set n :=n-1;
	end while;
	return total;
end;
1
2
3
4
5
6
7
8
9
10
11

select fun1(14);
1