学过的函数:LENGTH、SUBSTR、CONCAT等
语法格式:
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END
说明:
1、参数列表:指定参数为IN、OUT或INOUT只对PROCEDURE是合法的,FUNCTION中总是默认为IN参数。
2、RETURNS type 语句表示函数返回数据的类型;
RETURNS子句只能对FUNCTION做指定,对函数而言这是强制
的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value
语句。
3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同,这里不再赘述。
4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句,也可以省略BEGIN…END。
在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之,用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于,存储函数是用户自己定义
的,而内部函数是MySQL的开发者定义
的。
SELECT 函数名(实参列表)
举例1:
创建存储函数,名称为email_by_name(),参数定义为空,该函数查询Abel的email,并返回,数据类型为字符串型。
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;
SELECT email_by_name();
举例2:
创建存储函数,名称为email_by_id(),参数传入emp_id,该函数查询emp_id的email,并返回,数据类型为字符串型。
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;
SET @emp_id = 102;
SELECT email_by_id(102);
举例3:
创建存储函数count_by_id(),参数传入dept_id,该函数查询dept_id部门的员工人数,并返回,数据类型为整型。
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT '查询部门平均工资'
BEGIN
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //
DELIMITER ;
SET @dept_id = 50;
SELECT count_by_id(@dept_id);
注意:
若在创建存储函数中报错“you might want to use the less safe log_bin_trust_function_creators variable
”,有两种处理方法:
-
方式1:加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
-
方式2:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
对比存储函数和存储过程
关键字 调用语法 返回值 应用场景 存储过程 PROCEDURE CALL 存储过程() 理解为有0个或多个 一般用于更新 存储函数 FUNCTION SELECT 函数() 只能是一个 一般用于查询结果为一个值并返回时 - 此外,存储函数可以放在查询语句中使用,存储过程不行。反之,存储过程的功能更加强大,包括能够执行对表的操作(比如创建表,删除表等)和事务操作,这些功能是存储函数不具备的。