数据库第五次作业

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

insert into orders value (1,'A0001','橡皮',2.5,10,now());

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

delete from orders where gid = 'A0001';

 create trigger my3 after update on orders for each row update goods set num = num-new.onum+old.onum where name = new.name;

insert into orders value (2,'A0001','橡皮',2.5,10,now());
update orders set onum = 30 where name = '橡皮';

emp_new表信息：

 

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

delimiter //
create procedure s2(in name1 varchar(11),out age1 int)
begin
    select age into age1 from emp_new where name =name1;

end //
delimiter ;
drop procedure s2;
call s2('荣七',@age1);
select @age1;

 

delimiter //
create procedure s3 (in d int,inout p_sal float)
begin
    select avg(emp_new.incoming) into p_sal from emp_new group by dept2 having d= dept2;
end //
delimiter ;
call s3(102,@sal);
select @sal;