MySQL | 触发器的应用 | 存储过程的应用

目录

一.触发器

1.什么是触发器

2.触发器的应用

3.面试中关于触发器的几个问题

二.存储过程

1.什么是存储器

2.输入型参数 IN

 3.输出型参数OUT

4.输入输出型参数INOUT

---

一.触发器

1.什么是触发器

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。

触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如：您可以根据客户当前的帐户状态，控制是否允许插入新订单。

触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。

触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。

此外触发器是逻辑电路的基本单元电路，具有记忆功能，可用于二进制数据储存，记忆信息等。

2.触发器的应用

假设现在有一张表，程序人员要实时的统计表中的总数据量，但是表中的数据并不是一直不变的，随时会有新的数据被插入，或者旧的数据被删除，如果执行了N次插入或删除操作，我们就要遍历N次的表来获取数据的总数，这样的效率太低了，为了解决这一问题，我们先定义一个记录（相当于变量），这个记录的数值就是当前数据的总量，如果有新的数据被插入，那么这个记录的值就加1，如果有旧的数据被删除，那么这个记录的值就减1。具体的实现方法就是使用触发器。我们知道，触发器的工作原理就是，当事件到来时，触发器被触发并去执行其他操作。在这我们可以定义，当insert操作到来时，触发器被触发去给记录的值加1。当delete操作到来时，触发器被触发去给记录的值减1。

set @tmp = 0;//创建一个名为tmp值为0的变量

select @tmp;//查询tmp变量的值

delimiter $//将结束符修改为$

create trigger tir_1 //创建一个名为tir_1的触发器
after
insert
on test
for each row
begin
    set @tmp = @tmp+1;
end
$


/*这段代码的意思是，创建了一个名为tir_1的触发器，当在test这张表上
执行insert操作之后，触发器就会被触发，接着就会给tmp的值加一*/

delimiter ; //将结束符修改为;

3.面试中关于触发器的几个问题

（1）一张表上最多能建立几个触发器？

一张表上同类型的触发器最多允许出现一个，而触发时间分为两个，即before(触发前)和after(触发后)。而触发事件有三个，分别是insert，delete和update，所以一张表上最多能建立6个触发器。即：

before	after
insert	insert
delete	delete
update	update

（2）那些操作可以触发触发器?

insert，load（批量插入）和replace（replace的意思就是取代，代替。而执行replace操作时，其实是先执行了delete操作，接着又执行了insert操作）操作可以触发insert类型触发器。

delete类型触发器只能由delete操作触发。

update类型触发器只能由update操作触发。

二.存储过程

1.什么是存储器

存储过程就是特点操作的流程集合。

2.输入型参数 IN

set @tmp = 10;

select @tmp;

首先我们创建一个名为tmp的实参变量，并将他赋值为10。

 

接着执行以下语句

delimiter &

create procedure pro_1(IN tmp int)
begin
    select tmp;
    set tmp = 20;
    select tmp;
end
&

delimiter ;

call pro_1(@tmp);

 接着我们先将结束符修改为&，然后创建一个存储过程，它的参数为一个输入型的参数 tmp，进入存储过程后，我们先查看它的值，在将他（形参tmp）的值赋为20，然后再查看它的值，最后将结束符修改为;，然后我们使用call命令来调用这个存储过程。并给它传入实参tmp。具体结果如下：

的

执行完上面的步骤后，我们再查看一下实参tmp的值，结果如下：

select @tmp;

 

我们发现实参tmp的值并没有被修改，所以我们可以将上面的存储过程理解为一个函数，而输入型参数 IN 定义的形参tmp在接受外部实参时为值传递，所有在根本上实参tmp的值并没有被改变。

 3.输出型参数OUT

下面我们将tmp定义为输出型参数，即

delimiter &

create procedure pro_1(OUT tmp int)
begin
    select tmp;
    set tmp = 20;
    select tmp;
end
&

delimiter ;

call pro_1(@tmp);

接着我们再来查看一下实参tmp的值

select @tmp;

我们发现此时实参tmp的值也变为了20。而且我们发现在存储过程中第一次查看tmp的值时，打印的结果为NULL，所以在OUT定义的输出型参数中，只有在最后时才将形参数据的值输出到实参中。

4.输入输出型参数INOUT

下面我们将tmp定义为输入输出型参数，即

delimiter &

create procedure pro_1(INOUT tmp int)
begin
    select tmp;
    set tmp = 20;
    select tmp;
end
&

delimiter ;

call pro_1(@tmp);

select @tmp;

 这里的INOUT类型的输入输出型参数就相当于引用。