第17篇：存储过程&自定义函数详解-Java充电社【公众号：Java充电社】

Mysql系列的目标是：通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。

这是Mysql系列第17篇。

环境：mysql5.7.25，cmd命令中进行演示。

**代码中被[]包含的表示可选，|符号分开的表示可选其一。**

## 需求背景介绍

线上程序有时候出现问题导致数据错误的时候，如果比较紧急，我们可以写一个存储来快速修复这块的数据，然后再去修复程序，这种方式我们用到过不少。

存储过程相对于java程序对于java开发来说，可能并不是太好维护以及阅读，所以不建议在程序中去调用存储过程做一些业务操作。

关于自定义函数这块，若mysql内部自带的一些函数无法满足我们的需求的时候，我们可以自己开发一些自定义函数来使用。

所以建议大家掌握mysql中存储过程和自定义函数这块的内容。

## 本文内容

- 详解存储过程的使用
- 详解自定义函数的使用

## 准备数据

```java
/*建库javacode2018*/
drop database if exists javacode2018;
create database javacode2018;

/*切换到javacode2018库*/
use javacode2018;

/*建表test1*/
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语句集合，理解成批处理语句。

好处：

- 提高代码的重用性
- 简化操作
- 减少编译次数并且减少和数据库服务器连接的次数，提高了效率。

### 创建存储过程

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

> 参数模式有3种：
>
> in：该参数可以作为输入，也就是该参数需要调用方传入值。
>
> out：该参数可以作为输出，也就是说该参数可以作为返回值。
>
> inout：该参数既可以作为输入也可以作为输出，也就是说该参数需要在调用的时候传入值，又可以作为返回值。
>
> 参数模式默认为IN。
>
> 一个存储过程可以有多个输入、多个输出、多个输入输出参数。

### 调用存储过程

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

> 注意：调用存储过程关键字是`call`。

### 删除存储过程

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

> 存储过程只能一个个删除，不能批量删除。
>
> if exists：表示存储过程存在的情况下删除。

### 修改存储过程

存储过程不能修改，若涉及到修改的，可以先删除，然后重建。

### 查看存储过程

```java
show create procedure 存储过程名称;
```

> 可以查看存储过程详细创建语句。

### 示例

#### 示例1：空参列表

创建存储过程

```java
/*设置结束符为$*/
DELIMITER $
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc1;
/*创建存储过程proc1*/
CREATE PROCEDURE proc1()
  BEGIN
    INSERT INTO t_user VALUES (1,30,'路人甲Java');
    INSERT INTO t_user VALUES (2,50,'刘德华');
  END $

/*将结束符置为;*/
DELIMITER ;
```

> delimiter用来设置结束符，当mysql执行脚本的时候，遇到结束符的时候，会把结束符前面的所有语句作为一个整体运行，存储过程中的脚本有多个sql，但是需要作为一个整体运行，所以此处用到了delimiter。
>
> mysql默认结束符是分号。
>
> 上面存储过程中向t_user表中插入了2条数据。

调用存储过程：

```
CALL proc1();
```

验证效果：

```java
mysql> select * from t_user;
+----+-----+---------------+
| id | age | name          |
+----+-----+---------------+
|  1 |  30 | 路人甲Java    |
|  2 |  50 | 刘德华        |
+----+-----+---------------+
2 rows in set (0.00 sec)
```

> 存储过程调用成功，test1表成功插入了2条数据。

#### 示例2：带in参数的存储过程

创建存储过程：

```java
/*设置结束符为$*/
DELIMITER $
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc2;
/*创建存储过程proc2*/
CREATE PROCEDURE proc2(id int,age int,in name varchar(16))
  BEGIN
    INSERT INTO t_user VALUES (id,age,name);
  END $

/*将结束符置为;*/
DELIMITER ;
```

调用存储过程：

```java
/*创建了3个自定义变量*/
SELECT @id:=3,@age:=56,@name:='张学友';
/*调用存储过程*/
CALL proc2(@id,@age,@name);
```

验证效果：

```java
mysql> select * from t_user;
+----+-----+---------------+
| id | age | name          |
+----+-----+---------------+
|  1 |  30 | 路人甲Java    |
|  2 |  50 | 刘德华        |
|  3 |  56 | 张学友        |
+----+-----+---------------+
3 rows in set (0.00 sec)
```

> 张学友插入成功。

#### 示例3：带out参数的存储过程

创建存储过程：

```java
delete a from t_user 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中前2个参数，没有指定参数模式，默认为in。

