MySQL 自定义函数和存储过程的使用

MySQL支持自定义函数和存储过程，创建函数和存储过程分别使用CREATE PROCEDURE和CREATE FUNCTION，两者在语法结构上比较相似，但是它们的使用方法及使用场景有所有不同。

存储过程
- 1.1 语法结构
- 1.2 存储过程的使用
函数

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表示函数名。函数的定义与存储过程定义非常相似，只是以下两个参数略有不同：

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 函数与存储过程的区别

功能不同：函数实现的功能针对性比较强，而存储过程实现的功能要复杂一此。存储过程，可以执行包括修改表等一系列数据库操作；而自定义函数不能用于执行一组修改全局数据库状态的操作。
返回值方式及类型不同：存储过程通过参数返回值，返回值类型可以是记录集；而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个(通过多个返回参数实现)。
参数类型不同：存储过程的参数可以有IN、OUT、INOUT三种类型，而函数只能有IN类型。存储过程返回类型不是必须的，而函数需要返回类型，且函数体中必须包含一个有效的RETURN语句。
适用范围不同：存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用(如：SELECT中)。由于函数可以返回一个表对象，因此它可以在查询语句中FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。