存储过程与函数
存储过程没有返回值，函数有返回值
存储过程
存储过程就是一组预先编译好的SQL语句的封装，需要执行时客户端向服务器发送调用请求，服务器就会将这一系列预先存储好的SQL语句全部执行。
简单举例：存储过程的创建
DELIMITER意味将$设置为结束标识，这样就可以在存储过程中使用分号进行分隔了，但注意要在末尾改回来
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGINSELECT * FROM employees;
END $delimiter ;
调用存储过程：
CALL select_all_data();
求平均值的存储过程：
DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGINSELECT AVG(salary) AS avg_salary FROM employees;
END //
DELIMITER ;CALL avg_employee_salary();
求最大值的存储过程：
DELIMITER $
CREATE PROCEDURE show_max_salary()
BEGINSELECT MAX(salary)FROM employees;
END $
DELIMITER ;CALL show_max_salary()
带IN、OUT、INOUT的存储过程
查看最低薪资并将其输出
# 查询工资最低并将其保存到ms变量中并输出
DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGINSELECT MIN(salary) INTO msFROM employees;
END $
DELIMITER ;# 调用该函数
CALL show_min_salary(@temp);# 调用变量
SELECT @temp;
查询某个员工的薪资
# 查询某个员工的薪资（使用IN作为形参传入值）
DELIMITER $
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGINSELECT salary FROM employees WHERE last_name = empname;
END $
DELIMITER ;# 调用
CALL show_someone_salary('Abel');# 或者这样调用
SET @empname := 'Abel';
CALL show_someone_salary(@empname);
查询某个员工的薪资，传入员工名称，传出员工薪资
# 查询某个员工的薪资，传入员工名称，传出员工薪资
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname Varchar(20), OUT empsalary DECIMAL(10, 2))
BEGINSELECT salary INTO empsalaryFROM employeesWHERE last_name = empname;
END //
DELIMITER ;# 调用
SET @empname := 'Abel';
CALL show_someone_salary2(@empname, @empsalary);SELECT @empsalary;
选中员工的领导，并将传入的形参赋值为领导的名称：
# 选中员工的领导，并将传入的形参赋值为领导的名称：
DELIMITER $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGINSELECT last_name INTO empnameFROM employeesWHERE employee_id = (SELECT manager_idFROM employeesWHERE last_name = empname);END $
DELIMITER ;# 调用
SET @empname := 'Abel';
CALL show_mgr_name(@empname);SELECT @empname;
注意：在存储过程中进行的数据操作，一旦操作逻辑较为复杂时，难以进行排错
存储函数
存储函数就是指用户自定义的函数，这种函数必定有返回值。
创建一个存储函数用来返回Abel的邮箱
# 暂时改变分隔符
DELIMITER //
# 创建函数
CREATE FUNCTION email_by_name()
# 设置函数的返回值类型
RETURNS VARCHAR(25)
# 函数的一些限制，CONTAINS SQL意味着包含SQL语句，READS SQL DATA代表这是一条读数据，默认情况下这个限制是必须添加的DETERMINISTICCONTAINS SQLREADS SQL DATA
# 真正的函数体
BEGINRETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
# 将分隔符修改回分号
DELIMITER ;# 调用函数
SELECT email_by_name();
创建一个存储函数，传入emp_id并查询其email并返回
注意如果不添加函数特征就必须设置全局属性log_bin_trust_function_creators属性为1
# 保证函数的创建必定成功，不需要添加函数的特征（DETERMINISTIC）
SET GLOBAL log_bin_trust_function_creators = 1;DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGINRETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;# 调用
SELECT email_by_id(100);# 使用变量进行调用
SET @temp1 := 100;
SELECT email_by_id(@temp1);
传入部门id并返回部门的人数
# 创建
DELIMITER //
CREATE FUNCTION count_by_id(dep_id INT)
RETURNS INT
BEGINRETURN (SELECT COUNT(*)FROM employeesWHERE department_id = dep_id);
END //
DELIMITER ;# 调用
SELECT count_by_id(100);
存储过程和存储函数的简单对比
存储过程更倾向于将查询所得到的结果赋值给某个参数，其更适用于更新修改的操作中，其可以有多个赋值操作
存储函数则会将结果作为返回值，其可以更加灵活的运用在各种SQL语句中，其返回值只能为一个，一般用于只有一个结果的查询操作
存储过程与存储函数的查看、修改、删除
存储过程、函数的查看
注意以下信息都可以在控制台界面进行查看，在语句最后直接加’/G’进行查看
# 存储过程的查看创建信息
SHOW CREATE PROCEDURE avg_employee_salary;# 存储函数的查看创建信息
SHOW CREATE FUNCTION count_by_id;# 查看其状态信息
SHOW PROCEDURE STATUS;SHOW PROCEDURE STATUS LIKE 'avg_employee_salary';SHOW FUNCTION STATUS LIKE 'count_by_id';# 所有的存储过程与存储函数都会存储在information_schema.Routines中，对这个表进行查询也可以获取信息
SELECT *
FROM information_schema.Routines
WHERE ROUTINE_NAME = 'email_by_id'
存储过程、函数的修改与删除
注意存储过程与存储函数在创建好之后就不允许修改函数体，但我们可以对权限、注释等信息进行进一步修改
# 修改存储过程或存储函数的相关信息
ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT '最高工资'# 删除
DROP FUNCTION IF EXISTS count_by_id;
对于存储过程和存储函数的使用争议
其优点
- 可以提高SQL的执行效率：存储过程可以一次编译、多次使用，减少了SQL语句的编译次数，可以提高数据库调用的效率。
- 减少开发的工作量：将SQL语句封装为存储过程或函数，可以重复调用方法，不需要编写重复代码，可以减少开发的工作量。
- 安全性较强：在存储过程和存储函数中可以设置对用户的使用权限，以提高安全性。
- 可以减少网络传输量：具体代码被封装到存储过程或存储函数中，每次只需要进行调用，有效减少网络传输量。
- 良好的封装性：再进行较为复杂的数据库操作时，原本一条条的SQL可能会连接多次数据库，封装之后就只需要连接一次。
其缺点
- 可移植性差：存储过程或存储函数不可以跨数据库移植，在发生数据库变化的情况时需要重新编写。
- 调试困难（主要）：只有少数的数据库支持存储过程的调试，在开发和维护的阶段都会有极大的不便。
- 版本管理困难：在一些其他情况发生时（例如数据库索引发生变化），会导致存储过程或存储函数失效，此时的版本问题就会十分麻烦
- 不适合高并发场景：在分库分表的场景下，存储过程就会十分难以维护。
