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