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;

 

本次实验就顺利完成了，触发器和存储过程的应用相信大家跟着实例做很会快掌握，大家一起加油努力，我们下期见！！！