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

实验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 YGGL DELIMITER $$

1

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 SR1>SR2 THEN SET BJ=0; ELSE SET BJ=1; END IF; END$$ DELIMITER ;

调用该存储过程

CALL COMP('000001','108991',@BJ1);

查看@BJ1的值。

SELECT @BJ1 ;

将调用存储过程及查看@BJ1的值的命令及结果进行屏幕截图。

2

（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 Salary ORDER BY 2 DESC;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET 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 SEQ<3 AND OK=FALSE DO SET SEQ=SEQ+1;

IF X_EM_ID=EM_ID THEN SET OK=TRUE; END IF;

FETCH SALARY_DIS INTO X_EM_ID,ACT_IN; END WHILE;

CLOSE SALARY_DIS; END$$ DELIMITER ;

调用该存储过程并查看结果，进行屏幕截图

CALL TOP3('010018',@OUT1); SELECT @OUT1;

3

思考题：

1． 创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。

CREATE PROCEDURE t1() BEGIN

DECLARE DID CHAR(3);

SELECT DEPARTMENTID INTO DID FROM DEPARTMENTS WHERE DEPARTMENTNAME='经理办公室'; UPDATE EMPLOYEES SET DEPARTMENTID=DID WHERE WORKYEAR>6; END

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

DELIMITER $$

CREATE PROCEDURE t2(OUT RATIO float ) BEGIN

DECLARE 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 FOUND

SET FOUND=FALSE;

SET NUM1=0; SET NUMTOTAL=0; OPEN COMP;

4

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 INT

RETURN(SELECT COUNT(*) FROM Employees);

调用该存储函数：

SELECT EM_NUM();

（2）创建一个存储函数，检验一个员工号是否在Salary表中有但在Employees表中不存在，若是则删除Salary表中该员工并返回TRUE，否则返回FALSE。

DELIMITER $$

CREATE FUNCTION DELETE_EM(EM_ID CHAR(6) )

RETURNS BOOLEAN BEGIN

DECLARE NUM1,NUM2 INT;

SELECT COUNT(*) INTO NUM1 FROM Salary WHERE EmployeeID=EM_ID;

5

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) BEGIN

DECLARE 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

6

RETURN EDU; ELSE

RETURN 'NO'; END IF; END

3．触发器

（1）创建触发器，在Employees表中删除员工信息的同时将Salary表中该员工的信息删除，以确保数据完整性。

CREATE TRIGGER DELETE_EM AFTER DELETE

ON Employees FOR EACH ROW

DELETE 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 INSERT BEGIN END$$ DELIMITER ;

创建完后添加一个部门到Departments，然后查看Departments1表中的变化情况。

ON Departments FOR EACH ROW INSERT INTO Departments1

VALUES(NEW.DepartmentID,NEW.DepartmentName,NEW.Note);

（3）创建触发器，当修改Employees表时，若将Employees表中员工的工作时间增加1年，则将收入增加500，增加2年则增加1000，依次类推。若工作时间减少则无变化。

DELIMITER $$

7

CREATE TRIGGER ADD_SALARY AFTER UPDATE BEGIN

DECLARE YEARS INT;

SET YEARS=NEW.WorkYear-OlD.WorkYear; IF YEARS>0 THEN

UPDATE Salary SET InCome=InCome+500*YEARS WHERE

EmployeeID=NEW. EmployeeID;

END IF;

END$$ DELIMITER ;

创建完后修改Employees表中的一个职工的工作时间，然后查看Salary表中对应职工的收入变化情况。

ON Employees FOR EACH ROW

思考题：

1． 创建UPDATE触发器，当表Departments中的部门号发生变化时，Employees表中的员工所属的部门号也将改变。

CREATE TRIGGER T_11 AFTER UPDATE

ON Departments FOR EACH ROW UPDATE

Employees

SET DepartmentID=NEW.DepartmentID WHERE

DepartmentID=OLD.DepartmentID;

2． 创建UPDATE触发器，当表Salary中的InCome值增加500时，OutCome值增加50。

CREATE TRIGGER T_12 BEFORE UPDATE

ON Salary FOR EACH ROW

BEGIN

DECLARE N INT; DECLARE S FLOAT;

SET S=NEW.INCOME-OLD.INCOME; SET N=S/500;

8

IF N>=1 THEN

SET NEW.OUTCOME=old.outcome+N*50; END IF; END;

4．事件

（1）创建一个立即执行的事件，查询Employees表的信息。

CREATE EVENT DIRECT

ON SCHEDULE AT NOW() DO

SELECT * FROM Employees;

（2）创建一个事件，每天执行一次，它从明天开始直到2011年12月31日结束。

CREATE EVENT every_day

ON SCHEDULE EVERY 1 DAY

STARTS CURDATE()+INTERVAL 1 DAY ENDS '2011-12-31' DO

SELECT * FROM Employees; 思考题：

本次实验中遇到的问题及解决方法。

9

本文来源：https://www.bwwdw.com/article/3k37.html