MySQL5.触发器与存储过程

1. 建库

create database mydb16_trigger;
use mydb16_trigger;
show tables;

2. 建表

2.1 货物表

create table goods( 
gid char(8) primary key comment'商品编号', 
name varchar(10) comment'商品名', 
price decimal(8,2) comment'价格', 
num int comment'数量');

2.2 订单表

create table orders(
oid int primary key auto_increment comment'订单号', 
gid char(10) not null comment'商品编号', 
name varchar(10) comment'商品名', 
price decimal(8,2) comment'价格', 
onum int comment'订单数量', 
otime date comment'订单时间');

3. 插入数据

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

4. 触发器

4.1 orders_after_insert_trigger

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

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

insert into orders value(1,'B0001','小楷本',2.8,60,now());

4.2 orders_after_delete_trigger

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

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

delete from orders where gid='B0001';

4.3 orders_after_update_trigger

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

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;

insert into orders value(1,'B0001','小楷本',2.8,60,now());
update orders set onum=110 where gid='B0001';

5.存储过程

use mydb7_openlab;
select * from emp_new;
desc emp_new;

5.1

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

delimiter //

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

5.2

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

delimiter //

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 //
    
delimiter ;
# 调用
call s2('张三',@age);
select @age;
call s2('李四',@age);
select @age;
call s2('王五',@age);
select @age;

5.3

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

delimiter //

create procedure s3(in in_dept int,out out_avg_incoming float)
    begin
         select avg(incoming) into out_avg_incoming from emp_new where dept2=in_dept;
    end //
    
delimiter ;
# 调用
call s3(101,@avg_incoming);
select @avg_incoming;
call s3(102,@avg_incoming);
select @avg_incoming;
call s3(103,@avg_incoming);
select @avg_incoming;