【MySQL基础】存储过程、存储函数、触发器和视图

一、存储过程

1.1 概述

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

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

优点

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

缺点

1. 可移植性差。
2. 调试困难
3. 存储过程的版本管理很困难
4. 它不适合高并发的场景

存储过程的参数类型可以是IN、OUT和INOUT。

1、没有参数（无参数无返回）

2、仅仅带 IN 类型（有参数无返回）

3、仅仅带 OUT 类型（无参数有返回）

4、既带 IN 又带 OUT（有参数有返回）

5、带 INOUT（有参数有返回）

1.2 使用

创建存储过程

语法

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...] 
BEGIN
	存储过程体 
END

characteristics 表示创建存储过程时指定的对存储过程的约束条件

LANGUAGE SQL 
| [NOT] DETERMINISTIC 
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
CONTAINS SQL：不包含读写数据的SQL语句
NO SQL：不包含任何SQL语句
READS SQL DATA：包含读数据的SQL语句
MODIFIES SQL DATA:包含写数据的SQL语句
| SQL SECURITY { DEFINER | INVOKER } 
执行当前存储过程的权限
DEFINER：只有当前存储过程的创建者或者定义者才能执行
INVOKER：拥有当前存储过程的访问权限的用户能够执行
| COMMENT 'string'：注释信息

调用存储过程

CALL 存储过程名(实参列表)

格式

#调用in模式的参数
CALL sp1('值');
#调用out模式的参数
SET @name; 
CALL sp1(@name); 
SELECT @name;
#调用inout模式的参数
SET @name=值; 
CALL sp1(@name); 
SELECT @name;

1.3 实例

# 实现累加运算，计算 1+2+…+n
DELIMITER // 
CREATE PROCEDURE `add_num`(IN n INT) 
BEGIN DECLARE i INT; 
DECLARE sum INT; 
	SET i = 1; 
	SET sum = 0; 
	WHILE i <= n DO 
		SET sum = sum + i; 
		SET i = i + 1; 
	END WHILE; 
	SELECT sum; 
	END // 
DELIMITER ;
# 调用
CALL add_num(50);

二、存储函数

2.1 概述

语法格式：

CREATE FUNCTION 函数名(参数名 参数类型,...) 
RETURNS 返回值类型 
[characteristics ...] 
BEGIN
	函数体 #函数体中肯定有 RETURN 语句 
END

说明：

1. FUNCTION中总是默认为IN参数
2. RETURNS type 语句表示函数返回数据的类型；RETURNS子句只能对FUNCTION做指定，而且函数体必须包含一个 RETURN value 语句
3. characteristic 创建函数时指定的对函数的约束

2.2 使用

SELECT 函数名(实参列表)

2.3 实例

三、触发器

3.1 概述

MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一
段程序。触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会 自动 激发触发器执行相应的操作。

3.2 使用

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 触发器执行的语句块;

说明：

• 表名 ：表示触发器监控的对象。
• BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
• INSERT|UPDATE|DELETE ：表示触发的事件。
• 触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块

查看、删除触发器

# 查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G
# 查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
# 从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息
SELECT * FROM information_schema.TRIGGERS;
# 删除触发器
DROP TRIGGER IF EXISTS 触发器名称;

优缺点
优点

• 触发器可以确保数据的完整性
• 触发器可以帮助我们记录操作日志。
• 触发器还可以用在操作数据前，对数据进行合法性检查。

缺点

• 触发器最大的一个问题就是可读性差。
• 相关数据的变更，可能会导致触发器出错。

注意：如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。

四、视图

4.1 概述

视图是一种虚拟表 ，是建立在已有表的基础上，本身是不具有数据的，占用很少的内存空间，向视图提供数据内容的语句为 SELECT 语句，可以将视图理解为存储起来的 SELECT 语句

4.2 使用

# 创建或修改视图
CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW 视图名称 [(字段列表)] 
AS 查询语句 
[WITH [CASCADED|LOCAL] CHECK OPTION]
# 查看视图的结构
DESC / DESCRIBE 视图名称;
# 查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;
# 修改视图
ALTER VIEW 视图名称 AS查询语句
# 删除视图
DROP VIEW IF EXISTS 视图名称;

优点

• 操作简单
• 减少数据冗余
• 数据安全
• 适应灵活多变的需求
• 能够分解复杂的查询逻辑

缺点
如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好 ，容易变成系统的潜在隐患