答案實(shí)驗(yàn)五Oracle編程一

1、Oracle技術(shù)實(shí)驗(yàn)報(bào)告(五)成績實(shí)驗(yàn)名稱Oracle編程（一）姓名學(xué)號(hào)班級(jí)實(shí)驗(yàn)?zāi)康模?）熟練掌握編寫和調(diào)試PL/SQL塊的方法。（2）熟練掌握編寫和調(diào)試存儲(chǔ)過程方法。（3）熟練掌握編寫和調(diào)試函數(shù)方法。（4）熟練掌握各種控制結(jié)構(gòu)語句的使用。實(shí)驗(yàn)內(nèi)容（1） 編寫一個(gè)PL/SQL塊，輸出emp表中所有員工的員工名、員工號(hào)、工資和部門號(hào)。BEGIN FOR v_emp IN (SELECT * FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.ename| | v_emp.empno| |v_emp.sal| | v_emp.deptno); END LOOP; E

2、ND; /（2） 創(chuàng)建一個(gè)存儲(chǔ)過程，以員工號(hào)為參數(shù)，輸出該員工的工資。 （提示：如果不存在該員工，則顯示“員工號(hào)不存在” ）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_LINE(v_sal);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(員工號(hào)不存在);END;

3、（3） 編寫一個(gè)PL/SQL塊，查詢名為“SMITH”的員工信息，并輸出其員工號(hào)、工資、部門號(hào)。如果該員工不存在，則插入一條新記錄，員工號(hào)為2007，員工名為“SMITH”，工資為1500，部門號(hào)為10。如果存在多個(gè)名為“SMITH”的員工，則輸出所有名為“SMITH”的員工號(hào)、工資和部門號(hào)。（提示：要使用NO_DATE_FOUND和TOO_MANY_ROWS兩個(gè)異常。）DECLARE V_empno emp.empno%type; V_sal emp.sal%type; V_deptno emp.deptno%type;BEGIN SELECT empno,sal,deptno INTO V

4、_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 emp WHERE ename=SMITH) LOOP DB

5、MS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.sal| |v_emp.deptno); END LOOP; END; /（4） 創(chuàng)建一個(gè)存儲(chǔ)過程，以部門號(hào)為參數(shù)，輸出該部門入職日期最早的員工姓名。（提示：如果不存在該部門，則顯示“無此部門或此部門無員工!” ）CREATE OR REPLACE PROCEDURE CC(p_deptno emp.deptno%type)AS V_ename emp.ename%type;BEGIN select ename into v_ename from emp where deptno=p_deptno 實(shí)驗(yàn)內(nèi)容 and

6、 hiredate=(select min(hiredate) from emp where deptno=p_deptno); DBMS_OUTPUT.PUT_LINE(v_ename); exception when no_data_found then DBMS_OUTPUT.PUT_LINE('無此部門或此部門沒有員工!');END;（5） 創(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.s

7、al%type;BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=p_empno; RETURN v_sal;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN -1;END; /（6） 編寫一個(gè)PL/SQL塊調(diào)用（5）中創(chuàng)建的函數(shù)。如果不存在該員工，則顯示“員工號(hào)不存在!”。 命令：DECLARE V_empno emp.deptno%type; V_sal emp.sal%type;BEGIN V_empno:=&x; V_sal:=ret_sal(v_empno); If v_sal=-1 then D

8、BMS_OUTPUT.PUT_LINE(員工號(hào)不存在); Else DBMS_OUTPUT.PUT_LINE(v_sal); End if; END;/（7） 創(chuàng)建一個(gè)函數(shù)，以部門號(hào)為參數(shù)，返回該部門的平均工資。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 NUL

9、L THEN RETURN -1; ELSE RETURN v_avgsal; END IF; END;/（8） 編寫一個(gè)PL/SQL塊調(diào)用（7）中創(chuàng)建的函數(shù)。如果不存在該部門，則顯示“部門號(hào)不存在!”。DECLARE V_deptno emp.deptno%type; V_avgsal emp.sal%type;BEGIN V_deptno:=&x; V_avgsal:=ret_avgsal(v_deptno); If v_avgsal=-1 then DBMS_OUTPUT.PUT_LINE(部門號(hào)不存在！); Else DBMS_OUTPUT.PUT_LINE(v_avgsal)

10、; End if; 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 deptno INTO v_deptno FROM emp WHERE empno=p_empno; SELECT avg(sal) INTO v_avgsal FROM emp WHERE dept

11、no=v_deptno; RETURN v_avgsal;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN -1;END; /（10） 編寫一個(gè)PL/SQL塊調(diào)用（9）中創(chuàng)建的函數(shù)。如果不存在該部門，則顯示“員工號(hào)不存在!”。 DECLARE V_empno emp.empno%type;v_avgsal emp.sal%type; BEGIN V_empno:=&x; V_avgsal:= RET_AVGSAL_EMPNO(v_empno); If v_avgsal=-1 thenDBMS_OUTPUT.PUT_LINE(員工號(hào)不存在！); Else

12、DBMS_OUTPUT.PUT_LINE(v_avgsal); End if; END; /（11） 創(chuàng)建一個(gè)存儲(chǔ)過程，以一個(gè)整數(shù)為參數(shù)，輸出工資最高的前幾個(gè)(參數(shù)值)員工的姓名。CREATE OR REPLACE PROCEDURE max_n(p_n int)AS v_i int:=1;BEGIN for v_emp in (SELECT * FROM emp ORDER BY sal desc) loop DBMS_OUTPUT.PUT_LINE(v_emp.ename); Exit when v_i=p_n; v_i:=v_i+1; end loop; END;/（12） 創(chuàng)建一個(gè)存儲(chǔ)過程，以兩個(gè)整數(shù)為參數(shù)，輸出工資排序在兩個(gè)參數(shù)之間的員工姓名。CREATE OR REPLACE PROCEDURE min_m