【第十九部分】存储过程与存储函数

【第十九部分】存储过程与存储函数

---

---

19. 存储过程与存储函数

19.1 存储过程

定义: 就是一组经过预先编译的 SQL 语句的封装。

执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

与视图相比:相同的优点，清晰、安全，还可以减少网络传输量; 不同是视图是虚拟表,一般只是用来进行展示,不对底层数据进行直接的操作,而存储过程是程序化的 SQL,直接操作底层数据表,可以做更多复杂的数据出力

存储过程类似函数,将常用的SQL语句进行封装,便于下次的复用

优点:

1. 简化操作，提高了SQL语句的复用，减少了开发程序员的压力
2. 减少操作过程中的失误，提高效率
3. 减少网络传输量客户端不需要把所有的 SQL 语句通过网络发给服务器
4. 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

缺点：

1. 可移植性差，存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
2. 调试困难
3. 存储过程的版本管理很困难，比如数据表索引发生变化了，可能会导致存储过程失效。
4. 它不适合高并发的场景

19.2 创建、调用存储过程

存储过程的参数类型可以分为以下几种情况:

1. 没有参数
2. IN类型
3. OUT类型
4. IN和OUT类型
5. INOUT类型

• IN：当前参数为输入参数，也就是表示入参；

  存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。

• OUT：当前参数为输出参数，也就是表示出参；

  执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。

• INOUT：当前参数既可以为输入参数，也可以为输出参数。

传入或者输出参数最好先去查看所查询的表的结构,数据类型一一对应上,避免出现其他错误

关于在创建过程中指定对存储过程的约束条件

1. [NOT] DETERMINISTIC : DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。
2. ONTAINS SQL: 当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句
3. NO SQL：存储过程的子程序中不包含任何SQL语句
4. READS SQL DATA： 当前存储过程的子程序中包含读数据的SQL语句
5. MODIFIES SQL DATA: 当前存储过程的子程序中包含写数据的SQL语句
6. SQL SECURITY DEFINER :当前存储过程的创建者或者定义者才能执行当前存储过程
7. SQL SECURITY INVOKER : 当前存储过程的访问权限的用户能够执行当前存储过程
8. COMMENT ： 添加注释信息

语法

# DELIMITER 自定义设置结束标记符号 
# 为什么需要去设置结束标记符号,因为MySQL默认的语句结束符号为分号 ; 为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

DELIMITER //
CREATE PROCEDURE 存储过程名字(IN|OUT|INOUT 参数名 参数类型,参数名 参数类型 ...)
[约束条件]
BEGIN
	存储过程体;
END // 
DELIMITER ;  
# 最后将其恢复原来的结束标记符 DELIMITER ; 

19.2.1 不带参数

# 创建存储过程返回各个部门的平均工资
DELIMITER //
CREATE PROCEDURE dep_avg_salary()
BEGIN
		SELECT department_id, AVG(salary) "各部门平均工资"
		FROM employees
		WHERE department_id IS NOT NULL
		GROUP BY department_id;
END //
DELIMITER ;

# 调用存储过程
CALL dep_avg_salary()

19.2.2 IN 类型

# 查询输入员工id,查询他的名字和薪资
DELIMITER //
CREATE PROCEDURE e_info(IN emp_id INT)
BEGIN
	SELECT last_name,salary 
	FROM employees
	WHERE employee_id = emp_id;
END //
DELIMITER ;

# 调用存储过程
CALL e_info(100)

19.2.3 OUT类型

# 输出公司薪资最高的员工
DELIMITER //
CREATE PROCEDURE best_salary_emp(OUT e_name VARCHAR(25))
BEGIN 
	SELECT last_name INTO e_name
	FROM employees
	WHERE salary = (
		SELECT MAX(salary)
		FROM employees
	);
END //
DELIMITER ;

# OUT类型调用的方式有些不同,加一个@输出名
CALL best_salary_emp(@e_name)
SELECT @e_name

