写在前面

在前面学习变量的时候提到了存储过程，那么本篇就来学习MySQL中的存储过程。一般来说，当线上程序出现问题导致数据错误时，如果情况较为紧急，那么可以写一个存储来快速修复数据，然后再去修复程序，这在某些场景下还是有效的。但是对于Java开发人员来说，存储过程以及在程序中调用存储过程实现一些业务其实是不可取的操作。还有如果MySQL自带的函数无法满足我们日常的需求时，开发者可以自定义一些函数。

本文主要介绍MySQL中的存储过程以及自定义函数的使用。

数据准备

为了后续学习的必要，这里先准备了一张数据表book2，相应的建表语句如下所示：

use envythink;
drop table if exists t_user;
create table t_user(
  id int not null primary key comment '编号',
  age smallint unsigned not null comment '年龄',
  name varchar(16) not null comment '姓名'
)comment '用户表';

存储过程

存储过程概念

所谓的存储过程，其实是一组预编译好的SQL语句集合，也可以将其理解为批处理语句。

存储过程优点

使用存储过程可以带来如下优点：（1）提高代码的重用性；（2）简化操作；（3）减少编译次数；（4）减少和数据库服务器的连接次数，提高效率。

创建存储过程

创建存储过程的语法格式如下所示：

create procedure 存储过程名([参数模式] 参数名 参数类型)
begin
  存储过程体
end

在上面的语法格式中，参数模式有3种类型：in、out和inout。in模式，表示该参数可以作为输入，也就是该参数需要调用方传入值。out模式，表示该参数可以作为输出，也就是该参数可以作为返回值。inout模式，表示该参数既可以作为输入也可以作为输出，也就是该参数需要在调用的时候传入值，又可以作为返回值。

注意参数模式默认为IN。一个存储过程可以有多个输入参数、多个输出参数和多个输入输出参数。

调用存储过程

调用存储过程的语法格式如下所示：

1	call 存储过程名称(参数列表);

也就是说调用存储过程的关键字是call。

删除存储过程

删除存储过程的语法格式如下所示：

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

请注意，存储过程每次只能删除一个，无法批量删除。这一点和数据表是不同的，数据表可以批量删除。

修改存储过程

其实存储过程是无法修改的，因此所谓的修改存储过程，其实是先删除现有的存储过程，然后重新建立新的存储过程而已。

查看存储过程

查看存储过程的语法格式如下所示：

1	show create procedure 存储过程名称;

使用show子句可以查看创建存储过程的详细语句。

存储过程示例

无参列表

下面这个是无参列表的存储过程，相应的创建过程如下所示：

第一步，创建存储过程：

/*设置结束符为$*/
delimiter $

/*如果存储过程存在则删除*/
drop procedure if exists proc1;

/*创建proc1存储过程*/
create procedure proc1()
  begin
    insert into t_user values(1,30,'book');
    insert into t_user values(2,50,'movie');
  end $

/*将结束符置为;*/
delimiter ;

在前面我们就介绍了使用delimiter关键字来设置结束符，当MySQL在执行脚本遇到结束符的时候，会将结束符前面的所有语句作为一个整体来执行。这里的存储过程通常都是包含多个SQL的，且需要将其作为一个整体来运行，因此就需要使用到delimiter关键字。MySQL默认的结束符是分号。

很明显上面的proc1存储过程的作用就是向t_user表中插入两条数据。

第二步，调用存储过程：

1	call proc1();

第三步，查询t_user表来验证效果：

mysql> select * from t_user;
+----+-----+-------+
| id | age | name  |
+----+-----+-------+
|  1 |  30 | book  |
|  2 |  50 | movie |
+----+-----+-------+
2 rows in set (0.00 sec)

从执行结果可以看出，存储过程调用是成功的。

包含in参数模式的有参列表

下面这个是有参列表且包含in参数模式的存储过程，相应的创建过程如下所示：

第一步，创建存储过程：

/*设置结束符为$*/
delimiter $

/*如果存储过程存在则删除*/
drop procedure if exists proc2;

/*创建proc1存储过程*/
create procedure proc2(id int,age int,in name varchar(16))
  begin
    insert into t_user values(id,age,name);
  end $

/*将结束符置为;*/
delimiter ;

第二步，调用存储过程：

/*创建了3个自定义变量*/
select @id:=3,@age:=66,@name:='bike';
/*调用存储过程*/
call proc2(@id,@age,@name);

第三步，查询t_user表来验证效果：

mysql> select * from t_user;
+----+-----+-------+
| id | age | name  |
+----+-----+-------+
|  1 |  30 | book  |
|  2 |  50 | movie |
|  3 |  66 | bike  |
+----+-----+-------+
3 rows in set (0.00 sec)

从执行结果可以看出，存储过程调用是成功的。

包含in和out这两个参数模式的有参列表

下面这个是有参列表且包含in和out这两个参数模式的存储过程，相应的创建过程如下所示：

第一步，创建存储过程：

