mysql存储函数

---

存储函数

我们学过很多函数，使用这些函数可以对数据进行的各种处理操作，极大地提高用户对数据库的管理 效率。MySQL支持自定义函数，定义好之后，调用方式与调用MySQL预定义的系统函数一样，比如AVG、COUNT、SUBSTR等。

一、创建存储函数

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...] 
BEGIN
	函数体	#函数体中肯定有 RETURN 语句
END

• 不同于存储过程的是，存储函数的参数总是IN类型（不需要指定），没有OUT、INOUT。
• 函数体可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，可以省略BEGIN…END
• characteristic 创建函数时指定的对函数的约束。
  characteristics特性：
  • LANGUAGE SQL:说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
  • [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
  • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL语句的限制。
    • CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句
    • NO SQL表示当前存储过程的子程序中不包含任何SQL语句
    • READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句
    • MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句
    • 默认情况下，系统会指定为CONTAINS SQL
  • SQL SECURITY {DEFINER | INVOKER}：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
    • DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程
    • INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程
    • 如果没有设置相关的值，则MySQL默认指定值为DEFINER
  • COMMENT 'string'注释信息，可以用来描述存储过程
• RETURNS 指定返回类型且必须指定，函数体中也必须有RETURN value语句

CREATE FUNCTION func(e_name VARCHAR(10))
RETURNS DATE						#日期类型
DETERMINISTIC
BEGIN
	RETURN(select hiredate from emp where ename=e_name);
END;

注意
若在创建存储函数中报错“you might want to use the less safe log_bin_trust_function_creators variable”，必须要加上函数特性“[NOT] DETERMINISTIC”或者“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”。或者前面加上

SET GLOBAL log_bin_trust_function_creators = 1;

二、调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存 储函数与MySQL内部函数是一个性质的。区别在于，存储函数是 用户自己定义 的，而内部函数是MySQL的开发者定义的。

SELECT 函数名(实参列表) 

存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够 执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。