[MySQL 自定义函数和存储过程的使用](https://itbilu.com/database/mysql/Nye1nX0Bb.html) MySQL支持自定义函数和存储过程，创建函数和存储过程分别使用`CREATE PROCEDURE`和`CREATE FUNCTION`，两者在语法结构上比较相似，但是它们的使用方法及使用场景有所有不同。 1. [存储过程](https://itbilu.com/database/mysql/Nye1nX0Bb.html#procedure) * [1.1 语法结构](https://itbilu.com/database/mysql/Nye1nX0Bb.html#proc-syntax) * [1.2 存储过程的使用](https://itbilu.com/database/mysql/Nye1nX0Bb.html#proc-example) 2. [函数](https://itbilu.com/database/mysql/Nye1nX0Bb.html#function) * [2.1 语法结构](https://itbilu.com/database/mysql/Nye1nX0Bb.html#func-syntax) * [2.2 自定义函数的使用](https://itbilu.com/database/mysql/Nye1nX0Bb.html#func-example) * [2.3 函数与存储过程的区别](https://itbilu.com/database/mysql/Nye1nX0Bb.html#dif-fun-proc) ### 1\. 存储过程 #### 1.1 语法结构 ~~~ CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body ~~~ 在上面语句中，包含以下参数： * `sp_name` - 存储过程名，可以是任何合法的MySQL标识符 * `[proc_parameter]` - 参数列表，可选。其形式为 ~~~ [ IN | OUT | INOUT ] param_name type ~~~ * `[ IN | OUT | INOUT ]` - 其中，`IN`表示输入参数，`OUT`表示输出参数，`INOUT`表示输入输出参数。默为输入参数 * `param_name` - 参数名 * `type` - 参数类型，可以是任何合法的MySQL数据类型 * `characteristic` - 存储过程特性描述，其取值如下： ~~~ characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } ~~~ * `COMMENT` - 注释信息 * `LANGUAGE` - 指明`routine_body`部分由SQL组成。注：`SQL`是LANGUAGE特性的唯一值 * `[NOT] DETERMINISTIC` - 指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的，即：每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，即：相同的输入可能得到不同的输出。默认为 NOT DETERMINISTIC。 * `CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA` - 指明子程序使用SQL语句的限制，默认为`CONTAINS SQL` * `CONTAINS SQL` - 表明子程序包含SQL语句，但是不包含读写数据的语句 * `NO SQL` - 表明子程序不包含SQL语句 * `READS SQL DATA` - 说明子程序包含读数据的语句 * `MODIFIES SQL DATA` - 子程序包含写数据的语句 * `SQL SECURITY { DEFINER | INVOKER } `- 执行权限。DEFINER表示定义者调用，INVOKER表示拥有调用权限都可以执行 * `routine_body` - 程序体，即：包含在`BEGIN...END`中的，要执行的SQL语句 #### 1.2 存储过程的使用 定义一个包含传出参数的存储过程： ~~~ delimiter // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t; END // delimiter ; ~~~ 在上例中，我们定义了一个名为`simpleproc`，这个存储过程会统计表`t`的数据，并将其插入`param1`参数传了。`delimiter`用于定义分隔符，MySQL中默认的分隔符是`;`，而存储过程中可能包含多条SQL语句，所以需要通过`delimiter`来重新指定分隔符。 存储过程的调用 存储过程定义后，可以使用`CALL`来调用存储过程。可以客户端单独调用，也可以在SQL语名或另一个存储过程中调用。 ~~~ mysql> CALL simpleproc(@a); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 3 | +------+ 1 row in set (0.00 sec) ~~~ 存储过程的删除 MySQL中的大多数自定义对象都可以通过`DROP`语句删除。如，删除上面存储过程： ~~~ DROP PROCEDURE simpleproc ~~~ ### 2\. 函数 #### 2.1 语法结构 ~~~ CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body ~~~ 创建一个函数，`sp_name`表示函数名。函数的定义与[存储过程定义](https://itbilu.com/database/mysql/Nye1nX0Bb.html#proc-syntax)非常相似，只是以下两个参数略有不同： * `func_parameter` - 函数参数列表，其形式如下： ~~~ param_name type ~~~ * `param_name` - 参数名 * `type` - 参数类型，可以是任何合法的MySQL数据类型 * `RETURNS type` - 表示函数的返回值类型 #### 2.2 自定义函数的使用 定义一个返回字符串的自定义函数： ~~~ DELIMITER // CREATE FUNCTION getName(id INT) RETURNS CHAR(50) RETURN (SELECT name FROM t WHERE id=id); // DELIMITER ; ~~~ 函数的调用 定义函数后，可以通过`SELECT`语句来调用函数： ~~~ SELECT getName(1); +----------------+ | getName(1) | +----------------+ | itbilu.com | +----------------+ 1 row in set (0.00 sec) ~~~ 函数的调用 函数同样可以通过`DROP`语句删除。如，删除上面定义的函数： ~~~ DROP FUNCTION getName; ~~~ #### 2.3 函数与存储过程的区别 1. 功能不同：函数实现的功能针对性比较强，而存储过程实现的功能要复杂一此。存储过程，可以执行包括修改表等一系列数据库操作；而自定义函数不能用于执行一组修改全局数据库状态的操作。 2. 返回值方式及类型不同：存储过程通过参数返回值，返回值类型可以是记录集；而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个(通过多个返回参数实现)。 3. 参数类型不同：存储过程的参数可以有`IN`、`OUT`、`INOUT`三种类型，而函数只能有`IN`类型。存储过程返回类型不是必须的，而函数需要返回类型，且函数体中必须包含一个有效的`RETURN`语句。 4. 适用范围不同：存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用(如：SELECT中)。由于函数可以返回一个表对象，因此它可以在查询语句中FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。