第五次作业

触发器1：更新商品表数量
delimiter //
create trigger reduce_goods_num
after insert on orders
for each row
begin
    update goods
    set num = num - new.onum
    where gid = new.gid;
end //
delimiter ;
触发器2：取消订单恢复商品数量
delimiter //
create trigger restore_goods_num
after delete on orders
for each row
begin
    update goods
    set num = num + old.onum
    where gid = old.gid;
end //
delimiter ;
触发器3：修改订单同步更新商品数量
delimiter //
create trigger update_goods_num
after update on orders
for each row
begin
    update goods
    set num = num + old.onum - new.onum
    where gid = new.gid;
end //
delimiter ;
2.存储过程
存储过程s1：提取emp_new表所有员工姓名和工资
delimiter //
create procedure s1()
begin
    select emp_name, emp_salary from emp_new;
end //
delimiter ;
存储过程s2：输入员工姓名后返回员工的年龄
delimiter //
create procedure s2(in emp_name varchar(50), out emp_age int)
begin
    select age into emp_age from employees where name = emp_name;
end //
delimiter ;
存储过程s3：传入部门号返回该部门的平均工资
delimiter //
create procedure s3(in dept_id int, out avg_salary decimal(8,2))
begin
    select avg(salary) into avg_salary from employees where dept_id = dept_id;
end //
delimiter ;