调用存储过程：

```java
/*创建了3个自定义变量*/
SELECT @id:=4,@age:=55,@name:='郭富城';
/*调用存储过程*/
CALL proc3(@id,@age,@name,@user_count,@max_id);
```

验证效果：

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

#### 示例4：带inout参数的存储过程

创建存储过程：

```java
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc4;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc4*/
CREATE PROCEDURE proc4(INOUT a int,INOUT b int)
  BEGIN
    SET a = a*2;
    select b*2 into b;
  END $

/*将结束符置为;*/
DELIMITER ;
```

调用存储过程：

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

验证效果：

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

> 上面的两个自定义变量@a、@b作为入参，然后在存储过程内部进行了修改，又作为了返回值。

#### 示例5：查看存储过程

## 函数

### 概念

一组预编译好的sql语句集合，理解成批处理语句。类似于java中的方法，但是必须有返回值。

### 创建函数

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

> 参数是可选的。
>
> 返回值是必须的。

### 调用函数

```java
select 函数名(实参列表);
```

### 删除函数

```java
drop function [if exists] 函数名;
```

### 查看函数详细

```java
show create function 函数名;
```

### 示例

#### 示例1：无参函数

创建函数：

```java
/*删除fun1*/
DROP FUNCTION IF EXISTS fun1;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
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 ;
```

调用看效果：

```java
mysql> SELECT fun1();
+--------+
| fun1() |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)
```

#### 示例2：有参函数

创建函数：

```java
/*删除函数*/
DROP FUNCTION IF EXISTS get_user_id;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION get_user_id(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 ;
```

运行看效果：

```java
mysql> SELECT get_user_id(name) from t_user;
+-------------------+
| get_user_id(name) |
+-------------------+
|                 1 |
|                 2 |
|                 3 |
|                 4 |
+-------------------+
4 rows in set (0.00 sec)
```

## 存储过程和函数的区别

存储过程的关键字为**procedure**，返回值可以有多个，调用时用**call**，**一般用于执行比较复杂的的过程体、更新、创建等语句**。

函数的关键字为**function**，**返回值必须有一个**，调用用**select**，一般用于查询单个值并返回。

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

<a style="display:none" target="_blank" href="https://mp.weixin.qq.com/s/_S1DD2JADnXvpexxaBwLLg" style="color:red; font-size:20px; font-weight:bold">继续收门徒，亲手带，月薪 4W 以下的可以来找我</a>

## 最新资料

1. <a href="https://mp.weixin.qq.com/s?__biz=MzkzOTI3Nzc0Mg==&mid=2247484964&idx=2&sn=c81bce2f26015ee0f9632ddc6c67df03&scene=21#wechat_redirect" target="_blank">尚硅谷 Java 学科全套教程（总 207.77GB）</a>
2. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484192&idx=1&sn=505f2faaa4cc911f553850667749bcbb&scene=21#wechat_redirect" target="_blank">2021 最新版 Java 微服务学习线路图 + 视频</a>
3. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484573&idx=1&sn=7f3d83892186c16c57bc0b99f03f1ffd&scene=21#wechat_redirect" target="_blank">阿里技术大佬整理的《Spring 学习笔记.pdf》</a>
4. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484544&idx=2&sn=c1dfe907cfaa5b9ae8e66fc247ccbe84&scene=21#wechat_redirect" target="_blank">阿里大佬的《MySQL 学习笔记高清.pdf》</a>
5. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485167&idx=1&sn=48d75c8e93e748235a3547f34921dfb7&scene=21#wechat_redirect" target="_blank">2021 版 java 高并发常见面试题汇总.pdf</a>
6. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485664&idx=1&sn=435f9f515a8f881642820d7790ad20ce&scene=21#wechat_redirect" target="_blank">Idea 快捷键大全.pdf</a>

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/1/2883e86e-3eff-404a-8943-0066e5e2b454.png)