【MySql】MySql存储过程与函数

存储过程与函数

存储过程没有返回值，函数有返回值

存储过程

存储过程就是一组预先编译好的SQL语句的封装，需要执行时客户端向服务器发送调用请求，服务器就会将这一系列预先存储好的SQL语句全部执行。

简单举例：存储过程的创建

DELIMITER意味将$设置为结束标识，这样就可以在存储过程中使用分号进行分隔了，但注意要在末尾改回来

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM employees;
END $

delimiter ;
1
2
3
4
5
6
7

调用存储过程：

CALL select_all_data();
1

求平均值的存储过程：

DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN
	SELECT AVG(salary) AS avg_salary FROM employees;
END //
DELIMITER ;

CALL avg_employee_salary();
1
2
3
4
5
6
7
8

求最大值的存储过程：

DELIMITER $
CREATE PROCEDURE show_max_salary()
BEGIN
	SELECT MAX(salary)
	FROM employees;
END $
DELIMITER ;

CALL show_max_salary()
1
2
3
4
5
6
7
8
9

带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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查询某个员工的薪资

# 查询某个员工的薪资（使用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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查询某个员工的薪资，传入员工名称，传出员工薪资

# 查询某个员工的薪资，传入员工名称，传出员工薪资
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

选中员工的领导，并将传入的形参赋值为领导的名称：

# 选中员工的领导，并将传入的形参赋值为领导的名称：
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

注意：在存储过程中进行的数据操作，一旦操作逻辑较为复杂时，难以进行排错

存储函数

存储函数就是指用户自定义的函数，这种函数必定有返回值。

创建一个存储函数用来返回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();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

创建一个存储函数，传入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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

传入部门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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

存储过程和存储函数的简单对比

存储过程更倾向于将查询所得到的结果赋值给某个参数，其更适用于更新修改的操作中，其可以有多个赋值操作

存储函数则会将结果作为返回值，其可以更加灵活的运用在各种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'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

存储过程、函数的修改与删除

注意存储过程与存储函数在创建好之后就不允许修改函数体，但我们可以对权限、注释等信息进行进一步修改

# 修改存储过程或存储函数的相关信息
ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT '最高工资'

# 删除
DROP FUNCTION IF EXISTS count_by_id;
1
2
3
4
5
6
7

对于存储过程和存储函数的使用争议

其优点

1. 可以提高SQL的执行效率：存储过程可以一次编译、多次使用，减少了SQL语句的编译次数，可以提高数据库调用的效率。
2. 减少开发的工作量：将SQL语句封装为存储过程或函数，可以重复调用方法，不需要编写重复代码，可以减少开发的工作量。
3. 安全性较强：在存储过程和存储函数中可以设置对用户的使用权限，以提高安全性。
4. 可以减少网络传输量：具体代码被封装到存储过程或存储函数中，每次只需要进行调用，有效减少网络传输量。
5. 良好的封装性：再进行较为复杂的数据库操作时，原本一条条的SQL可能会连接多次数据库，封装之后就只需要连接一次。

其缺点

1. 可移植性差：存储过程或存储函数不可以跨数据库移植，在发生数据库变化的情况时需要重新编写。
2. 调试困难（主要）：只有少数的数据库支持存储过程的调试，在开发和维护的阶段都会有极大的不便。
3. 版本管理困难：在一些其他情况发生时（例如数据库索引发生变化），会导致存储过程或存储函数失效，此时的版本问题就会十分麻烦
4. 不适合高并发场景：在分库分表的场景下，存储过程就会十分难以维护。