/*删除id为4的记录*/
delete a from t_user as a where a.id=4;

/*如果存储过程存在则删除*/
drop procedure if exists proc3;

/*设置结束符为$*/
delimiter $
/*创建proc3存储过程*/
create procedure proc3(id int,age int,in name varchar(16),out user_count int,out max_id int)
  begin
    insert into t_user values(id,age,name);
    /*查询出t_user表的记录，其中user_count存放所有记录数，max_id用来存储t_user表中的最大id值*/
    select count(*),max(id) into user_count,max_id from t_user;
  end $

/*将结束符置为;*/
delimiter ;

请注意，proc3存储过程中的前两个参数id和age前面没有指定参数模式，那么它就使用了默认的in参数模式。

第二步，调用存储过程：

/*创建了3个自定义变量*/
select @id:=4,@age:=77,@name:='lichee';
/*调用存储过程*/
call proc3(@id,@age,@name,@user_count,@max_id);

第三步，查询@user_count和@max_id参数来验证效果：

mysql> select @user_count,@max_id;
+-------------+---------+
| @user_count | @max_id |
+-------------+---------+
|           4 |       4 |
+-------------+---------+
1 row in set (0.00 sec)

包含inout参数模式的有参列表

下面这个是有参列表且包含inout参数模式的存储过程，相应的创建过程如下所示：

第一步，创建存储过程：

/*设置结束符为$*/
delimiter $

/*如果存储过程存在则删除*/
drop procedure if exists proc4;

/*创建proc4存储过程*/
create procedure proc4(inout a int,inout b int)
  begin
    set a = a*2;
    select b*2 into b;
  end $

/*将结束符置为;*/
delimiter ;

第二步，调用存储过程：

/*创建了2个自定义变量*/
set @a:=10,@b:=20;
/*调用存储过程*/
call proc4(@a,@b);

第三步，查询@a和@b参数来验证效果：

mysql> select @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|   20 |   40 |
+------+------+
1 row in set (0.00 sec)

可以看到，我们自定义了两个变量@a和@b作为入参，然后在存储过程内部进行了修改，又作为返回值。

上面介绍的4个示例，基本上能解决日常工作中的绝大多数问题。

函数

函数概念

函数和存储过程非常相似，它也是一组预编译好的SQL语句集合，类似于Java中的方法，但是它必须有返回值。

创建函数

创建函数的语法格式如下所示：

create function 函数名(参数名称 参数类型)
returns 返回值类型
begin
  函数体
end

注意其中的参数是可选的，但是返回值却是必须的。

调用函数

调用函数的语法格式如下所示：

1	select 函数名(实参列表);

删除函数

删除函数的语法格式如下所示：

1	drop function [if exists] 函数名;

查看函数

查看函数的语法格式如下所示：

1	show create function 函数名;

使用show子句可以查看创建函数的详细语句。

函数示例

无参函数

下面这个是无参函数，相应的创建过程如下所示：

第一步，创建函数：

/*如果函数存在则删除*/
drop function if exists fun1;

/*设置结束符为$*/
delimiter $

/*创建fun1函数*/
create function fun1()
  returns int
  begin
    declare max_id int default 0;
    select max(id) into max_id from t_user;
    return max_id;
  end $

/*将结束符置为;*/
delimiter ;

第二步，调用函数并查看执行结果：

mysql> select fun1();
+--------+
| fun1() |
+--------+
|      4 |
+--------+
1 row in set (0.04 sec)

从执行结果可以看出，函数的调用是成功的。

有参函数

下面这个是有参函数，相应的创建过程如下所示：

第一步，创建函数：

/*如果函数存在则删除*/
drop function if exists fun2;

/*设置结束符为$*/
delimiter $

/*创建fun2函数*/
create function fun2(v_name varchar(16))
  returns int
  begin
    declare r_id int;
    select id into r_id from t_user where name =v_name;
    return r_id;
  end $

/*将结束符置为;*/
delimiter ;

第二步，调用函数并查看执行结果：

mysql> select fun2(name) from t_user;
+------------+
| fun2(name) |
+------------+
|          1 |
|          2 |
|          3 |
|          4 |
+------------+
4 rows in set (0.10 sec)

一般来说，上面这两个函数示例基本上也能解决日常工作中可能遇到的问题，当遇到其他的复杂情况，到时候可以再来继续深入学习。

存储过程和函数对比

定义存储过程使用的关键字是procedure，返回值可以有多个，然后使用call来调用它，存储过程一般用于执行较为复杂的过程体、更新和创建等语句。

定义函数使用的关键字是function，返回值必须有一个，然后使用select来调用它，函数一般用于查询单个值并返回。

方面	存储过程	函数
关键字	procedure	function
调用关键字	call	select
返回值	可以有0个或者多个	必须有一个

文章总结

本文主要学习了MySQL中的存储过程和函数，并介绍了概念、创建、调用、删除、查看等操作，并通过实例来加深对上述操作的理解与使用，最后通过一张表来将存储过程和函数进行对比。