技術(shù)實(shí)驗(yàn)報(bào)告5答案1

1、Oracle技術(shù)實(shí)驗(yàn)報(bào)告成績(jī)實(shí)驗(yàn)名稱PL/SQL編程（選作10題，程序另加附頁(yè)）姓名學(xué)號(hào)日期專業(yè)班級(jí)實(shí)驗(yàn)?zāi)康模?）了解PL/SQL塊的基本結(jié)構(gòu)和分類。（2）熟練掌握編寫PL/SQL塊的方法。（3）了解PL/SQL存儲(chǔ)過程、觸發(fā)器、游標(biāo)的作用。（4）熟練掌握編寫PL/SQL存儲(chǔ)過工程的方法及維護(hù)過程。（5）熟練掌握創(chuàng)建觸發(fā)器的方法及維護(hù)過程。（6）孰練掌握顯式游標(biāo)和隱式游標(biāo)的使用。實(shí)驗(yàn)內(nèi)容（1） 編寫一個(gè)PL/SQL塊，輸出所有員工的員工名、員工號(hào)、工資和部門號(hào)。BEGIN FOR v_emp IN (SELECT * FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(v_

2、emp.ename| | v_emp.empno| |v_emp.sal| | v_emp.deptno); END LOOP; END; /（2） 編寫一個(gè)PL/SQL塊，查詢名為“SMITH”的員工信息，并輸出其員工號(hào)、工資、部門號(hào)。如果該員工不存在，則插入一條新記錄，員工號(hào)為2007，員工名為“SMITH”，工資為1500，部門號(hào)為10。如果存在多個(gè)名為“SMITH”的員工，則輸出所有名為“SMITH”的員工號(hào)、工資和部門號(hào)。DECLARE V_empno emp.empno%type; V_sal emp.sal%type; V_deptno emp.deptno%type;BEGIN

3、 SELECT empno,sal,deptno INTO V_empno,v_sal,v_deptno FROM emp WHERE ename=SMITH; DBMS_OUTPUT.PUT_LINE(v_empno| |v_sal| |v_deptno); EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO EMP(empno, ename,sal,deptno) VALUES(2007,SMITH,1500,10); WHEN TOO_MANY_ROWS THEN FOR v_emp IN (SELECT empno,sal,deptno FROM

4、 emp WHERE ename=SMITH) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.sal| |v_emp.deptno); END LOOP; END; /（3） 創(chuàng)建一個(gè)存儲(chǔ)過程，以員工號(hào)為參數(shù)，輸出該員工的工資。CREATE OR REPLACE PROCEDURE OUTPUT_SAL(p_empno emp.empno%type) AS V_sal emp.sal%type;BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=p_empno; DBMS_OUTPUT.PUT_LIN

5、E(v_sal);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(無(wú)此員工);END;（4） 創(chuàng)建一個(gè)存儲(chǔ)過程，以員工號(hào)為參數(shù)，修改該員工的工資。若該員工屬于10號(hào)部門，則工資增加150；若屬于20號(hào)部門，則工資增加200；若屬于30號(hào)部門，則工資增加250；若屬于其他部門，則工資增加300。CREATE OR REPLACE PROCEDURE UPDATE_SAL(p_empno emp.empno%type)AS v_deptno emp.deptno%type; v_increment NUMBER(4); BEGIN SE

6、LECT deptno INTO v_deptno FROM emp WHERE empno=p_empno; CASE v_deptno WHEN 10 THEN v_increment:=150; WHEN 20 THEN v_increment:=200; WHEN 30 THEN v_increment:=250; ELSE v_increment:=300; END CASE; UPDATE emp SET sal=sal+v_increment WHERE empno=p_empno; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PU

7、T_LINE(無(wú)此員工); END; /（5） 創(chuàng)建一個(gè)存儲(chǔ)過程，以員工號(hào)為參數(shù)，返回該員工的工作年限(以參數(shù)形式返回)。CREATE OR REPLACE PROCEDURE RETURN_GZNX(p_empno emp.empno%type,p_gznx out number)AS v_gznx NUMBER(2); BEGIN SELECT trunc(months_between(sysdate,hiredate)/12) INTO v_gznx FROM emp WHERE empno=p_empno; p_gznx:=v_gznx; EXCEPTION WHEN NO_DATA_

