Mysql代碼.doc

1. 存儲過程練習（1）創(chuàng)建存儲過程，使用Employees表中的員工人數(shù)來初始化一個局部變量，并調(diào)用這個存儲過程。USE YGGLDELIMITER $CREATE PROCEDURE TEST(OUT NUMBER1 INT )BEGIN DECLARE NUMBER2 INT; SET NUMBER2=(SELECT COUNT(*) FROM Employees); SET NUMBER1=NUMBER2;END$DELIMITER ;調(diào)用該存儲過程CALL TEST(NUMBER);查看NUMBER的值。SELECT NUMBER ;將調(diào)用存儲過程及查看NUMBER的值的命令及結果進行屏幕截圖。(2) 創(chuàng)建存儲過程，比較兩個員工的實際收，若前者比后者高就輸出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 ;調(diào)用該存儲過程CALL COMP(000001,108991,BJ1);查看BJ1的值。SELECT BJ1 ;將調(diào)用存儲過程及查看BJ1的值的命令及結果進行屏幕截圖。（2） 創(chuàng)建存儲過程，使用游標確定一個員工的實際收入是否排在前三名。結果為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 ;調(diào)用該存儲過程并查看結果，進行屏幕截圖思考題：1 創(chuàng)建存儲過程，要求當一個員工的工作年份大于6年時將其轉到經(jīng)理辦公室工作。2 創(chuàng)建存儲過程，使用游標計算本科及以上學歷的員工在總員工數(shù)中所占的比例。2存儲函數(shù)（1）創(chuàng)建一個存儲函數(shù)，返回員工的總人數(shù)。CREATE FUNCTION EM_NUM( )RETURNS INTRETURN(SELECT COUNT(*) FROM Employees);調(diào)用該存儲函數(shù)：SELECT EM_NUM();（2）創(chuàng)建一個存儲函數(shù)，檢驗一個員工號是否在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 ;調(diào)用該存儲函數(shù)：SELECT DELETE_EM (000001);往Salary表中插入一行記錄后調(diào)用該存儲函數(shù)。INSERT INTO Salary values(0