函数、触发器、存储过程

1 存储过程与函数

MySQL 从5.0版本开始支持存储过程和函数，存储过程和函数可以将复杂的SQL逻辑封装在一起

1.1 存储过程概述

1.1.1 理解

含义： 存储过程的英文是 Stored Procedure。它的思想很简单，就是一组经过 预先编译的 SQL 语句的封装
执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行
好处：

1. 简化操作，提高 sql 语句的重用性
2. 减少操作过程中的失误，提高效率
3. 减少网络传输量
4. 减少 SQL 语句暴露在网络上的风险，也提高了数据查询的安全性
   和视图的对比：
   他和视图优点相同，清晰、安全，还可以减少网络传输量。但是视图是虚拟表，不会对底层数据进行操作，存储过程就是一个写好的SQL，可以直接对底层数据进行操作

1.1.2 分类

存储过程的参数类型可以是 IN、 OUT 和 INOUT。根据这点分类如何：

1. 没有参数（无参无返回）
2. 仅仅带 IN 类型（有参无返回）
3. 仅仅带 OUT 类型（无参有返回）
4. 既带 IN 又带 OUT （有参有返回）
5. 带 INOUT （有参有返回）

注意：IN、OUT 、INOUT 都可以在一个存储过程中带多个

1.2 创建存储过程

1.2.1 语法分析

语法：

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

类似于Java中的方法：

修饰符 返回类型 方法名(参数类型 参数名, ...){
	方法体;
}

• 需要设置新的结束标记

DELIMITER 新的结束标记

1.2.2 代码示例

示例：创建一个查询所有员工的函数 select_all_employees()

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

DELIMITER ;

1.3 调用存储过程

1.3.1 调用格式

存储过程有多种调用方法。存储过程必须使用 CALL 语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如： call dbname.procname

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

举例：

CALL select_all_employees();

格式：

1. 调用 in 模式的参数：

CALL sql('值');

2. 调用 out 模式的参数：

SET @name;
CALL sql(@name);
SELECT @name;

举例：

CREATE PROCEDURE
select_students_by_sex(IN gender VARCHAR(10))
BEGIN
	SELECT *
	FROM student
	where sex = gender;
END ;
#调用
CALL select_students_by_sex('女');

CREATE PROCEDURE
select_students_count_by_sex(IN gender VARCHAR(10), OUT number int(10))
BEGIN
	SELECT COUNT(*) INTO number
	FROM student
	where sex = gender;
END ;
#调用
CALL select_students_count_by_sex('女',@number);
SELECT @number;

3. 调用 inout模式的参数：

SET @name=值; 
CALL sp1(@name); 
SELECT @name;

1.3.2 如何调试

在 MySQL 中，存储过程不像普通的编程语言（比如 VC++、Java 等）那样有专门的集成开发环境。因此，你可以通过 SELECT 语句，把程序执行的中间结果查询出来，来调试一个 SQL 语句的正确性。调试成功之后，把 SELECT 语句后移到下一个 SQL 语句之后，再调试下一个 SQL 语句。这样 逐步推进 ，就可以完成对存储过程中所有操作的调试了。当然，你也可以把存储过程中的 SQL 语句复制出来，逐段单独调试。

1.4 存储函数的使用

1.4.1 语法分析

学过的函数：LENGTH、SUBSTR、CONCAT 等
语法格式：

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

1.4.2 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。区别在于存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的

SELECT 函数名(实参列表)

举例：

CREATE FUNCTION select_max_salary()
RETURNS double
BEGIN
	RETURN (SELECT MAX(salary) AS result FROM employees);
END ;

#调用
SELECT select_max_salary();

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有 0 个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时

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

1.5 关于存储过程使用的争议

1.5.1 优点

1. 存储过程可以一次编译多次使用
2. 可以减少开发工作量
3. 存储过程的安全性强
4. 可以减少网络传输量
5. 良好的封装性

1.5.2 缺点

1. 可移植性差。无法跨数据移植
2. 调试困难。没有很好的调试程序
3. 存储过程的版本管理很困难。
4. 它不适合高并发的场景。高并发场景需要减少数据库的压力，而存储过程会增加数据库压力。

2 触发器

在实际开发中，我们经常会遇到这样的情况：有 2 个或者多个相互关联的表，如 商品信息 和 库存信息 分别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。
这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用== 事务 ==包裹起来，确保这两个操作成为一个 原子操作 ，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很 容易忘记其中的一步，导致数据缺失。
这个时候，咱们可以使用触发器。你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数 据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。

2.1 触发器概述

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。
触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会 自动 激发触发器执行相应的操作。
当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

2.2 触发器的创建

2.2.1 创建触发器语法

创建触发器的语法结构是：

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

2.2.2 代码举例

创建一个插入触发器

CREATE TRIGGER employees_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
	INSERT INTO departments(department_id,department_name,manager_id,location_id)
	VALUES(280,'GAM',211,1700);
END ;

#向employees表插入数据
INSERT INTO
	employees(employee_id,first_name,last_name)
VALUES(207,'zs','ls');

2.3 查看、删除触发器

2.3.1 查看触发器

查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等

• 方式1：查看啊当前数据库的所有触发定义
  SHOW TRIGGERS
• 方式2：查看当前数据库中某个触发器的定义
  SHOW CREATE TRIGGER 触发器名
• 方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
• SELECT * FROM information_schema.TRIGGERS;

2.3.2 删除触发器

触发器也是数据库对象，删除触发器也用DROP 语句，语法如下：
DROP TRIGGER IF EXISTS 触发器名称;

2.4 触发器的优缺点

2.4.1 优点

1. 确保数据的完整性
2. 记录操作日志
3. 可以用在操作数据前，对数据进行合法性检查

2.4.2 缺点

1. 可读性差
2. 相关数据变更，可能会导致触发器出错

2.4.3 注意点

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

例如：基于子表员工表（t_employee）的DELETE语句定义了触发器t1，而子表的部门编号（did）字段定义了外键约束引用了父表部门表（t_department）的主键列部门编号（did），并且该外键加了“ON DELETE SET NULL”子句，那么如果此时删除父表部门表（t_department）在子表员工表（t_employee）有匹配记录的部门记录时，会引起子表员工表（t_employee）匹配记录的部门编号（did）修改为NULL， 但是此时不会激活触发器t1。只有直接对子表员工表（t_employee）执行DELETE语句时才会激活触发器t1。