17｜第十七话：基础篇-MySQL之存储函数和存储过程简介

从视图开始，我们延续一个思想：知识是无限的，精力是有限的，如何合理安排有限的精力去甄别并学习无限知识中的重点，也是我们学习的一个注意点。本篇文章我们快速过一遍存储函数和存储过程相关知识，了解其概念和基本用法即可。

为了方便说明，仍然使用之前文章中所使用到的表结构和数据来作为演示。

CREATE TABLE t1 (m1 int, n1 char(1));
CREATE TABLE t2 (m2 int, n2 char(1));
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

一、存储程序

有时候为了完成一个常用的功能需要执行许多条语句，目前只能是一条一条输入去执行，针对这个痛点问题，MySQL提供了一种称之为存储程序的东东，这个所谓的存储程序可以封装一些语句，然后给用户提供一种简单的方式来调用这个存储程序，从而间接地执行这些语句。

根据调用方式的不同，我们可以把存储程序分为存储例程、触发器和事件这几种类型。其中，存储例程又可以被细分为存储函数和存储过程。

本篇文章核心关注存储函数和存储过程两个概念，触发器和事件放在下一篇文章中介绍，而在正式介绍存储函数和存储过程前，我们先来了解下“自定义变量”和“语句结束分隔符”两个前置概念。

二、自定义变量

如果具备基本的编程开发经验，相信大家对变量这个概念是比较清楚的，相对于变量这个概念就是常量，比如数字100、字符串”abc“，是固定不变的东西我们称之为常量。而更多的时候，我们会定义一个符号假设叫做”a“，a的类型是int，那么这个”a“的值可以是1，也可以是2，也可以是任意一个我们希望的整数值，我们就称”a“为变量。

MySQL中也可以定义变量，并且为它赋值，语法也极其简单，比如：

mysql> SET @a = 1;
Query OK, 0 rows affected (0.00 sec)

上边的语句就表明我们定义了一个称之为a的变量，并且把整数1赋值给了这个变量。注意，必须给自定义变量前加一个@符号。

变量定义好了，我们需要查询该变量的值：

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

在MySQL中，同一个变量也可以存储存储不同类型的值，比方说我们再把一个字符串值赋值给变量a：

mysql> SET @a = '哈哈哈';
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+-----------+
| @a        |
+-----------+
| 哈哈哈    |
+-----------+
1 row in set (0.00 sec)

除了把一个常量赋值给一个变量以外，我们还可以把一个变量赋值给另一个变量：

mysql> SET @b = @a;
Query OK, 0 rows affected (0.00 sec)

mysql> select @b;
+-----------+
| @b        |
+-----------+
| 哈哈哈    |
+-----------+
1 row in set (0.00 sec)

我们还可以将某个查询的结果赋值给一个变量，前提是这个查询的结果只有一个值：

mysql> SET @a = (SELECT m1 FROM t1 LIMIT 1);
Query OK, 0 rows affected (0.01 sec)

还有一种写法：

mysql> SELECT n1 FROM t1 LIMIT 1 INTO @b;
Query OK, 1 row affected (0.00 sec)

当需要同时给a和b用查询出来的两个字段赋值时，只能用INTO这种写法，比如我们把这条记录的m1列的值赋值到了变量a中，n1列的值赋值到了变量b中：

mysql> SELECT m1, n1 FROM t1 LIMIT 1 INTO @a, @b;
Query OK, 1 row affected (0.01 sec)

三、语句结束分隔符

一开始的时候，我们就介绍过，在MySQL客户端的交互界面处，当我们完成键盘输入并按下回车键时，MySQL客户端会检测我们输入的内容中是否包含;或者g或者G这三个符号之一，如果有的话，会把我们输入的内容发送到服务器。这样一来，如果我们想一次性给服务器发送多条的话，是比较麻烦的。

我们可以使用delimiter命令来自定义MySQL的检测语句输入结束的符号，也就是所谓的语句结束分隔符。

比如我们定义语句结束分隔符为$，也就是说之后MySQL客户端检测用户语句输入结束的符号为$，直到敲下$符号并回车，MySQL客户端才会将我们输入的内容提交到服务器，否则语句不会被理解提交到服务端，举个例子，直接将如下一坨粘贴到MySQL客户端上：

delimiter $
SELECT * FROM t1 LIMIT 1;
SELECT * FROM t2 LIMIT 1;
$

执行结果为：

mysql> delimiter $
mysql> SELECT * FROM t1 LIMIT 1;
    -> SELECT * FROM t2 LIMIT 1;
    -> $
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

当然了，我们可以定义语句结束分隔符为$，也可以定义为其他自定义的字符串，这里就不再赘述了，不过后续最好还是改回我们常用的分号;吧。

mysql> delimiter ;

四、存储函数基础

结束了前置知识铺垫后，快速开始存储函数基础学习。首先存储函数本质就是个函数，函数大家并不陌生，函数一般又被称为方法，是某个处理逻辑的封装，我们直接调用这个函数就可以完成某个逻辑的处理，而不用再关心其内部到底怎么实现，使得主逻辑代码看起来十分简洁。

创建存储函数

MySQL中创建存储函数的语法为：

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    函数体内容
END

对有编程经验的同学十分容易理解，定义一个函数，都需要定义函数名称、接收参数列表定义、返回值定义以及函数本身的执行内容。

我们来写一个简单的函数：

delimiter $
CREATE FUNCTION avg_score(cla VARCHAR(20))
RETURNS DOUBLE
BEGIN
    RETURN (select avg(score) from student_info where class=cla);
