MySQL第五次作业

初始值

1.触发器

要求1：

代码

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

测试结果

要求2：

代码

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;




delete from orders where gid='D0001';

测试结果

要求3：

代码

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;



insert into orders values(1,'A0001','橡皮',2.5,50,now());

测试结果

2.存储过程

初始值

要求1：

代码

mysql> delimiter $$
mysql> create procedure s1()
    -> begin
    ->     select name,incoming from emp_new;
    -> end $$
mysql> delimiter ;



mysql> call s1();

测试结果

要求2：

代码
 

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

mysql-> delimiter //

mysql> call s2('张三',@a);
Query OK, 1 row affected (0.00 sec)

测试结果：

要求3：

代码

mysql-> delimiter $$
mysql> create procedure s3(in dept int, out incoming1 int)
    -> begin
    ->   select avg(incoming) from emp_new where dept2=dept;
    -> end $$

mysql-> delimiter ;

测试结果