存储过程与函数20221027

一. 存储过程

• 1. 没有参数 (无参数无返回)
• 2. 仅仅带有IN类型 (有参数无返回)
• 3. 仅仅带有OUT类型 (无参数有返回)
• 4. 既带有IN又带有OUT (有参数，有返回)
• 5. 带INOUT (有参数有返回)

准备工作：

CREATE DATABASE dbtest15;

USE dbtest15;

CREATE TABLE employees
AS 
SELECT * 
FROM atguigudb.`employees`;

CREATE TABLE departments
AS 
SELECT * 
FROM atguigudb.`departments`;

SELECT * 
FROM employees;

SELECT * 
FROM departments;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

类型1:无返回值

• 举例2： 创建存储过程avg_employee_salary(),返回所有员工的平均工资
• 举例3：创建存储过程show_max_salary(),用来查看"emps"表的最高薪资

# 举例2： 创建存储过程avg_employee_salary(),返回所有员工的平均工资
DELIMITER $

CREATE PROCEDURE avg_employee_salary()
BEGIN 
	SELECT AVG(salary) FROM employees;
END $

DELIMITER;


CALL avg_employee_salary();


# 举例3：创建存储过程show_max_salary(),用来查看"emps"表的最高薪资

DELIMITER //

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

DELIMITER;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

类型2:带OUT

• 举例4：创建存储过程show_min_salary(),查看"emps"表的最低薪资值。并将最低薪资通过OUT参数"ms"输出

# 举例4：创建存储过程show_min_salary(),查看"emps"表的最低薪资值。并将最低薪资通过OUT参数"ms"输出

DESC employees;


DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN

	SELECT MIN(salary) INTO ms
	FROM employees;

END //

DELIMITER;



# 调用

CALL show_min_salary(@ms);

# 查看
SELECT @ms;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

类型3:带IN

• 举例5：创建存储过程show_someone_salary(),查看"emps"表的某一个员工的薪资待遇，并用IN参数empname输入员工姓名

# 举例5：创建存储过程show_someone_salary(),查看"emps"表的某一个员工的薪资待遇，并用IN参数empname输入员工姓名
DELIMITER //

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
	SELECT salary FROM employees
	WHERE last_name = empname;
END //

DELIMITER;

# 调用方式1
CALL show_someone_salary('Abel');

# 调用方式2
SET @empname := 'Abel';
CALL show_someone_salary(@empname);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

类型4:带IN和OUT

• 举例6：创建存储过程show_someone_salary2(),查看"emps"表的某一个员工的薪资，并用IN参数empname输入员工的姓名，用OUT参数empsalary输入员工的薪资

# 举例6：创建存储过程show_someone_salary2(),查看"emps"表的某一个员工的薪资，并用IN参数empname输入员工的姓名，用OUT参数empsalary输入员工的薪资

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
16
17
18

类型5:带INOUT

• 举例7：创建存储过程show_mgr_name().查询某个员工领导的姓名，并用INOUT参数"empname"输入员工姓名，输出领导姓名

# 举例7：创建存储过程show_mgr_name().查询某个员工领导的姓名，并用INOUT参数"empname"输入员工姓名，输出领导姓名
DESC employees;

DELIMITER //

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
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
21
22
23
24
25

二. 存储函数

• 举例1：创建存储函数，名称为email_by_name()参数定义为空，该函数查询Abel的email,并返回，数据类型为字符串型

# 举例1：创建存储函数，名称为email_by_name(0,参数定义为空，该函数查询Abel的email,并返回，数据类型为字符串型

DELIMITER //

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25) 
        DETERMINISTIC 
	CONTAINS SQL
	READS SQL DATA
BEGIN 
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //

DELIMITER ;


# 调用
SELECT email_by_name();

SELECT email, last_name FROM employees WHERE last_name = 'Abel';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

• 举例2：创建存储函数，名称定义为email_by_id(),参数传入emp_id，该函数查询emp_id,并返回，数据类型为字符串型。

# 创建存储函数，名称定义为email_by_id(),参数传入emp_id，该函数查询emp_id,并返回，数据类型为字符串型。

# 创建函数前，执行此语句，保证函数的创建可以成功(可以不用检验特征)
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(102);

SET @emp_id = 102;
SELECT email_by_id(@emp_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

• 举例3：创建存储函数count_by_id(),参数传入dept_id, 该函数查询dept_id部门的员工数，并返回，数据类型为整型。

# 举例3：创建存储函数count_by_id(),参数传入dept_id, 该函数查询dept_id部门的员工数，并返回，数据类型为整型。

DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
	
BEGIN
	RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //

DELIMITER;

# 调用
SELECT count_by_id(30);

SET @dept_id := 30;
SELECT count_by_id(@dept_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

存储过程和存储函数的总结：

此外，存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作和事务的操作，这些功能是存储函数不具备的。

三. 存储过程和函数的查看、修改、删除

3.1 查看

• MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可以直接从系统的information_scheme数据库中查询。这里介绍三种方法：

使用SHOW CREATE语句查看存储过程和函数的创建信息

# 使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE PROCEDURE show_mgr_name;

SHOW CREATE FUNCTION count_by_id;
1
2
3
4

使用SHOW CREATE语句查看存储过程和函数的状态信息

# 使用SHOW CREATE语句查看存储过程和函数的状态信息
SHOW PROCEDURE STATUS; 

SHOW PROCEDURE STATUS LIKE 'show_max_salary';

SHOW FUNCTION STATUS LIKE 'email_by_id';
1
2
3
4
5
6

从information_schema.Routines表中查看存储过程和函数信息

# 从information_schema.Routines表中查看存储过程和函数信息
SELECT * FROM information_schema.`ROUTINES`
WHERE ROUTINE_NAME = 'EMAIL_BY_ID';
1
2
3

3.2 修改

• 修改存储过程或者函数，不影响存储过程或者函数功能，只是修改相关特性。使用ALTER语句实现

ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT '查询最高工资';
1
2
3

3.3 删除

DROP FUNCTION IF EXIT count_by_id;
DROP PROCEDURE IF EXIT show_min_salary;
1
2