Mysql代码.doc

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的值的命令及结果进行屏幕截图。（2） 创建存储过程，使用游标确定一个员工的实际收入是否排在前三名。结果为TRUE表示是，结果为FALSE表示否。DELIMITER $CREATE PROCEDURE TOP3(IN EM_ID CHAR(6),OUT OK BOOLEAN )BEGIN DECLARE X_EM_ID CHAR(6); DECLARE ACT_IN,SEQ INT;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 SEQ3 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 ;调用该存储过程并查看结果，进行屏幕截图思考题：1 创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。2 创建存储过程，使用游标计算本科及以上学历的员工在总员工数中所占的比例。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(0