存储过程

MySQL的存储过程（procedure），也叫存储程序。它是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。可以通过存储过程的名称对其进行调用。

存储过程跟触发器有点类似，都是一组SQL集，但存储过程是主动调用的，且功能比触发器更加强大，触发器是某种SQL行为后自动触发的。

应用场景：

• 当用多种语言编写应用程序，或应用程序在不同平台上运行且需要执行相同的数据库操作时。
• 当安全极为重要时。比如，银行对某些数据库操作使用存储过程。

优点

• 存储过程的sql语句已经预编译过了，因此再来调用它，运行的速度比较快。
• 存储过程可以包含流程控制语句、逻辑判断和数据库查询语句，隐藏了处理逻辑，实现了代码的封装。

1. 创建和调用存储过程

基本语法：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

存储过程可以带参数，参数的形式为：[ IN | OUT | INOUT ] param_name type

• IN 表示参数是输入参数，参数的值必须在调用存储过程时指定，如果在存储过程内部改变了该参数的值，并不会改变存储过程外部的该参数的值。默认是输入参数。
• OUT 表示参数是输出参数，参数的值不能在调用存储过程时指定，如果在存储过程内部改变了该参数的值，也会改变存储过程外部的该参数的值。
• INOUT 表示参数是输入输出参数，参数的值可以在调用存储过程时指定，如果在存储过程内部改变了该参数的值，也会改变存储过程外部的该参数的值。

示例1：不带参数的存储过程

mysql -uroot -p123456 test	
# 连接MySQL，并选择test数据库

\d //	
# 将sql语句的定界符改为//，默认的分割符是分号。 也可以写成 delimiter //

create procedure p1()
begin
set @i=1;
while @i<=5 do
select @i;
set @i=@i+1;
end while;
end
//

这样，就创建好了一个存储过程p1，调用方式如下：

\d ;		
# 将定界符还原为分号

call p1();	
# 调用p1，就会执行存储过程里的sql语句

示例2：带输入参数的存储过程

delimiter //

CREATE PROCEDURE p2(IN p_in int) 
begin
select p_in;
set p_in=5;
select p_in;
end
//

调用方式如下：

delimiter ;
set @p_in=2;
call p2(@p_in);

示例3：带输出参数的存储过程

delimiter //

CREATE PROCEDURE p3(OUT p_out int) 
begin
select p_out;
set p_out=5;
select p_out;
end
//

调用方式如下：

delimiter ;
SET @p_out=3; 
call p3(@p_out);

示例4：带输入输出参数的存储过程

delimiter //

CREATE PROCEDURE p4(INOUT p_inout int) 
begin
select p_inout;
set p_inout=5;
select p_inout;
end
//

调用方式如下：

delimiter ;
SET @p_inout=4; 
call p4(@p_inout);

说明：

• 存储过程可以带参数，也可以没有参数。如果声明了参数，无论是输入参数、输出参数还是输入输出参数，调用时就必须传递。多个参数之间用逗号分隔。
• 简单来说，输入参数就是供存储过程内部使用，就算在内部改变它的值，也不会影响外部；输出参数在存储过程内部不能获得它的初始值，但能改变它的值，并影响外部；输入输出参数可在存储过程内部获得它的初始值，也可改变它的值，并影响外部。

2. 变量

2.1 声明局部变量

语法： DECLARE var_name[,...] type [DEFAULT value]

声明局部变量时，如果没有DEFAULT子句，初始值为NULL。

局部变量的作用范围在它被声明的BEGIN ... END块内。

delimiter //

CREATE PROCEDURE p5()
begin

DECLARE p_a int unsigned default 1; 
DECLARE p_b varchar(20) default 'test'; 

select p_a;
select p_b;
end
//

2.2 变量赋值

给变量赋值，有两种方式：

• SET语句

  delimiter //
  
  CREATE PROCEDURE p6()
  begin
  
  DECLARE p_a int unsigned default 1; 
  DECLARE p_b varchar(20) default 'test'; 
  
  set p_a = 200;
  set p_b = 'hello';
  
  select p_a;
  select p_b;
  end
  //

• SELECT ... INTO语句

  delimiter //
  
  CREATE PROCEDURE p7()
  begin
  
  DECLARE p_a int unsigned default 1; 
  DECLARE p_b varchar(20) default 'test'; 
  
  SELECT id,name INTO p_a,p_b FROM test.t1 LIMIT 1;
  
  select p_a;
  select p_b;
  end
  //

2.3 用户变量

用户变量的形式为： @var_name

用户变量与当前的连接有关。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接中的所有用户变量将自动释放。

设置用户变量的一种方式是执行SET语句：

语法： SET @var_name = expr [, @var_name = expr] ...

对于SET，可以使用=或:=作为分配符。分配给每个变量的值可以为整数、小数、字符串或者NULL值。

SET @a=1,@b:=2.5;
SET @c:=@a+@b;

select @a,@b,@c;

也可以用SQL语句代替SET来为用户变量分配一个值。此时，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较运算符。

SET @t1=0, @t2=0, @t3=0;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

说明：

• 用户变量以@开头。
• 用户变量名不区分大小写。
• 不要滥用用户变量。

2.4 系统变量

系统变量主要是和服务器运行有关的变量，具体可参考MySQL手册。

3. 修改存储过程

基本语法：

ALTER PROCEDURE sp_name ...

4. 查看存储过程

查看所有的存储过程：

show procedure status;
或
select name from mysql.proc;

查看指定数据库里的存储过程：

show procedure status where db='test';
或
select name from mysql.proc where db='test';

查看存储过程的详细信息:

show create procedure p1;

5. 删除存储过程

drop procedure if exists p1;

6. 存储过程和存储函数的区别

• 存储过程和存储函数统称为存储例程（stored routine），两者的语法比较类似。
• 存储函数限制比较多，存储过程的限制相对就比较少。 存储过程实现的功能要复杂一点，而存储函数实现的功能针对性比较强。
• 存储过程使用 PROCEDURE 关键字，存储函数使用 FUNCTION 关键字。
• 存储过程的参数有三种类型（in|out|inout），存储函数的参数就一种类型。
• 存储过程的返回值可以有多个，存储函数的返回值只能有一个。
• 存储过程只能通过call语句进行调用，而存储函数可以直接在SQL语句中使用，和其他内置函数一样。

下面是一个创建和使用存储函数的例子：

delimiter //

create function diy_max(a int, b int) returns int
begin 
declare c int;
if a>=b then set c=a;
else set c=b;
end if;
return c;
end
//

delimiter ;
select diy_max(3,7);