MySQL的存储过程应用介绍

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);