MySQL第五次作业

一、触发器

        1、建立两个表:goods(商品表)、orders(订单表)

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

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 '订单时间'
    );

        2、在商品表中导入商品记录

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

     

        3、触发器设置

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

delimiter $$
create trigger after_order_insert
    after insert on orders
    for each row
    begin
        update goods
        set num = num - new.onum
        where gid = new.gid;
    end; $$
delimiter ;
select * from goods;
insert into orders values
(1, 'D0001', '计算器', '20', 10, '2024-07-25');
select * from goods;

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

delimiter $$ 
create trigger after_order_cancel
    -> before delete on orders
    -> for each row
    -> begin
    ->  update goods
    ->  set = num = num + old.onum
    ->  where gid = old.gid;
    -> end; $$
delimiter ;
select * from goods;
delete from orders where oid = 1;
select * from goods;

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

delimiter $$
create trigger updateorders
    after update on orders
    for each row
        begin
            declare old_num int;
            declare new_num int;
            select num into old_num from goods where gid = old.gid;
            set new_num = old_num - new.onum + old.onum;
            update goods
            set num = new_num
            where gid = old.gid;
        end; $$
 delimiter ;
select * from goods;
select * from orders;
select * from goods;
update orders set onum = 100 where oid = 2;
select * from goods;

二、存储过程

1、使用mydb7_openlab库

use mydb7_openlab;

2、创建提取emp_new表所有员工姓名和工资的存储过程s1

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

delimiter ;
call s1();

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

delimiter $$
create procedure s2(in emp_name varchar(20))
     begin
      declare emp_age int;
      select age into emp_age from emp_new
      where name = emp_name;
      if emp_age is not null then
              select emp_age as age;
      else
              select '未找到该员工' as error;
      end if;
     end; $$
delimiter ;
call s2('牛七');
call s2('牛八');

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

delimiter $$
create procedure s3(in dept_num int, out avg_incoming decimal(10, 2))
     begin
      select round(avg(incoming), 2) into avg_incoming
      from emp_new
      where dept2 = dept_num;
     end $$

delimiter ;
call s3(101, @average_incoming);
select @average_incoming;