本教程以MySQL8为主版本（内容兼顾MySQL5.7）。
所有MySQL文章的目录为：总目录
存储过程（Procedure）和函数（Function）是事先编译好，并存放在数据库服务端的一段SQL代码。使用存储过程和函数可以减少应用逻辑，也减少客户端与服务器端的交互次数，提高对数据库数据的执行效率。
两者的区别有：
- 存储过程无返回值，函数必须有返回值（或者说通过参数返回）
- 存储过程的参数可以使用IN、OUT、INOUT。而函数只能使用IN
创建存储过程或函数
创建存储过程（Procedure）或函数（Function）需要用户具有Create routine权限。删除和修改需要Alter routine权限，执行需要Execute权限。创建语法为：
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = user]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statementLANGUAGE SQL是指示下面body内容使用SQL语言编写
DETERMINISTIC表示相同的输入一定得到相同的输出。默认是NOT DETERMINISTIC
SQL SECURITY 以谁的权限来表示执行该存储过程或函数：DEFINER 以创建者的权限； INVOKER 以调用者的权限。默认是DEFINER。
body代码需要使用BEGIN ... END 包起来。 语句之间的分隔符为分号；
存储过程和函数的代码中，可以调用其他的存储过程和函数。
如果在mysql客户端中执行创建语句。由于body每个语句都有分号分隔符，会被mysql认为语句结束而立即执行该语句。因此需要设置delimiter修改mysql客户端语句的结束符号。例如：
存储过程创建举例：
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)函数创建举例：
mysql> CREATE FUNCTION hello (s CHAR(20))
-> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
-> $$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)可以看到存储过程和直接执行SQL语句的效果是相同的。但是存储过程的优点是，封装了处理过程，调用内不用关心内部细节。而且存储过程代码在数据库服务端，存储过程代码执行过程中不需要与客户端交互等开销。
查看存储过程和函数
查看存储过程和函数的状态
mysql> show procedure status like '%dorep%';
+------+----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | dorepeat | PROCEDURE | root@% | 2019-11-22 20:52:06 | 2019-11-22 20:52:06 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+------+----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
mysql> show FUNCTION status like '%hello%';
+------+-------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | hello | FUNCTION | root@% | 2019-11-22 20:55:36 | 2019-11-22 20:55:36 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+------+-------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
查询存储过程和函数的定义
mysql> show create procedure dorepeat;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| dorepeat | 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 DEFINER=`root`@`%` PROCEDURE `dorepeat`(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)mysql> show create FUNCTION hello;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| hello | 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 DEFINER=`root`@`%` FUNCTION `hello`(s CHAR(20)) RETURNS char(50) CHARSET utf8mb4
DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!') | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)还可以通过information_schema.routines查看
mysql> select * from information_schema.routines where routine_name like '%dor%' \G;
*************************** 1. row ***************************
SPECIFIC_NAME: dorepeat
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: test
ROUTINE_NAME: dorepeat
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2019-11-22 20:52:06
LAST_ALTERED: 2019-11-22 20:52:06
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
ROUTINE_COMMENT:
DEFINER: root@%
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ci
DATABASE_COLLATION: utf8mb4_general_ci
1 row in set (0.00 sec)
ERROR:
No query specified存储过程和函数的使用
MySQL存储过程和函数非常相似，因此简单说明如下：
- 变量定义。变量定义是放在BEGIN之后的，语法类似为：declare v_amt decimal(10,2)
- 变量赋值。使用SET 或者 select...... into 变量名语法
- 游标。游标类似存放多个数据集合变量。语法为declare cursor_name cursor for select ..... from ......
- 条件判断： IF.......THEN..... ELSEIF...... ELSE.......END IF
- 条件判断：CASE.... WHEN ..... THEN..... WHEN.....THEN.....ELSE.....END CASE
- 循环：LOOP...... END LOOP
- LEAVE：类似于java和C++中的break语句，常用从循环中退出
- ITERATE：类似于java和C++中的continue语句，终止当前循环，开始下一循环
- REPEAT循环：类似于java和C++中的while(){....}语句。 REPEAT......END REPEAT
- WHILE： WHILE DO...... END WHILE
建议：
存储过程和函数建议能不用就不用。许多银行和公司直接明文禁止使用存储过程。主要原因是：
- 一般应用逻辑已经使用Java、Python等语言开发，这时再使用存储过程和函数则相当于多了一种语言。不仅是代码分散，还需要人员团队技能增高，增加成本；
- 存储过程和函数没有生态链和扩展库。 比如Java/Python有完整的生态链和丰富的库。而存储过程和函数则缺少这些，开发稍微复杂的程序，犹如回到80年底的感觉，缺少现代的特性和组件
- 调试和问题定位比较麻烦。Java/Python有丰富的调试诊断工具，而存储过程和函数主要靠打日志，而且其日志想当简陋。 同时一段业务逻辑的运行日志，一部分在Java/Python中，一部分在存储过程和函数中。对于查问题的人来说，想当不爽。
因此实践经验中建议两条：①少用存储过程和函数；②如果必须用，则需要写特别简单（通过简单，来保证代码质量，提供稳定下，减少排查代码的痛苦）。
当然如果一个系统中（真有，国外的一个核心系统）主要逻辑都是存储和函数，相当于它最主要开发语言是。那就放开用吧。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
