mysql中函数知识

mysql中函数知识

#函数
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

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

*/

#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END
/*

注意：
1.参数列表 包含两部分：
参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记

*/

#二、调用语法
SELECT 函数名(参数列表)

USE `myemployees`;

#------------------------------案例演示----------------------------
#1.无参有返回
#案例：返回公司的员工个数
DELIMITER $
CREATE FUNCTION empCount() RETURNS INT
BEGIN
    #注意：局部变量的作用域，仅仅在定义它的begin end块中有效
    #注意：局部变量必须写在begin end中的开头，即必须写在begin end中的第一句话
    
    #DECLARE employeesCount INT DEFAULT 0;#定义局部变量并赋值初始化
    
    DECLARE employeesCount INT;  #定义局部变量(注意：局部变量可以只声明，不初始化)
    SELECT COUNT(*) INTO employeesCount FROM employees; #给变量赋值
    RETURN employeesCount;
END$

#调用函数
SELECT empCount()$
#调用函数
SELECT empCount() AS 员工总人数$

#使用自定义函数
SELECT empCount(), last_name, email, salary FROM employees;

 

/*
--------------------------------------------------------------
注意：局部变量
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
局部变量的声明(要求指定数据类型，可以只声明，不初始化)
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;

--------------------------------------------------------------
注意：用户变量
作用域：针对于当前会话(连接)有效，作用域同于会话变量
应用在任何地方，也就是begin end里面或begin end外面
#注意：用户变量要求声明时必须初始化

--------------------------------------------------------------
*/

#2.有参有返回
#案例1：根据员工名，返回它的工资
DELIMITER $
CREATE FUNCTION getSalaryByEmpName(last_name VARCHAR(30)) RETURNS DOUBLE
BEGIN
    SET @sal = 0; #定义用户变量(用户变量要求声明时必须初始化)
    #select salary into @sal from employees where employees.last_name = last_name; #给变量赋值
    #担心查到的结果是多条记录，所以使用了LIMIT，只取1条记录
    SELECT salary INTO @sal FROM employees WHERE employees.last_name = last_name LIMIT 0, 1; #给变量赋值
    RETURN @sal;
END$

#调用函数,会报错(Result consisted of more than one row),原因是查询到的记录不止一条，即查到了多条记录
SELECT getSalaryByEmpName('K_ing')$
#调用函数
SELECT getSalaryByEmpName('Kochhar')$
#查到有2个人的last_name都叫K_ing
SELECT * FROM employees WHERE last_name = 'K_ing';

#
#案例2：根据部门名，返回该部门的平均工资
DELIMITER $
CREATE FUNCTION avg_dep_salary(department_name VARCHAR(50)) RETURNS DOUBLE
BEGIN
    DECLARE avg_dep_salary DOUBLE; #定义局部变量(注意：局部变量可以只声明，不初始化)
    #给局部变量赋值
    SELECT AVG(salary) INTO avg_dep_salary FROM employees emp
    INNER JOIN departments dep
    ON emp.department_id = dep.department_id
    WHERE dep.department_name = department_name;
    RETURN avg_dep_salary;
END$

#
SELECT * FROM departments;

#调用函数
SELECT avg_dep_salary('Mar')$
#调用函数
SELECT avg_dep_salary('Sal')$
#调用函数
SELECT avg_dep_salary('IT')$

#三、查看函数
SHOW CREATE FUNCTION avg_dep_salary;

#四、删除函数
DROP FUNCTION IF EXISTS avg_dep_salary;
DROP FUNCTION avg_dep_salary;

#案例
#一、创建函数，实现传入两个float，返回二者之和
DELIMITER $
CREATE FUNCTION getSum(number1 FLOAT(10,2), number2 FLOAT(10,2)) RETURNS FLOAT(10,2)
BEGIN
    DECLARE mySum FLOAT(10,2) DEFAULT 0; #定义一个局部变量并赋初始值
    SET mySum = number1 + number2;
    RETURN mySum;
END$

#调用函数
SELECT getSum(6.65, 2.31)$
SELECT getSum(26, 18)$