存储过程与函数
存储过程没有返回值，函数有返回值
存储过程
存储过程就是一组预先编译好的SQL语句的封装，需要执行时客户端向服务器发送调用请求，服务器就会将这一系列预先存储好的SQL语句全部执行。
简单举例：存储过程的创建
DELIMITER意味将$设置为结束标识，这样就可以在存储过程中使用分号进行分隔了，但注意要在末尾改回来
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM employees;
END $
delimiter ;
调用存储过程：
CALL select_all_data();
求平均值的存储过程：
DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN
SELECT AVG(salary) AS avg_salary FROM employees;
END //
DELIMITER ;
CALL avg_employee_salary();
求最大值的存储过程：
DELIMITER $
CREATE PROCEDURE show_max_salary()
BEGIN
SELECT MAX(salary)
FROM employees;
END $
DELIMITER ;
CALL show_max_salary()
带IN、OUT、INOUT的存储过程
查看最低薪资并将其输出
# 查询工资最低并将其保存到ms变量中并输出
DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms
FROM employees;
END $
DELIMITER ;
# 调用该函数
CALL show_min_salary(@temp);
# 调用变量
SELECT @temp;
查询某个员工的薪资
# 查询某个员工的薪资（使用IN作为形参传入值）
DELIMITER $
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
SELECT 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))
BEGIN
SELECT salary INTO empsalary
FROM employees
WHERE 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))
BEGIN
SELECT last_name INTO empname
FROM employees
WHERE employee_id = (
SELECT manager_id
FROM employees
WHERE 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代表这是一条读数据，默认情况下这个限制是必须添加的
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
# 真正的函数体
BEGIN
RETURN (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)
BEGIN
RETURN (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
BEGIN
RETURN (
SELECT COUNT(*)
FROM employees
WHERE department_id = dep_id
);
END //
DELIMITER ;
# 调用
SELECT count_by_id(100);
存储过程和存储函数的简单对比
存储过程更倾向于将查询所得到的结果赋值给某个参数，其更适用于更新修改的操作中，其可以有多个赋值操作
存储函数则会将结果作为返回值，其可以更加灵活的运用在各种SQL语句中，其返回值只能为一个，一般用于只有一个结果的查询操作
存储过程与存储函数的查看、修改、删除
存储过程、函数的查看
注意以下信息都可以在控制台界面进行查看，在语句最后直接加’/G’进行查看文章来源：https://www.toymoban.com/news/detail-706784.html
# 存储过程的查看创建信息
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'
存储过程、函数的修改与删除
注意存储过程与存储函数在创建好之后就不允许修改函数体，但我们可以对权限、注释等信息进行进一步修改文章来源地址https://www.toymoban.com/news/detail-706784.html
# 修改存储过程或存储函数的相关信息
ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT '最高工资'
# 删除
DROP FUNCTION IF EXISTS count_by_id;
对于存储过程和存储函数的使用争议
其优点
- 可以提高SQL的执行效率：存储过程可以一次编译、多次使用，减少了SQL语句的编译次数，可以提高数据库调用的效率。
- 减少开发的工作量：将SQL语句封装为存储过程或函数，可以重复调用方法，不需要编写重复代码，可以减少开发的工作量。
- 安全性较强：在存储过程和存储函数中可以设置对用户的使用权限，以提高安全性。
- 可以减少网络传输量：具体代码被封装到存储过程或存储函数中，每次只需要进行调用，有效减少网络传输量。
- 良好的封装性：再进行较为复杂的数据库操作时，原本一条条的SQL可能会连接多次数据库，封装之后就只需要连接一次。
其缺点
- 可移植性差：存储过程或存储函数不可以跨数据库移植，在发生数据库变化的情况时需要重新编写。
- 调试困难（主要）：只有少数的数据库支持存储过程的调试，在开发和维护的阶段都会有极大的不便。
- 版本管理困难：在一些其他情况发生时（例如数据库索引发生变化），会导致存储过程或存储函数失效，此时的版本问题就会十分麻烦
- 不适合高并发场景：在分库分表的场景下，存储过程就会十分难以维护。
到了这里，关于【MySql】MySql存储过程与函数的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
