一、函数简介
和存储过程一样,函数也是一组预先编译好的SQL语句的集合,且其优缺点也和存储过程一样,在创建之后,可以多次调用,提高了代码的重用性,同时减少了SQL语句的编译次数和与服务器的连接次数,提高了效率。
和存储过程不同的是,存储过程可以有任意个返回值(包括0个),但函数必须有且仅有一个返回值。存储过程适合做批量的插入、更新、删除等,而函数适合做数据处理后返回一个结果。
二、函数的创建和调用
1、创建
语法:
DELIMITER $
CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型
BEGIN
函数体;
END $
DELIMITER ;
说明:
1️⃣函数的参数列表和存储过程稍有不同,函数的参数列表只有两部分:参数名和参数类型
2️⃣函数体和存储过程一样,是一组合法的SQL语句,但是函数体中必须包含一个RETURN语句,用于返回结果,该RETURN语句一般放在函数体的最后,当然不放在最后也不会报错,但是不建议这样
3️⃣若函数体只有一句,也可以省略BEGIN和END关键字
2、调用
函数的调用使用SELECT而不是用CALL:
SELECT 函数名(参数列表);
3、示例
①无参数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义一个局部变量,用以接收返回值
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c; #一定要记得RETURN
END $
DELIMITER ;
#调用
SELECT myf1();
②有参数
a、根据员工姓名返回其工资
DELIMITER $
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal = 0;#定义一个用户变量用以接收返回值,注意用户变量声明时必须初始化值
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;
RETURN @sal;
END $
DELIMITER ;
#调用
SELECT myf2('Hunold');
b、分局部门名称查询其平均工资
DELIMITER $
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE; #声明一个局部变量,局部变量声明时无需初始化值
SELECT AVG(salary) INTO sal #赋值
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = deptName;
RETURN sal; #返回结果
END $
DELIMITER ;
#调用
SELECT myf3('IT');
c、计算两个小数之和
DELIMITER $
CREATE FUNCTION myf4(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
RETURN num1 + num2; #直接返回,此时BEGIN和END可以省略
END $
DELIMITER ;
#调用
SELECT myf4(1.2,2.3);
三、函数的查看和删除
1、查看
SHOW CREATE FUNCTION 函数名;
2、删除:和存储过程一样,函数也不支持一次删除多个
DROP FUNCTION 函数名;
Tip
:和存储过程一样,函数也不支持修改