网络数据库技术课程实验8anN1.doc

实验8 存储过程实验类型： 验证型 实验课时： 4 指导教师： 鲁 琴 时 间：2011年 月 日 课 次：第 节 教学周次：第 周 实验分室： 实验台号： 实 验 员： 实验目的：1 掌握存储过程的创建和调用方法2 掌握MySQL的流程控制语句3 掌握MySQL游标的使用4掌握存储函数的创建和调用方法5掌握触发器的创建和使用6掌握事件的创建和使用实验要求：掌握存储过程、存储函数、触发器和事件的相关知识实验内容：1 存储过程2 存储函数3 触发器4 事件实验步骤：说明：按实验步骤对数据库YGGL中的三个表进行操作，三个表结构如下（具体参看实验2）：Departments (DepartmentID,DepartmentName,Note)Employees (EmployeeID,Name,Sex,Birthday,Education,WorkYear,Address,PhoneNumber,DepartmentID)Salary(EmployeeID,InCome,OutCome)要求：将实验步骤中紫色底纹部分填上正确答案。1 存储过程（1）创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。USE YGGLDELIMITER $CREATE PROCEDURE TEST(OUT NUMBER1 INT )BEGIN DECLARE NUMBER2 INT; SET NUMBER2=(SELECT COUNT(*) FROM Employees); SET NUMBER1=NUMBER2;END$DELIMITER ;调用该存储过程CALL TEST(NUMBER);查看NUMBER的值。SELECT NUMBER ;将调用存储过程及查看NUMBER的值的命令及结果进行屏幕截图。（2） 创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1。DELIMITER $CREATE PROCEDURE COMP(IN ID1 CHAR(6),IN ID2 CHAR(6),OUT BJ INT )BEGIN DECLARE SR1,SR2 FLOAT; SELECT InCome-OutCome INTO SR1 FROM Salary WHERE EmployeeID=ID1; SELECT InCome-OutCome INTO SR2 FROM Salary WHERE EmployeeID=ID2; IF SR1SR2 THEN SET BJ=0; ELSE SET BJ=1; END IF;END$DELIMITER ;调用该存储过程CALL COMP(000001,108991,BJ1);查看BJ1的值。SELECT BJ1 ;将调用存储过程及查看BJ1的值的命令及结果进行屏幕截图。（3） 创建存储过程，使用游标确定一个员工的实际收入是否排在前三名。结果为TRUE表示是，结果为FALSE表示否。DELIMITER $CREATE PROCEDURE TOP3(IN EM_ID CHAR(6),OUT OK BOOLEAN )BEGIN DECLARE X_EM_ID CHAR(6); DECLARE SEQ INT;DECLARE ACT_IN FLOAT;DECLARE FOUND BOOLEAN;DECLARE SALARY_DIS CURSOR FOR SELECT EmployeeID, InCome-OutCome FROM SalaryORDER BY 2 DESC;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET FOUND=FALSE;SET SEQ=0;SET FOUND=TRUE;SET OK=FALSE;OPEN SALARY_DIS;FETCH SALARY_DIS INTO X_EM_ID,ACT_IN;WHILE FOUND AND SEQ6; END2 创建存储过程，使用游标计算本科及以上学历的员工在总员工数中所占的比例。DELIMITER $CREATE PROCEDURE t2(OUT RATIO float )BEGINDECLARE NUM1,NUMTOTAL INT;DECLARE EDU1 CHAR(4);DECLARE FOUND BOOLEAN DEFAULT TRUE;DECLARE comp CURSOR FOR SELECT Education FROM Employees;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET FOUND=FALSE;SET NUM1=0;SET NUMTOTAL=0;OPEN COMP;FETCH COMP INTO EDU1;WHILE FOUND DO SET NUMTOTAL=NUMTOTAL+1; IF EDU1=本科 or EDU1=硕士 THEN SET NUM1=NUM1+1; END IF; FETCH COMP INTO EDU1; END WHILE; CLOSE COMP;SET RATIO=NUM1/NUMTOTAL;END$DELIMITER ;2存储函数（1）创建一个存储函数，返回员工的总人数。CREATE FUNCTION EM_NUM( )RETURNS INTRETURN(SELECT COUNT(*) FROM Employees);调用该存储函数：SELECT EM_NUM();（2）创建一个存储函数，检验一个员工号是否在Salary表中有但在Employees表中不存在，若是则删除Salary表中该员工并返回TRUE，否则返回FALSE。DELIMITER $CREATE FUNCTION DELETE_EM(EM_ID CHAR(6) )RETURNS BOOLEANBEGINDECLARE NUM1,NUM2 INT;SELECT COUNT(*) INTO NUM1 FROM Salary WHERE EmployeeID=EM_ID;SELECT COUNT(*) INTO NUM2 FROM Employees WHERE EmployeeID=EM_ID; IF NUM1=1 AND NUM2=0 THEN DELETE FROM Salary WHERE EmployeeID=EM_ID;RETURN TRUE; ELSE RETURN FALSE; END IF;END$DELIMITER ;调用该存储函数：SELECT DELETE_EM (000001);往Salary表中插入一行记录后调用该存储函数。INSERT INTO Salary values(000002,1212,123);SELECT DELETE_EM (000002);观察并总结上面两次调用结果的不同。思考题：1 创建存储函数，判断员工是否在研发部工作，若是则返回其学历，若不是则返回字符串“NO”。CREATE FUNCTION JUDGE_EM(EM_ID CHAR(6) )RETURNS CHAR(4)BEGINDECLARE DEP CHAR(20);DECLARE EDU CHAR(4);SELECT DepartmentName,Education INTO DEP,EDU FROM EMPLOYEES,DEPARTMENTS WHERE EMPLOYEES.DepartmentID=DEPARTMENTS.DepartmentID AND EmployeeID=EM_ID; IF DEP=研发部 THEN RETURN EDU; ELSE RETURN NO; END IF;END3触发器（1）创建触发器，在Employees表中删除员工信息的同时将Salary表中该员工的信息删除，以确保数据完整性。CREATE TRIGGER DELETE_EM AFTER DELETEON Employees FOR EACH ROWDELETE FROM Salary WHERE EmployeeID=OLD. EmployeeID;创建完后删除Employees表中的一行数据，然后查看Salary表中的变化情况。（2）创建表Departments1,其结构和内容与表Departments都相同CREATE TABLE Departments1 LIKE Departments;INSERT INTO Departments1 SELECT * FROM Departments;在表Departments上创建一个触发器，如果添加一个新的部门，该部门也会添加到表Departments1中。DELIMITER $CREATE TRIGGER Departments_Ins AFTER INSERTON Departments FOR EACH ROWBEGININSERT INTO Departments1 VALUES(NEW.DepartmentID,NEW.DepartmentName,NEW.Note);END$DELIMITER ;创建完后添加一个部门到Departments，然后查看Departments1表中的变化情况。（3）创建触发器，当修改Employees表时，若将Employees表中员工的工作时间增加1年，则将收入增加500，增加2年则增加1000，依次类推。若工作时间减少则无变化。DELIMITER $CREATE TRIGGER ADD_SALARY AFTER UPDATEON Employees FOR EACH ROWBEGINDECLARE YEARS INT;SET YEARS=NEW.WorkYear-OlD.WorkYear;IF YEARS0 THENUPDATE Salary SET InCome=InCome+500*YEARS WHERE EmployeeID=NEW. EmployeeID;END IF;END$DELIMITER ;创建完后修改Employees表中的一个职工的工作时间，然后查看Salary表中对应职工的收入变化情况。思考题：1 创建UPDATE触发器，当表Departments中的部门号发生变化时，Employees表中的员工所属的部门号也将改变。CREATE TRIGGER T_11 AFTER UPDATEON Departments FOR EACH ROWUPDATE Employees SET DepartmentID=NEW.DepartmentID WHERE DepartmentID=OLD.DepartmentID;2 创建UPDATE触发器，当表Salary中的InCome值增加500时，OutCome值增加50。CREATE TRIGGER T_12 BEFORE UPDATEON Salary FOR EACH ROWBEGINDECLARE N INT;DECLARE S FLOAT;SET S=NEW.INCOME-OLD.INCOME;SET N=S/500;IF N=1 THENSET NEW.OUTCOME=old.outcome+N*50; END IF;END;4事件（1）创建一个立即执行的事件，查询Employees表的