MySQL之触发器和存储过程

1、触发器

触发器简介

触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。

2、存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法，是SQL 语言层面的代码封装与重用。存储过程和函数的区别：函数必须有返回值，而存储过程没有。存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN

优点：

存储过程只在创建时进行编译；而SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行

速度简化复杂操作

结合事务一起封装复用性好安全性高

可指定存储过程的使用权

注意：并发量少的情况下很少使用存储过程，并发量高的情况下，为了提高效率，用存储过程比较多。

使用触发器和存储过程的实例

触发器

# 建立两个表:goods(商品表)、orders(订单表)

use mydb16_trigger;

# 建立goods表

create table goods (gid char(8) primary key,name varchar(10),price decimal(8,2),num int);

# 建立orders表

create table orders(

oid int primary key auto_increment,

gid char(10)not null,
name varchar(10),
price decimal(8,2),
onum int,

otime date

);

向商品表导入数据

insert into goods values(('A0001','橡皮',2.5,100)
('B0001','小楷本',2.8,210)，
('C0001','铅笔',1.2,120)
('D0001','计算器”28,20);

建立触发器，订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单出数量,并测试

# 建立触发器

mysql> create trigger orders_after_insert_trigger
-> after insert on orders for each row
-> update goods set num=num-new.onum
-> where gid=new.gid;

# 测试

向orders表中加入数据

insert into orders(gid,name,price,onum,otime) value('C0001','铅笔',1.2,80,now());

goods表中的数据会发生改变

select * from goods;

插入数据前：

插入数据后：

建立触发器，实现功能:客户取消订单，恢复商品表对应商品的数量

mysql> create trigger orders_after_delete_trigger
-> after delete on orders for each row
-> update goods set num=num+old.onum
-> where gid=old.gid;

建立触发器，实现功能:客户修改订单，商品表对应商品数量同步更新

mysql> create trigger orders_after_update_trigger
-> after update on orders for each row
-> update goods set num=num+(old.onum-new.onum)
-> where gid=old.gid;

存储过程

使用mydb7 openlab库

mysql> use mydb7_openlab

创建提取emp_new表所有员工姓名和工资的存储过程s1

mysql> delimiter //
mysql> create procedure s1()
-> begin
-> select name,incoming from emp_new;
-> end //

# 测试

mysql> delimiter ;
mysql> call s1();

创建存储过程s2，实现输入员工姓名后返回员工的年龄

mysql> delimiter $$

mysql> create procedure s2(in in_name varchar(11),out out_age int)
-> begin
-> select age into out_age from emp_new where name=in_name;
-> end $$

mysql> delimiter ;

mysql> call s2('张三',@age);

mysql> select @age;

创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资

mysql> delimiter //

mysql> create procedure s3(in in_dept int, out avg_sal float)
-> begin
-> select avg(incoming) into avg_sal from emp_new where dept2=in_dept;
-> end //

mysql> delimiter ;
mysql> call s3(101,@sal);

mysql> select @sal;