19.2.4 IN和OUT类型同时使用

# 输入指定部门,输出该部门下薪资最低的员工
DELIMITER //
CREATE PROCEDURE select_lowest_salary(IN dep_id INT,OUT e_name VARCHAR(25))
BEGIN 
	SELECT last_name INTO e_name
	FROM employees
	WHERE department_id = dep_id AND salary = (
		SELECT MIN(salary)
		FROM employees
		WHERE department_id = dep_id
	);
END //
DELIMITER ;

CALL select_lowest_salary(20,@e_name)
SELECT @e_name

19.2.5 INOUT类型

# 查询某个员工领导的姓名，并用INOUT参数,输入员工姓名，输出领导的姓名
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT emp_name VARCHAR(25))
BEGIN
	SELECT last_name INTO emp_name
	FROM employees
	WHERE employee_id = (
		SELECT manager_id
		FROM employees
		WHERE last_name = emp_name
	);		
END //
DELIMITER ;

# 调用inout模式的参数
# 先设置一个参数进行传入
SET @emp_name = 'Fay'
CALL show_mgr_name(@emp_name)
SELECT @emp_name

19.3 存储函数

1. FUNCTION中总是默认为IN参数

2. RETURNS 返回值类型 该语句表示函数返回数据的类型，RETURNS子句只能对FUNCTION做指定，对存储函数而言这是强制的。

3. 函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

4. 关于在创建过程中指定对存储函数的约束条件

   1. [NOT] DETERMINISTIC : DETERMINISTIC表示结果是确定的。每次执行存储函数时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。
   2. ONTAINS SQL: 当前存储函数的子程序包含SQL语句，但是并不包含读写数据的SQL语句
   3. NO SQL：存储函数的子程序中不包含任何SQL语句
   4. READS SQL DATA： 当前存储函数的子程序中包含读数据的SQL语句
   5. MODIFIES SQL DATA: 当前存储函数的子程序中包含写数据的SQL语句
   6. SQL SECURITY DEFINER :当前存储函数的创建者或者定义者才能执行当前存储函数
   7. SQL SECURITY INVOKER : 当前存储函数的访问权限的用户能够执行当前存储函数
   8. COMMENT ： 添加注释信息

19.4 创建、调用存储函数

# 语法
# 一定要有返回值
DELIMITER //
CREATE FUNCTION 函数名(参数名 参数类型,...) 
RETURNS 返回值类型
[约束条件]
BEGIN
	RETURN(函数体); #函数体中肯定有 RETURN 语句  
END //
DELIMITER ;

使用存储函数

首次创建存储函数失败的话需要加约束条件

# 创建存储函数total_department(),传入部门id，函数返回该部门的人数，返回类型为整型

DELIMITER //
CREATE FUNCTION total_department(d_id INT)
RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT '查询部门总人数'
BEGIN
	RETURN(
		SELECT COUNT(*) 
		FROM employees
		WHERE department_id = d_id
	);
END //
DELIMITER ;

SELECT total_department(80)

19.5 存储函数和存储过程区别

类型	关键字	调用语法	返回值	应用场景	特点
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个	一般用于更新	能够执行对表的操作（比如创建表，删除表等）和事务操作
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时	存储函数可以放在查询语句中使用，存储过程不行

19.6 存储过程和存储函数的查看、删除

19.6.1 查看

# 查看创建信息
1. SHOW CREATE {PROCEDURE | FUNCTION} \G  存储过程名或函数名

# 查看所有的存储过程或者存储函数的状态，可以用like过滤信息
2. SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'xxx']

# information_schema.Routines表中查看存储过程和函数的信息
3. SELECT * FROM information_schema.Routines
	 WHERE ROUTINE_NAME='存储过程或函数的名' 

19.6.2 删除

DROP {PROCEDURE | FUNCTION} 存储过程名 | 存储函数名;

---

总结

以上就是今天要讲的内容，希望对大家有所帮助！！！