MySQL实验6 存储过程与触发器

目的要求：
（1）掌握存储过程创建和调用的方法
（2）掌握MySQL中程序片段的组成
（3）掌握游标的使用方法
（4）掌握存储函数创建和调用的方法
（5）掌握触发器的使用方法
（6）掌握事件的创建和使用方法
实验准备：
（1）了解存储过程体中允许的SQL语句类型和参数的定义方法
（2）了解存储过程的调用方法
（3）了解存储函数的定义和调用方法
（4）了解触发器的作用和使用方法
（5）了解时间的作用和定义方法
实验内容：
1.存储过程
（1）创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。（20分）

```sql
delimiter @@
CREATE PROCEDURE emp_up_pro()
	BEGIN 
		DECLARE v_empno decimal(4,0);
		DECLARE v_sal DECIMAL(7,2);
		DECLARE v_comm DECIMAL(7,2);
		DECLARE flag boolean DEFAULT TRUE;
		DECLARE comm_cur CURSOR
			for SELECT empno,sal from emp_c;
		DECLARE CONTINUE HANDLER for not found
			set flag = FALSE;
		OPEN comm_cur;
		WHILE flag DO
			FETCH comm_cur into v_empno,v_sal;
			if v_sal<500 then set v_comm=v_sal*0.25;
			ELSEIF v_sal<1000 then set v_comm=v_sal*0.2;
			ELSEIF v_sal<3000 then set v_comm=v_sal*0.15;
			else set v_comm=v_sal*0.12;
			end if;
			UPDATE emp_c set comm=v_comm
				WHERE empno=v_empno;
		END WHILE;
		CLOSE comm_cur;
END@@

```sql
delimiter @@
CREATE PROCEDURE zyk()
BEGIN 
DECLARE flag boolean default true;
DECLARE mingzi char(10);
DECLARE shijian date;
DECLARE xm CURSOR
for SELECT ename,hiredate from emp2;
DECLARE CONTINUE HANDLER for not found
set flag=FALSE;
open xm;
WHILE flag DO
	fetch xm into mingzi,shijian;
	if year(now())-year(shijian)>6 then
	update emp2 set job='manager' WHERE ename=mingzi;
	end if;
END WHILE;
close xm;
end @@

call zyk()

（2）创建存储过程，使用游标计算本科及以上学历的员工在总员工数中所占的比例。（20分）

delimiter @@
create procedure zyk1()
begin
declare flag boolean default true;
declare a int;
declare b int;
declare xueli char(10);
declare xm cursor
for select education from zhangyk_employees;
declare continue handler for not found 
set flag=false;
set a=0,b=0;
open xm;
while flag do
fetch xm into xueli;
if xueli='大专' then set a=a+1;
end if;
set b=b+1;
end while;
select concat('比例为',1-a/b);
close xm;
end@@

call zyk1();

2.存储函数
（1）创建存储函数，判断员工是否在研发部工作，若是则返回其学历，若不是则返回字符串“NO”。（15分）

delimiter @@
CREATE FUNCTION zyk2(cname char(10))
RETURNS char(10)
BEGIN 
DECLARE xueli char(10);
DECLARE a char(10);
set a=(SELECT departmentName from zhangyk_departments
INNER JOIN zhangyk_employees on zhangyk_employees,
departmentID=zhangyk_departments.departmentID WHERE name =cname);
if a='研发部' then SELECT education into xueli from zhangyk_employees
WHERE name = cname;
else set xueli='NO';
end if;
RETURN xueli;
end @@

drop function zyk2

SELECT zyk2('叶凡')；

（2）创建一个存储函数，将工作时间满4年的员工收入增加500。（15分）

delimiter @@
CREATE FUNCTION zyk2()
RETURNS char(10)
BEGIN
DECLARE flag boolean DEFAULT true;
DECLARE a date;
DECLARE xm CURSOR
for SELECT hiredate from emp2;
DECLARE CONTINUE HANDLER for not found
set flag=FALSE;
set flag = false;
open xm;
WHILE flag DO
	fetch xm into a;
	if year(now())-year(a)>4 then 
update emp2 set sal=sal+500 where hiredate=a;
end if;
END WHILE;
end while;
return 'ok';
close xm;
end @@

SELECT zyk2()

3.触发器
（1）创建UPDATE触发器，当Departments表中部门号发生变化时，Employees表中员工所属部门号也将改变。（15分）

delimiter @@
create trigger xm
after update on zhangyk_departments1
for each row
begin
update zhangyk_employees3 set
departmentID=new.departmentID where departmentID=old.departmentID;
end @@

update zhangyk_departments1 set departmentID=60 where departmentID=1
select * from zhangyk_employees3

（2）创建UPDATE触发器，当Salary表中的InCome值增加500时，OutCome值则增加50。（15分）

delimiter @@
create trigger zhangyk_update2
before update on zhangyk_salary
for each row 
begin 
declare n int;
declare s float;
set s=new.income-old.income;
set n=s/500;
if n>=1 then 
set new.outcome=out.outcome+n*50;
end if;
end @@