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

 2016年06月30日    1477     声明


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

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

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