数据库视图、触发器、存储过程

一直以来总是 CRUD，只会操作数据库表了，其他的视图、触发器、存储过程忘完了，但因为生产事故被迫研究了一下，简单回顾一下概念吧。

视图

在看系统的权限管理部分时，发现它是从视图中查询数据的，通过将多个权限表的数据关联为视图，大大减少了查询时的 SQL 复杂度。但导致我找了半天找不到那个表，最后才发现是个视图?。

数据库视图（Database View）是一种虚拟的数据库对象，它是基于一个或多个表的查询结果集。视图并不实际存储数据，而是从一个或多个基本表中检索数据并提供一个虚拟表的形式进行访问。

视图可以看作是一个预定义的查询，通过定义视图，可以将复杂的查询逻辑封装为一个简单的、可重复使用的对象。视图提供了以下几个主要的优点：

简化查询：通过视图，可以隐藏底层数据表的复杂性，使查询更加简洁、易读和易维护。

数据安全性：视图可以限制用户对底层表的访问权限，只暴露需要的数据，增加数据的安全性。

数据独立性：当基础表结构发生变化时，视图可以保持不变，从而减少了对应用程序的影响。

逻辑重用：通过定义视图，可以将常用的查询逻辑封装起来，多个应用程序或查询可以共享同一个视图。

创建视图时，需要定义视图的名称、列名和查询语句。查询语句可以包括表之间的关联、条件过滤、聚合函数等。一旦视图创建完成，您可以像访问普通表一样对视图进行查询和操作，尽管视图不存储数据，但查询视图将根据基础表的数据提供结果。

总而言之，数据库视图是一个虚拟的表，通过定义查询语句来检索和展示基础表中的数据。它提供了简化查询、数据安全性、数据独立性和逻辑重用等优势，帮助提高数据库的灵活性和可维护性。

看一下视图的创建 SQL ：

create or replace
algorithm = UNDEFINED view database.syrole_syresource as
select
    a.role_resource_seq as role_resource_seq,
    a.seq as seq,
    a.role_seq as role_seq,
    a.resource_seq as resource_seq,
    b.syrole_id as syrole_id,
    b.syresource_id as syresource_id,
    b.del_flg as del_flg,
    b.created_user as created_user,
    b.created_time as created_time,
    b.updated_user as updated_user,
    b.updated_time as updated_time
from
    (database.role_resource_seq_relation a
join database.role_resource b)
where
    (a.seq = b.seq);

这句 SQL 从表 role_resource 和表 role_resource_seq_relation 中获取数据，组合成了一个视图。这个视图的作用就是将两个表的数据连接了起来，当需要查询的时候可以直接从视图里查询，省去了表 JOIN 的操作，简化了查询 SQL。

视图的本质就是一段查询 SQL，因此视图是动态变化的。如上面的表 role_resource 和表 role_resource_seq_relation 数据发生了改变，从这两个表里查询的数据自然也改变了，所以视图也会改变。从这个角度上来说，视图是只读的。

触发器

由于出现了生产事故，需要改生产环境数据库里的数据，改完后恢复正常了，但过一段时间加的数据又没了，人都给整晕了。最后只能通过加个触发器，当数据被删除时将数据写到一个日志表里面，记录一下删除的时间。虽然这样能提供的信息不多，但也不知道怎么搞了。

数据库触发器（Database Trigger）是一种特殊的数据库对象，与表相关联，并在表上的特定事件发生时自动执行一系列操作。触发器可以用于捕捉、验证或修改数据库中的数据变化，以满足特定的业务需求或执行特定的业务逻辑。

触发器通常与插入（INSERT）、更新（UPDATE）或删除（DELETE）等操作相关联。当与触发器关联的事件发生时，触发器的代码会被触发执行。触发器可以在数据被修改之前（BEFORE）或之后（AFTER）执行，具体取决于触发器的类型。

触发器可以执行各种操作，包括但不限于以下几种：

数据验证：触发器可以检查插入、更新或删除操作中的数据是否满足特定的条件或约束，并拒绝不合法的操作。

数据补充：触发器可以在插入或更新操作之前或之后，自动填充或修改某些数据字段，以确保数据的完整性和一致性。

数据记录：触发器可以记录数据的变化情况，将相关信息插入到其他表中，以便进行审计、日志记录或跟踪操作。

