存储例程是存储程序的一种类型, 本质上也是封装了一些可执行的语句, 只不过它的调用方式是: 需要手动去调用! 存储例程又可以分为存储函数和存储过程, 下边我们详细唠叨这两个家伙.
存储函数
创建存储函数
存储函数其实就是一种函数, 只不过在这个函数里可以执行命令语句而已. 函数的概念大家都应该不陌生, 它可以把处理某个问题的过程封装起来, 之后我们直接调用函数就可以去解决同样的问题了, 简单方便又环保. MySQL 中定义存储函数的语句如下:
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
函数体内容
END
从这里我们可以看出, 定义一个存储函数需要指定函数名称, 参数列表, 返回值类型以及函数体内容, 如果该函数不需要参数, 那参数列表可以被省略, 函数体内容可以包括一条或多条语句, 每条语句都要以分号; 结尾. 里边的制表符和换行仅仅是为了好看, 如果你觉得烦, 完全可以用空格代替! 光看定义理解的不深刻, 我们先写一个存储函数开开眼:
- MySQL> delimiter $
- MySQL> CREATE FUNCTION avg_score(s VARCHAR(100))
- -> RETURNS DOUBLE
- -> BEGIN
- -> RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
- -> END $
- Query OK, 0 rows affected (0.00 sec)
- MySQL> delimiter ;
我们定义了一个名叫 avg_score 的函数, 它接收一个 VARCHAR(100)类型的参数, 声明的返回值类型是 DOUBLE, 需要注意的是, 我们在 RETURN 语句后边写了一个 SELECT 语句, 表明这个函数的最后返回结果就是根据这个查询语句产生的, 也就是返回了指定科目的平均成绩.
存储函数的调用
我们自定义的函数和系统内置函数的使用方式是一样的, 都是在函数名后加小括号 () 表示函数调用, 有参数的函数调用可以把参数写到小括号里边. 函数调用可以作为查询对象或者搜索条件, 或者和别的操作数一起组成更复杂的表达式, 我们现在来调用一下刚刚写好的这个函数吧:
- MySQL> SELECT avg_score('母猪的产后护理');
- +------------------------------------+
- | avg_score('母猪的产后护理') |
- +------------------------------------+
- | 73 |
- +------------------------------------+
- 1 row in set (0.00 sec)
- MySQL> SELECT avg_score('论萨达姆的战争准备');
- +------------------------------------------+
- | avg_score('论萨达姆的战争准备') |
- +------------------------------------------+
- | 73.25 |
- +------------------------------------------+
- 1 row in set (0.00 sec)
- MySQL>
这样调用函数就比我们直接写两个又臭又长的查询语句简单多了.
查看和删除存储函数
如果我们想查看我们已经定义了多少个存储函数, 可以使用下边这个语句:
SHOW FUNCTION STATUS [LIKE 需要匹配的函数名]
由于这个命令得到的结果太多, 我们就不演示了哈, 自己试试.
如果我们想查看某个函数的具体定义, 可以使用这个语句:
SHOW CREATE FUNCTION 函数名
比如这样:
- MySQL> SHOW CREATE FUNCTION avg_score\G
- *************************** 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`(s VARCHAR(100)) RETURNS double
- BEGIN
- RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
- END
- character_set_client: utf8
- collation_connection: utf8_general_ci
- Database Collation: utf8_general_ci
- 1 row in set (0.01 sec)
- MySQL>
忽略结果中我们看不懂的那些东东, 后边都会详细唠叨的, 现在主要聚焦在这个函数的定义处.
如果想删除某个存储函数, 使用这个语句:
DROP FUNCTION 函数名
比如我们来删掉 avg_score 这个函数:
- MySQL> DROP FUNCTION avg_score;
- Query OK, 0 rows affected (0.00 sec)
- MySQL>
什么? 你以为到这里存储函数就唠叨完了么? 写完是不可能的, 这辈子都不可能写完的! 到现在为止我们只是勾勒出一个存储函数的大致轮廓, 下边我们来详细说一下 MySQL 定义函数体时支持的一些语句.
在函数体中定义变量
我们在前边说过在命令行 (黑框框) 中自定义变量的方式, 它可以不用声明就为变量赋值(也就是调用 SET 语句). 而在函数体中使用变量前必须先声明这个变量, 声明方式如下:
DECLARE 变量名 数据类型 [DEFAULT 默认值];
需要特别留心的是, 函数体中的变量名不允许加 @前缀, 这一点和黑框框中定义变量的方式是截然不同的, 特别注意一下. 在声明了这个变量之后, 才可以使用它:
- MySQL> delimiter $;
- MySQL> CREATE FUNCTION var_demo()
- -> RETURNS INT
- -> BEGIN
- -> DECLARE c INT;
- -> SET c = 5;
- -> RETURN c;
- -> END $
- Query OK, 0 rows affected (0.00 sec)
- MySQL> delimiter ;
我们定义了一个名叫 var_demo 而且不需要参数的函数, 我们在函数体中声明了一个名称为 c 的 INT 类型变量, 之后我们调用 SET 语句为这个变量赋值了整数 5, 并且把变量 c 当作函数结果返回, 我们调用一下这个函数:
- MySQL> select var_demo();
- +------------+
- | var_demo() |
- +------------+
- | 5 |
- +------------+
- 1 row in set (0.00 sec)
- MySQL>
如果我们不对声明的变量赋值的话, 它的默认值就是 NULL, 当然我们也可以通过 DEFAULT 子句来显式的指定变量的默认值, 比如这样:
- MySQL> delimiter $
- MySQL> CREATE FUNCTION var_default_demo()
- -> RETURNS INT
- -> BEGIN
- -> DECLARE c INT DEFAULT 1;
- -> RETURN c;
- -> END $
- Query OK, 0 rows affected (0.00 sec)
- MySQL> delimiter ;
- MySQL>
在新创建的这个 var_default_demo 函数中, 我们声明了一个变量 c, 并且指定了它的默认值为 1, 然后看一下函数的调用结果:
- MySQL> SELECT var_default_demo();
- +--------------------+
- | var_default_demo() |
- +--------------------+
- | 1 |
- +--------------------+
- 1 row in set (0.00 sec)
- MySQL>
得到的结果是 1, 说明了我们指定的变量默认值生效了! 另外, 特别需要注意一下我们可以将某个查询语句的结果赋值给变量的情况, 比如我们改写一下前边的 avg_score 函数:
- CREATE FUNCTION avg_score(s VARCHAR(100))
- RETURNS DOUBLE
- BEGIN
- DECLARE a DOUBLE;
- SET a = (SELECT AVG(score) FROM student_score WHERE subject = s);
- return a;
- END
我们先把一个查询语句的结果赋值给了变量 a, 然后再返回了这个变量.
参数的编写
在定义函数的时候, 可以指定多个参数, 每个参数都要指定对应的数据类型, 就像这样:
参数名 数据类型
比如我们上边编写的这个 avg_score 函数:
- CREATE FUNCTION avg_score(s VARCHAR(100))
- RETURNS DOUBLE
- BEGIN
- RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
- END
这个函数只需要一个类型为 VARCHAR(100)参数, 我们这里给这个参数起的名称是 s, 需要注意的是, 参数名不要和函数体语句中其他的变量名, 命令语句的标识符冲突, 比如如果把这个变量名命名为 subject, 它就与下边用到 WHERE 子句中的列名冲突了, 导致列名失效.
另外, 函数参数不可以指定默认值, 我们在调用函数的时候, 必须显式的指定所有的参数, 并且参数类型也一定要匹配, 比方说我们在调用函数 avg_score 时, 必须指定我们要查询的课程名, 不然会报错的:
- MySQL> select avg_score();
- ERROR 1318 (42000): Incorrect number of arguments for FUNCTION xiaohaizi.avg_score; expected 1, got 0
- MySQL>
判断语句的编写
像其他的编程语言一样, 在 MySQL 的函数体里也可以使用判断的语句, 语法格式如下:
IF 布尔表达式 THEN
处理语句
[ELSEIF 布尔表达式 THEN
处理语句]
[ELSE
处理语句]
END IF;
需要注意的是, 这里的处理语句可以是由多条语句构成的复合语句. 我们举个例子:
- MySQL> delimiter $
- MySQL> CREATE FUNCTION condition_demo(i INT)
- -> RETURNS VARCHAR(10)
- -> BEGIN
- -> DECLARE result VARCHAR(10);
- -> IF i = 1 THEN
- -> SET result = '结果是 1';
- -> ELSEIF i = 2 THEN
- -> SET result = '结果是 2';
- -> ELSEIF i = 3 THEN
- -> SET result = '结果是 3';
- -> ELSE
- -> SET result = '非法参数';
- -> END IF;
- -> RETURN result;
- -> END $
- Query OK, 0 rows affected (0.00 sec)
- MySQL> delimiter ;
- MySQL>
在我们定义的函数 condition_demo 中, 它接收一个 INT 类型的参数, 这个函数的处理逻辑如下:
如果这个参数的值是 1, 就把 result 变量的值设置为'结果是 1'.
否则如果这个这个参数的值是 2, 就把 result 变量的值设置为'结果是 2'.
否则如果这个这个参数的值是
3
, 就把 result 变量的值设置为'结果是 3'.
否则就把 result 变量的值设置为'非法参数'.
当然了, 我们举的这个例子还是比较白痴的啦, 当然了, 我们只是为了说明语法怎么用, 等于到更复杂一点的业务逻辑再往复杂了说哈. 我们现在调用一下这个函数:
- MySQL> SELECT condition_demo(2);
- +-------------------+
- | condition_demo(2) |
- +-------------------+
| 结果是 2 |
- +-------------------+
- 1 row in set (0.00 sec)
- MySQL> SELECT condition_demo(5);
- +-------------------+
- | condition_demo(5) |
- +-------------------+
| 非法参数 |
- +-------------------+
- 1 row in set (0.00 sec)
- MySQL>
循环语句的编写
除了判断语句, MySQL 还支持循环语句的编写, 不过有 3 种形式的循环语句, 我们一一道来:
WHILE 循环语句:
WHILE 布尔表达式 DO
循环语句
END WHILE;
这个语句的意思是: 如果满足给定的表达式, 则执行循环语句, 否则退出循环. 比如我们想定义一个从 1 到 n 这 n 个数的和(假设 n 大于 0), 可以这么写:
- MySQL> delimiter $
- MySQL> CREATE FUNCTION sum_all(n INT UNSIGNED)
- -> RETURNS INT
- -> BEGIN
- -> DECLARE result INT DEFAULT 0;
- -> DECLARE i INT DEFAULT 1;
- -> WHILE i <= n DO
- -> SET result = result + i;
- -> SET i = i + 1;
- -> END WHILE;
- -> RETURN result;
- -> END $
- Query OK, 0 rows affected (0.00 sec)
- MySQL> delimiter ;
- MySQL>
在函数 sum_all 中, 我们接收一个 INT UNSIGNED 类型的参数, 声明了两个 INT 类型的变量 i 和 result. 我们先测试一下这个函数:
- MySQL> SELECT sum_all(3);
- +------------+
- | sum_all(3) |
- +------------+
- | 6 |
- +------------+
- 1 row in set (0.00 sec)
- MySQL>
分析一下这个结果是怎么产生的, 初始的情况下 result 的值是 0,i 的值是 1, 给定的参数 n 的值是 3. 这个过程就是:
先判断 i <= n 是否成立, 显然成立, 进入循环体, 将 result 的值设置为 1(result + i),i 的值为 2(i + 1).
再判断 i <= n 是否成立, 显然成立, 进入循环体, 将 result 的值设置为
3
(result + i),i 的值为
3
(i + 1).
再判断 i <= n 是否成立, 显然成立, 进入循环体, 将 result 的值设置为
6
(result + i),i 的值为
4
(i + 1).
再判断 i <= n 是否成立, 显然不成立, 退出循环.
所以最后返回的 result 的值就是 6, 也就是 1,2,3 这三个数的和.
REPEAT 循环语句
REPEAT 循环和 WHILE 循环差不多, 只是形式上变了一下:
REPEAT
循环语句
UNTIL 布尔表达式 END REPEAT;
先执行循环语句, 再判断布尔表达式是否成立, 如果成立继续执行循环语句, 否则退出循环. 与 WHILE 循环不同的一点是: WHILE 循环先判断布尔表达式的值, 再执行循环语句, REPEAT 循环先执行循环语句, 再判断布尔表达式的值, 所以至少执行一次循环语句, 所以如果 sum_all 函数用 REPEAT 循环改写, 可以写成这样:
- CREATE FUNCTION sum_all(n INT UNSIGNED)
- RETURNS INT
- BEGIN
- DECLARE result INT DEFAULT 0;
- DECLARE i INT DEFAULT 1;
- REPEAT
- SET result = result + i;
- SET i = i + 1;
- UNTIL i <= n END REPEAT;
- RETURN result;
- END
LOOP 循环语句
这只是另一种形式的循环语句:
循环标记: LOOP
循环语句
LEAVE 循环标记;
END LOOP 循环标记;
在 LOOP 循环语句中, 比较特别的是需要我们设置循环标记来标识一个循环, 在循环体内依靠 LEAVE 循环标记的形式来中断某个循环, 比方说我们可以把 sum_all 函数改写成这样:
- CREATE FUNCTION sum_all(n INT UNSIGNED)
- RETURNS INT
- BEGIN
- DECLARE result INT DEFAULT 0;
- DECLARE i INT DEFAULT 1;
- flag:LOOP
- IF i> n THEN
- LEAVE flag;
- END IF;
- SET result = result + i;
- SET i = i + 1;
- END LOOP flag;
- RETURN result;
- END
其中的 flag 就是一个循环标记, 在循环体内判断 i> n 成立的时候就调用 LEAVE flag 来跳出这个循环.
注释的使用
不论什么时候, 对语句添加注释都是一件好事儿! 注释不仅仅是帮助别人理解我们写的语句是什么意思, 对于我们自己来说, 可能隔了几天之后再看自己写的语句就不知道是什么意思了. 在函数体内以 -- 开头的语句都算作注释语句, MySQL 服务器在执行语句的时候会忽略掉这些注释语句.
-- 函数名: sum_all
-- 参数: n = 从 1 累加到的数字
- CREATE FUNCTION sum_all(n INT UNSIGNED) COMMENT '求 1 到 n 这 n 个数的和'
- RETURNS INT
- BEGIN
-- 当前累加的和
DECLARE result INT DEFAULT 0;
-- 当前累加的数字
DECLARE i INT DEFAULT 1;
-- 若当前累加的数字不大于指定数字, 则继续执行循环
- WHILE i <= n DO
- SET result = result + i;
- SET i = i + 1;
- END WHILE;
-- 返回累加的和
- RETURN result;
- END
除了 -- 开头的语句表示注释, 我们还可以在函数参数后写 COMMENT 注释语句说明这个函数的作用.
小册
本系列专栏都是 MySQL 入门知识, 想看进阶知识可以到小册中查看: MySQL 是怎样运行的链接 . 小册的内容主要是从小白的角度出发, 用比较通俗的语言讲解关于 MySQL 进阶的一些核心概念, 比如记录, 索引, 页面, 表空间, 查询优化, 事务和锁等, 总共的字数大约是三四十万字, 配有上百幅原创插图. 主要是想降低普通程序员学习 MySQL 进阶的难度, 让学习曲线更平滑一点~
来源: https://juejin.im/post/5c9198f96fb9a070aa5ce939