mysql8.0存储函数

4、存储函数的使用

4.1、语法分析

学过的函数：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。

4.2、调用存储函数

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

4.3、代码举例

举例1：
创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。
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：SET GLOBAL log_bin_trust_function_creators = 1;

4.4、对比存储函数和存储过程

练习

#0.准备工作
USE test15_pro_func;
CREATE TABLE employees
AS
SELECT * FROM test.`employees`;
CREATE TABLE departments
AS
SELECT * FROM test.`departments`;

#无参有返回
#1.创建函数get_count(),返回公司的员工个数
DELIMITER//
CREATE FUNCTION get_count()
RETURNS INT 
BEGIN
    RETURN(SELECT COUNT(*) FROM employees);
END//
DELIMITER;
#调用
SELECT get_count();

#有参有返回
#2.创建函数ename_salary(),根据员工姓名，返回它的工资
DELIMITER//
CREATE FUNCTION ename_salary(emp_name VARCHAR(20))
RETURNS DOUBLE 
BEGIN
    RETURN(
        SELECT salary
        FROM employees
        WHERE last_name=emp_name );
END//
DELIMITER;
#调用
SELECT ename_salary('Abel');

#3.创建函数dept_sal(),根据部门名，返回该部门的平均工资
DELIMITER//
CREATE FUNCTION dept_sal(dept_name VARCHAR(20))
RETURNS DOUBLE
BEGIN
    RETURN(
        SELECT AVG(salary)
        FROM employeese JOIN departmentsd
        ON e.department_id=d.department_id
        WHERE d.department_name=dept_name
    );
END//
DELIMITER;
#调用
SELECT dept_sal('Marketing');

#4.创建函数add_float()，实现传入两个float，返回二者之和DELIMITER//
CREATE FUNCTION add_float(num1 FLOAT,num2 FLOAT)
RETURNS FLOAT
BEGIN
    RETURN(SELECT num1+num2);
END//
DELIMITER;
#调用
SET @num1:=1.2,@num2=3.2;
SELECT add_float(@num1,@num2);