【数据库——MySQL】（16）游标和触发器习题及讲解

1. 题目

1.1 游标

1. 创建存储过程，利用游标依次显示某部门的所有员工的实际收入。(分别用使用 计数器 来循环和使用 标志变量 来控制循环两种方法实现)

2. 创建存储过程，将某部门的员工工资按工作年限进行调整，工作年限<3,提高 %5，3<=工作年限<5，提高 %10，工作年限>=5，提高 %15。

1.2 触发器

1. 创建触发器，当在 employees 表中插入一个员工信息时，如果员工的部门编号，department 表中没有，则取消插入操作。

2. 创建触发器，当在 employees 表中删除一条记录时，在 salary 表中删掉该员工的记录。

3. 创建触发器，当在 employees 表中修改员工编号时，在 salary 表中同时修改员工编号。

2. 解答

2.1 游标

1. 创建存储过程，利用游标依次显示某部门的所有员工的实际收入。(分别用使用 计数器 来循环和使用 标志变量 来控制循环两种方法实现)

   方法一：使用计数器来循环

   drop PROCEDURE if EXISTS p1;
   delimiter $
   create PROCEDURE p1(in dname char(20))
   begindeclare employee_id char(6);declare employee_name char(10);declare employee_salary float;declare salary_count int;		# 统计记录数declare i int default 1;# 设置游标declare c_salary CURSOR forselect employees.EmployeeID, employees.`Name`, salary.Income - salary.Outcome as '实际收入'from employees join departments on employees.DepartmentID = departments.DepartmentIDjoin salary on employees.EmployeeID = salary.EmployeeIDwhere departments.DepartmentName = dname;select count(*) into salary_countfrom (select employees.EmployeeID, employees.`Name`, salary.Income - salary.Outcome as '实际收入'from employees join departments on employees.DepartmentID = departments.DepartmentIDjoin salary on employees.EmployeeID = salary.EmployeeIDwhere departments.DepartmentName = dname)a;open c_salary;    # 打开游标while i <= salary_count doFETCH c_salary into employee_id, employee_name, employee_salary;		# 读取游标select employee_id, employee_name, employee_salary;		# 使用游标set i = i + 1;end while;close c_salary;		# 关闭游标end $
   delimiter ;call p1('广告部');
   

   

   方法二：使用控制循环

   drop PROCEDURE if EXISTS p1;
   delimiter $
   create PROCEDURE p1(in dname char(20))
   begindeclare employee_id char(6);declare employee_name char(10);declare employee_salary float;declare f int default 1;# 设置游标declare c_salary CURSOR forselect employees.EmployeeID, employees.`Name`, salary.Income - salary.Outcome as '实际收入'from employees join departments on employees.DepartmentID = departments.DepartmentIDjoin salary on employees.EmployeeID = salary.EmployeeIDwhere departments.DepartmentName = dname;# 错误处理declare exit handler for not foundset f = 0;open c_salary;    # 打开游标while f = 1 doFETCH c_salary into employee_id, employee_name, employee_salary;		# 读取游标select employee_id, employee_name, employee_salary;		# 使用游标end while;close c_salary;		# 关闭游标end $
   delimiter ;call p1('广告部');
   

   

2. 创建存储过程，将某部门的员工工资按工作年限进行调整，工作年限<3,提高 %5，3<=工作年限<5，提高 %10，工作年限>=5，提高 %15。

   drop PROCEDURE if EXISTS p2;
   delimiter $
   create PROCEDURE p2(in dname char(20))
   begindeclare employee_id char(6);declare employee_WorkYear TINYINT;declare add_income float;declare f int default 1;# 设置游标declare c_salary CURSOR forselect employees.EmployeeID, employees.WorkYearfrom employees join departments on employees.DepartmentID = departments.DepartmentIDjoin salary on employees.EmployeeID = salary.EmployeeIDwhere departments.DepartmentName = dname;# 错误处理declare exit handler for not foundset f = 0;open c_salary;    # 打开游标while f = 1 doFETCH c_salary into employee_id, employee_WorkYear;		# 读取游标if employee_WorkYear < 3 then set add_income = 0.05;elseif employee_WorkYear < 5 thenset add_income = 0.1;elseset add_income = 0.15;end if;update salaryset Income = Income + Income * add_incomewhere salary.EmployeeID = employee_id;end while;close c_salary;		# 关闭游标end $
   delimiter ;call p2('广告部');
   

   

   再次调用存储过程 p1 查看广告部员工实际收入，从而验证存储过程 p2 是否正确。

   注：因为在存储过程 p2 提高的是 收入，而不是 实际收入，即支出是不变的。所以 p2 的结果不是简单的通过 p1 的结果乘上相应提升率！

   

   比如 伍容华 的支出是 88.03，那么存储过程 p2 的结果是这么来的：

   $$(1494.59+88.03)\ast (1+0.1)=1740.882$$
   $$1740.88-88.03=1652.85$$

2.2 触发器

1. 创建触发器，当在 employees 表中插入一个员工信息时，如果员工的部门编号，department 表中没有，则取消插入操作。

   drop trigger if EXISTS tri_insertinfo;
   delimiter $
   create TRIGGER tri_insertinfo before insert on employees for each row
   begindeclare a int;select count(*) into afrom departmentswhere departments.DepartmentID = new.DepartmentID;if a = 0 then SIGNAL SQLSTATE '12345' set message_text = '部门不存在';end if;end$
   delimiter ;select * from employees;
   

   

   select * from departments;
   

   

   insert into employees values('999996', '小邓在森林', '本科', '2022-02-17', '1', 5, '武汉大学', '00000000', '6');
   

   

   insert into employees values('999995', '小邓在森林', '本科', '2022-02-17', '1', 5, '武汉大学', '00000000', '10');
   

   

   select * from employees;
   

   

2. 创建触发器，当在 employees 表中删除一条记录时，在 salary 表中删掉该员工的记录。

   drop trigger if EXISTS tri_deleteinfo;
   delimiter $
   create TRIGGER tri_deleteinfo after delete on employees for each row
   begindelete from salarywhere EmployeeID=old.EmployeeID;end$
   delimiter ;
   

   delete from employees where EmployeeID = 999996;
   select * from employees;
   select * from salary;
   

   

   注：因为没有在表 salary 中插入 小邓在森林 的数据，我们再来试一下删除 伍容华 的数据。

   delete from employees where EmployeeID = 010008;
   select * from employees;
   select * from salary;
   

   
   可以看见 伍容华 的数据已经被删除。

3. 创建触发器，当在 employees 表中修改员工编号时，在 salary 表中同时修改员工编号。

   drop trigger if EXISTS tri_updateinfo;
   delimiter $
   create TRIGGER tri_updateinfo after update on employees for each row
   beginupdate salaryset EmployeeID = new.EmployeeIDwhere EmployeeID = old.EmployeeID;
   end$
   delimiter ;
   

   我们修改 王林 的编号（将 000001 修改为 999999），原数据是：

   select * from employees;
   select * from salary;
   

   

   修改后结果：

   update employeesset EmployeeID = '999999'where employees.EmployeeID = '000001';select * from employees;
   select * from salary;
   

   

上一篇文章：【数据库——MySQL】（15）存储过程、存储函数和事务处理习题及讲解