数据库原理及应用

仅供参考，感谢指出错误

---

E4 基于Mysql的游标定义和应用

问题1：请写一个程序，根据输入的部门编号，得到所有员工的姓名和工资

用游标实现

BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cur_emp cursor for select ename,sal from emp where deptno=deptcode;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_emp;
REPEAT
FETCH cur_emp INTO empname,empsalary;
IF NOT done THEN
Select empname,empsalary;
END IF;
UNTIL done END REPEAT;
CLOSE cur_emp;
END

---

问题2:请写一个程序:根据输入的部门编号，得到该部门所有员工的平均工资

CREATE PROCEDURE `sp_avgsal`(
IN `deptcode` INT,
OUT `avgsalary` FLOAT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE countnum INT DEFAULT 0;
DECLARE sumsalary float DEFAULT 0;
DECLARE empsalary FLOAT default 0;
DECLARE done INT DEFAULT 0;
DECLARE cur_emp cursor for select sal from emp where deptno=deptcode;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_emp;
REPEAT
FETCH cur_emp INTO empsalary;
IF NOT done THEN
set sumsalary=sumsalary+empsalary;
set countnum=countnum+1;
END IF;
UNTIL done END REPEAT;
set avgsalary=sumsalary/countnum;

select deptcode,avgsalary;
CLOSE cur_emp;
END

---

问题3：请写一个程序:更改所有员工的津贴:如果员工的原津贴小于100;则改为200;如果空值则赋100元

用游标

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_update_comm`(
OUT `empcode` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE empcomm FLOAT DEFAULT 0;
declare all_comm cursor for select empno,comm from emp;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open all_comm;
repeat
fetch all_comm into empcode,empcomm;
IF (NOT done and empcomm<100) THEN
update emp set comm=200 where empno=empcode;
select empcode,200;
ELSEIF (NOT done and empcomm is null) THEN
update emp set comm=100 where empno=empcode;
select empcode,100;
END IF;
until done end repeat;
SELECT ename,comm FROM emp;
CLOSE all_comm;
END

---

E5 触发器的定义和应用

问题1：设计一个行触发器:当DEPT表中deptno列的值被更新时，EMP表中相应的deptno值也被修改

CREATE TRIGGER `dept_after_update` AFTER UPDATE ON `dept` FOR EACH ROW BEGIN
update emp set emp.deptno=TRIM(NEW.deptno) where emp.deptno=TRIM(OLD.deptno);
END

---

问题2：设计一个行触发器用于emp表的更新，当修改员工时候触发更新所在部门的工资总和

首先在dept表中添加一个sumsalary column

CREATE DEFINER=`root`@`localhost` TRIGGER `dept_after_update` AFTER UPDATE ON `emp` FOR EACH ROW 
BEGIN
UPDATE dept SET dept.sumsalary=dept.sumsalary+new.sal-old.sal WHERE dept.Deptno=old.deptno;
END

---

问题3：创建触发器:当emp表中删除一些记录时，将每个员工所在部门的最新人数写入dept表

先向dept表添加一个person column

CREATE DEFINER=`root`@`localhost` TRIGGER `dept_after_update` AFTER DELETE ON `emp` FOR EACH ROW BEGIN
update dept set dept.persons=(select count(*) from emp where emp.deptno=old.deptno) where dept.Deptno=old.deptno;
END