END $

这里定义了一个avg_score函数，接收一个VARCHAR(20)类型的参数，声明的返回值类型是DOUBLE，我们在RETURN后面写了一个SQL，是根据class（班级）来返回该class（班级）学生的平均分的SQL，执行下这个存储函数：

mysql> delimiter $
mysql> CREATE FUNCTION avg_score(cla VARCHAR(20))
    -> RETURNS DOUBLE
    -> BEGIN
    ->     RETURN (select avg(score) from student_info where class=cla);
    -> END $
Query OK, 0 rows affected (0.00 sec)

执行成功，相当于这个函数已经完成编写和注册，下面就是调用该函数。

调用存储函数

调用函数很简单，直接看如下例子：

mysql> select avg_score('一班');
+---------------------+
| avg_score('一班')   |
+---------------------+
|               71.25 |
+---------------------+
1 row in set (0.00 sec)

mysql> select avg_score('二班');
+---------------------+
| avg_score('二班')   |
+---------------------+
|        85.333333333 |
+---------------------+
1 row in set (0.01 sec)

是不是变的简洁了很多？

查看存储函数

如果我们想查看我们已经定义了多少个存储函数，可以使用下边这个语句：

SHOW FUNCTION STATUS [LIKE 需要匹配的函数名]

mysql> SHOW FUNCTION STATUS like 'avg_score'G
*************************** 1. row ***************************
                  Db: test_mysql
                Name: avg_score
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2022-11-13 17:41:44
             Created: 2022-11-13 17:41:44
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

查看某个存储函数具体是如何定义的：

SHOW CREATE FUNCTION 函数名

mysql> SHOW CREATE FUNCTION avg_scoreG
*************************** 1. row ***************************
            Function: avg_score
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `avg_score`(cla VARCHAR(20)) RETURNS double
BEGIN
    RETURN (select avg(score) from student_info where class=cla);
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

虽然展示出很多内容，但是我们只要聚焦于名叫Create Function的那部分信息，该部分信息展示了这个存储函数的定义语句是什么样的。

删除存储函数

DROP FUNCTION 函数名

mysql> DROP FUNCTION avg_score;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE FUNCTION avg_scoreG
ERROR 1305 (42000): FUNCTION avg_score does not exist

存储函数就说到这里，其实还有很多很多内容，比如可以结合自定义变量做更多的事情、比如使用判断、循环等语法，可以完成很多复杂逻辑的实现，对得起函数这个名号！感兴趣的同学可以去深入了解下。

五、存储过程基础

创建存储过程

说完了存储函数，再来聊聊存储过程，他们都是对某些语句的一个封装。存储函数侧重于执行这些语句并返回一个值，而存储过程更侧重于单纯的去执行这些语句。先看一下存储过程的定义语句：

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

与存储函数最直观的不同点就是，存储过程的定义不需要声明返回值类型。我们先定义一个存储过程看看：

delimiter $
CREATE PROCEDURE t1_operation(
m1_value INT,
n1_value CHAR(1)
)
BEGIN
    SELECT * FROM t1;
    INSERT INTO t1(m1, n1) VALUES(m1_value, n1_value);
    SELECT * FROM t1;
END $

我们建立了一个名叫t1_operation的存储过程，它接收两个参数，一个是INT类型的，一个是CHAR(1)类型的。这个存储过程做了3件事儿，一件是查询一下t1表中的数据，第二件是根据接收的参数来向t1表中插入一条语句，第三件是再次查询一下t1表中的数据。

调用存储过程

使用CALL语句来调用一个存储过程：

CALL 存储过程([参数列表]);

调用下t1_operation这个存储过程可以看到预期的效果：

mysql> CALL t1_operation(4, 'd');
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

查看存储过程

与存储函数类似，查看当前数据库中创建的存储过程都有哪些的语句：

SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称]

查看某个存储过程具体是如何定义的：

SHOW CREATE PROCEDURE 存储过程名称

mysql> SHOW CREATE PROCEDURE t1_operationG
*************************** 1. row ***************************
           Procedure: t1_operation
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `t1_operation`(
m1_value INT,
n1_value CHAR(1)
)
BEGIN
    SELECT * FROM t1;
    INSERT INTO t1(m1, n1) VALUES(m1_value, n1_value);
    SELECT * FROM t1;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

删除存储过程

DROP PROCEDURE 存储过程名称

mysql> DROP PROCEDURE t1_operation;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE PROCEDURE t1_operationG
ERROR 1305 (42000): PROCEDURE t1_operation does not exist

六、总结

存储过程和存储函数非常类似，他们最大的区别是：

存储函数在定义时需要显式用RETURNS语句标明返回的数据类型，而且在函数体中必须使用RETURN语句来显式指定返回的值，存储过程不需要。
存储函数直接在表达式中调用，而存储过程只能通过CALL语句来显式调用。

其实还有其他的区别，比如，存储过程支持返回一个值或多个值，是通过设置多个OUT参数或者INOUT参数来实现的，但是存储函数只能返回一个值，不过这一点并未在文章中体现，需要感兴趣的读者朋友继续深入学习存储函数和存储过程后才会体会出来，鉴于存储函数和存储过程使用也较少，本文的核心宗旨是带领读者朋友了解基础概念和使用方式。

此外，为了实现在存储过程中可以遍历select的结果做一些操作的话，可以使用游标，游标的作用是为了方便我们去访问这些有多条记录的结果集，感兴趣的读者朋友可以去了解下游标的使用方法。