数据库题目五（触发器与存储过程）

2301_80263909

已于 2025-03-12 21:43:44 修改

阅读量513

点赞数 25

文章标签：数据库 android adb

于 2025-02-09 22:43:43 首次发布

本文链接：https://blog.youkuaiyun.com/2301_80263909/article/details/145538043

版权

一、题目

在这里插入图片描述

二、运行过程

一、触发器

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

mysql> create table goods(
-> gid char(8) primary key,
-> name varchar(10),
-> price decimal(8,2),
-> num int);
mysql> create table orders(
-> oid int primary key auto_increment,
-> gid char(10) not null,
-> name varchar(10),
-> price decimal(8,2),
-> onum int,
-> otime date);

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

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

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

mysql> delimiter $mysql> 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$
mysql> delimiter ;
mysql> insert into orders (gid, name, price, onum, otime) values (‘a0001’, ‘橡皮’, 2.5, 10, curdate());

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

mysql> delimiter $mysql> 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$
mysql> delimiter ;
mysql> delete from orders where oid = 1;
Query OK, 1 row affected (0.00 sec)

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

mysql> delimiter $mysql> create trigger update_goods_num -> after update on orders -> for each row -> begin -> declare quantity_change int; -> set quantity_change = old.onum - new.onum; -> update goods -> set num = num + quantity_change -> where gid = new.gid; -> end$
mysql> delimiter ;
mysql> update orders set onum = 5 where oid = 1;

二、存储过程

1、使用mydb7 openlab库

mysql> use mydb7_openlab;

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

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

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

mysql> delimiter $mysql> create procedure s2(in emp_name varchar(50)) -> begin -> select timestampdiff(year, birthday, curdate()) as age -> from emp_new -> where name = emp_name; -> end$
mysql> delimiter ;
mysql> call s2(‘员工姓名’);

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

mysql> delimiter $mysql> create procedure s3(in dept_id int, out avg_salary decimal(8, 2)) -> begin -> select avg(salary) into avg_salary -> from emp_new -> where dept_id = dept_id; -> end$
mysql> delimiter ;
mysql> set @avg_salary = 0;
mysql> call s3(部门号, @avg_salary);
mysql> select @avg_salary;