级联操作：触发器可以触发其他相关的数据库操作，如更新其他表中的数据、执行存储过程等。

数据约束：触发器可以用于实施额外的数据约束，超出数据库的基本约束，以满足更复杂的业务逻辑要求。

需要注意的是，触发器的设计和使用应谨慎，避免过度使用或导致性能问题。触发器的执行是自动的，可能对数据库的性能和资源消耗产生影响。因此，在设计触发器时，需要考虑其执行频率、代码效率以及对数据库性能的影响。

总之，数据库触发器是与表相关联的特殊对象，用于在特定的数据库操作事件发生时自动执行一系列操作。触发器可以用于数据验证、数据补充、数据记录、级联操作和数据约束等方面，以满足特定的业务需求和业务逻辑。

看一下具体的创建触发器的 SQL，比较简单：

CREATE TRIGGER trigger_name BEFORE DELETE ON table_name
FOR EACH ROW
BEGIN
    INSERT INTO zz_log_table (delete_id, match_key, vin, delete_time)
    VALUES (OLD.id, OLD.match_key, OLD.vin, NOW()));
END;

这个 SQL 在表 table_name 上创建了一个触发器，触发时机为 BEFORE，触发事件为 DELETE，对象为 EACH ROW，即这张表的任何数据被删除前，都会触发这个触发器，执行里面的 SQL，将要被删除的数据写入 zz_log_table 表中，同时记录了当前删除的时间。

一开始还想记录是什么语句删除了数据，这样还能到程序中找一下，但触发器是数据库内部行为，执行 SQL 算是外部行为，触发器没法获取到是什么 SQL 触发的，很可惜。

存储过程

想之前在ZK就已经看到存储过程了，但当时还看不懂，现在虽然看得懂一点了，但也写不出来，简单了解一下吧，说不定以后会用上。

数据库存储过程（Database Stored Procedure）是一组预编译的数据库操作语句，以及相关的逻辑和控制结构，作为一个单元存储在数据库中。存储过程允许在需要时通过调用它们来执行一系列数据库操作，而无需每次都编写完整的SQL语句。

存储过程是在数据库服务器上创建和存储的，它们可以由应用程序、其他存储过程或数据库触发器等触发和调用。存储过程通常用于封装复杂的业务逻辑，减少重复的代码，提高数据库的性能和可维护性。

存储过程可以执行以下几种操作：

数据查询：存储过程可以包含查询语句，用于检索和返回数据结果集。

数据修改：存储过程可以包含插入、更新和删除等操作，用于修改数据库中的数据。

流程控制：存储过程可以使用条件语句（如IF、CASE）和循环语句（如WHILE、FOR）来控制程序的流程和逻辑。

事务管理：存储过程可以包含事务控制语句（如BEGIN TRANSACTION、COMMIT、ROLLBACK），用于管理数据库事务的一致性和完整性。

异常处理：存储过程可以捕获和处理异常，通过使用TRY...CATCH块来处理错误和异常情况。

参数传递：存储过程可以接受输入参数和输出参数，用于向存储过程传递数据和获取结果。

总之，数据库存储过程是一组预编译的数据库操作语句和逻辑，存储在数据库中，通过调用来执行一系列数据库操作。存储过程可以封装复杂的业务逻辑，提高代码重用性和性能优化，并提供安全性和可维护性的好处。

简单看一下实例吧，当然也不是我写的：

CREATE DEFINER=`admin4jaguar`@`%` FUNCTION database_name.function_name(o_org_id VARCHAR(20), n_org_id VARCHAR(20), size INT, ttid INT)
	RETURNS int(11)
BEGIN
	CASE ttid
	WHEN 1 THEN
		UPDATE car_org_info SET car_org_id = INSERT(car_org_id,1,size,n_org_id) WHERE car_org_id like CONCAT(o_org_id,"%");
	WHEN 2 THEN
		UPDATE org_info SET org_id = INSERT(org_id,1,size,n_org_id) WHERE org_id like CONCAT(o_org_id,"%");
	END CASE;
RETURN 1;
END

这个存储过程根据参数去执行不同的 SQL，属于是非常好理解的了。简单来说，存储过程就是数据库里的带 SQL 的程序，且这个程序直接由数据库执行，可以封装一些较为复杂的业务流程，也减少了程序与数据库通信的开销。