mysql 事件 函数_MySQL 自定义函数和存储过程的使用

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

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语句中不可用存储过程，而可以使用函数。