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) |
+----------------+
| |
+----------------+
1 row in set (0.00 sec)
函数的调用
函数同样可以通过DROP语句删除。如,删除上面定义的函数:
DROP FUNCTION getName;
2.3 函数与存储过程的区别
功能不同:函数实现的功能针对性比较强,而存储过程实现的功能要复杂一此。存储过程,可以执行包括修改表等一系列数据库操作;而自定义函数不能用于执行一组修改全局数据库状态的操作。
返回值方式及类型不同:存储过程通过参数返回值,返回值类型可以是记录集;而函数只能返回值或者表对象。函数只能返回一个变量;而存储过程可以返回多个(通过多个返回参数实现)。
参数类型不同:存储过程的参数可以有IN、OUT、INOUT三种类型,而函数只能有IN类型。存储过程返回类型不是必须的,而函数需要返回类型,且函数体中必须包含一个有效的RETURN语句。
适用范围不同:存储过程一般是作为一个独立的部分来执行,而函数可以作为查询语句的一个部分来调用(如:SELECT中)。由于函数可以返回一个表对象,因此它可以在查询语句中FROM关键字的后面。 SQL语句中不可用存储过程,而可以使用函数。