数据库第五次作业

1.触发器

建库建表并导入商品记录

创建触发器

触发器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 ;