8、FOUND THEN DBMS_OUTPUT.PUT_LINE(無(wú)此員工); END; /調(diào)用上面的存儲(chǔ)過程的PL/SQL塊DECLARE v_gznx number(2);BEGIN Return_gznx(7900,v_gznx); DBMS_OUTPUT.PUT_LINE(v_gznx);END;/（6） 創(chuàng)建一個(gè)存儲(chǔ)過程，以部門號(hào)為參數(shù)，輸出該部門入職日期最早的2個(gè)員工姓名。CREATE OR REPLACE PROCEDURE OUTPUT_EMPINFO(p_deptno emp.deptno%type)AS CURSOR c_emp IS SELECT * FROM emp WH

9、ERE deptno=p_deptno ORDER BY hiredate; V_emp emp%rowtype; BEGIN OPEN c_emp; FETCH c_emp INTO v_emp; IF c_emp%found THEN DBMS_OUTPUT.PUT_LINE(v_emp.ename); FETCH c_emp INTO v_emp; IF c_emp%found THEN DBMS_OUTPUT.PUT_LINE(v_emp.ename); ELSE DBMS_OUTPUT.PUT_LINE(該部門只有一個(gè)員工！); END IF;ELSE DBMS_OUTPUT.PUT

10、_LINE(無(wú)此部門!); END IF; END; /（7） 創(chuàng)建一個(gè)函數(shù)，以員工號(hào)為參數(shù)，返回該員工的工資。CREATE OR REPLACE FUNCTION RET_SAL(p_empno emp.empno%type) RETURN emp.sal%typeAS v_sal emp.sal%type;BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=p_empno; RETURN v_sal;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0;END; /（8） 創(chuàng)建一個(gè)函數(shù)，以部門號(hào)為參數(shù)，返回該部門

11、的平均工資。CREATE OR REPLACE FUNCTION RET_AVGSAL(p_deptno emp.deptno%type) RETURN emp.sal%typeAS v_avgsal emp.sal%type;BEGIN SELECT avg(sal) INTO v_avgsal FROM emp WHERE deptno=p_deptno; IF v_avgsal IS NULL THEN DBMS_OUTPUT.PUT_LINE('無(wú)此部門！'); RETURN 0; ELSE RETURN v_avgsal; END IF; END;/調(diào)用上面函數(shù)的PL

12、/SQL塊DECLARE V_deptno emp.deptno%type;BEGIN V_deptno:=&x; DBMS_OUTPUT.PUT_LINE(RET_AVGSAL(v_deptno); END;/（9） 創(chuàng)建一個(gè)函數(shù)，以員工號(hào)為參數(shù)，返回該員工所在部門的平均工資。CREATE OR REPLACE FUNCTION RET_AVGSAL_EMPNO(p_empno emp.empno%type) RETURN emp.sal%typeAS v_deptno emp.deptno%type; v_avgsal emp.sal%type;BEGIN SELECT deptn

13、o INTO v_deptno FROM emp WHERE empno=p_empno; SELECT avg(sal) INTO v_avgsal FROM emp WHERE deptno=v_deptno; RETURN v_avgsal;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(無(wú)此員工!); RETURN 0;END; / 調(diào)用上面函數(shù)的PL/SQL塊DECLARE V_empno emp.empno%type; BEGIN V_empno:=&x; DBMS_OUTPUT.PUT_LINE(RET_AVG

14、SAL_EMPNO(v_empno); END; /（10） 創(chuàng)建一個(gè)包，包中包含一個(gè)過程和一個(gè)游標(biāo)。游標(biāo)返回所有員工的信息；存儲(chǔ)過程實(shí)現(xiàn)每次輸出游標(biāo)中的5條記錄。（11） 在emp表上創(chuàng)建一個(gè)觸發(fā)器，保證每天8：0017：00之外的時(shí)間禁止對(duì)該表進(jìn)行DML操作。CREATE OR REPLACE TRIGGER tr_empBEFORE INSERT OR UPDATE OR DELETEON empBEGIN IF ( to_number(to_char(sysdate, 'hh24') not between 8 and 16) THEN RAISE_APPLICATIO

15、N_ERROR(-20600, '現(xiàn)在是非工作時(shí)間，請(qǐng)退出! '); END IF;END;/（12） 在emp表上創(chuàng)建一個(gè)觸發(fā)器，當(dāng)插入、刪除或修改員工信息時(shí)，統(tǒng)計(jì)各部門的人數(shù)及平均工資，并輸出。CREATE OR REPLACE TRIGGER tr_emp1AFTER INSERT OR UPDATE OR DELETEON empBEGIN FOR v_emp IN (SELECT deptno,count(*) rs,avg(sal) avgsal FROM emp GROUP BY deptno) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.deptno| |v_emp.rs| |trunc(v_emp.avgsal); EN