一、什么是函数？函数和存储过程有什么区别？

1.1.函数

在MySQL中，函数（Function）是用来封装一系列SQL语句并返回单个值的数据库对象。函数通常用于执行一些计算或操作，并将结果作为返回值返回给调用者。

1.2.存储过程和函数的区别：

  • 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
  • 函数：有且仅有1 个返回，适合做处理数据后返回一个结果

二、创建函数

创建函数语法：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END

注意：

  1. 参数列表 包含两部分：参数名、参数类型
  2. 函数体：肯定会有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不报错，但不建议，函数体中多个SQL后面需要添加分号
  3. 函数体中仅有一句话，则可以省略begin end
  4. 使用 delimiter语句设置结束标记

三、函数调用

SELECT 函数名(参数列表)

四、案例

4.1.无参数有返回值

案例：返回公司的员工个数

DELIMITER //

# 创建函数
CREATE FUNCTION myf1() RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    #定义局部变量
    DECLARE c INT DEFAULT 0;
    SELECT COUNT(*) INTO c#赋值给变量
    FROM employees;
    # 返回值
    RETURN c;
END //

DELIMITER;

# 调用函数
SELECT myf1()

上面需要DETERMINISTIC、READS SQL DATA添加，否则会报错，这个报错是因为MySQL要求在创建函数时必须指定函数的特性，以确保函数的安全性和一致性。在函数声明中缺少了函数的特性声明，导致MySQL无法识别函数的特性，从而报错。在这种情况下，可以通过在函数声明中添加函数的特性来解决这个问题。

在你的函数声明中，可以添加DETERMINISTIC、NO SQL或READS SQL DATA中的一个来指定函数的特性。这些特性的含义如下：

  • DETERMINISTIC：表示函数是确定性的，即对于相同的输入参数，函数总是返回相同的结果。
  • NO SQL：表示函数不会访问或修改数据库中的数据。
  • READS SQL DATA：表示函数只会读取数据库中的数据，而不会修改数据。

4.2.有参数有返回值

案例1：根据员工名，返回它的工资

DELIMITER //

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
READS SQL DATA
BEGIN

    # 定义变量保存薪资
    DECLARE s INT DEFAULT 0;

    SELECT employees.salary INTO s #查询出来的值赋值给变量s
    FROM employees 
    WHERE employees.last_name= empName;
    
    # 返回值
    RETURN s;

END //

DELIMITER;


# 调用语句
SELECT myf2('De Haan')

案例2：根据部门名，返回该部门的平均工资

DELIMITER //

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
READS SQL DATA
BEGIN
    # 定义变量保存薪资
    DECLARE s INT DEFAULT 0;

    SELECT AVG(e.salary) INTO s #查询出来的值赋值给变量s
    FROM employees e INNER JOIN departments d
    ON e.department_id = d.department_id
    WHERE d.department_name = deptName # 根据部门名筛选
    GROUP BY d.department_id; # 分组
    # 返回值
    RETURN s;

END //

DELIMITER;

# 调用函数
SELECT myf3('Fin')

五、查看函数

可以查看函数的创建语法信息：

SHOW CREATE FUNCTION myf3;

如下：

MySQL函数_函数体

六、删除函数

删除函数和存储过程类似，使用FUNCTION，如下：

DROP FUNCTION myf3;