答案實驗五：Oracle編程

1、Oracle技術實驗報告(五)實驗名稱Oracle 編程(一)姓名學號班級成績實 驗 內 容實驗(1)熟練掌握編寫和調試PL/SQL塊的方法。頭驗(2)熟練掌握編寫和調試存儲過程方法。曰的(3)熟練掌握編寫和調試函數(shù)方法。曰的(4)熟練掌握各種控制結構語句的使用。(1) 編寫一個PL/SQL塊，輸出emp表中所有員工的員工名、員工號、 工資和部門號。BEGINFOR v_emp IN (SELECT * FROM EMP) LOOP II '1111' Illi 'II ；END LOOP;END;/(2) 創(chuàng)建一個存儲過程，以員工號為參數(shù)，輸出該員工的工資。(提示：如

2、果不存在該員工，則顯示“員工號不存在”)CREATE OR REPLACE PROCEDURE OUTPUT_SAL(p_emp no %type)ASV_sal %type;BEGINSELECT sal INTO v_sal FROM emp WHERE emp no=p_emp no;(v_sal); EXCEPTIONWHEN NO_DATA_FOUND THEN('員工號不存在)；END;(3) 編寫一個PL/SQL塊，查詢名為“ SMITH的員工信息，并輸出其員工號、工資、部門號。如果該員工不存在，則插入一條新記錄，員工號為2007,員工名為“ SMITH,工資為1500,

3、部門號為10。如果存在多個名為“ SMITH的員工，貝U輸出所有名為“SMITH的員工號、工資和部門號。(提示：要使用NO_DATE_FOU和DTOO_MANY_R(W個異常。)DECLAREV_emp no %type;V_sal %type;V_dept no %type;BEGINSELECT emp no,sal,dept no INTOV_empno,v_sal,v_deptno FROM emp WHERE ename=' SMITH ;(v_empno| '' |v_sal|'' |v_deptno);EXCEPTIONWHEN NO_DA

4、TA_FOUND THENINSERT INTO EMP(emp no, en ame,sal,dept no)VALUES(2007, SMITH ,1500,10);WHEN TOO_MANY_ROWS THENFOR v_emp IN (SELECT emp no,sal,dept no FROMemp WHERE ename= SMITH ) LOOPII '1111' II；END LOOP;END;/(4) 倉U建一個存儲過程，以部門號為參數(shù)，輸出該部門入職日期最 早的員工姓名。(提示：如果不存在該部門，則顯示“無此部門或此部門無員工！” )CREATE OR RE

5、PLACE PROCEDURE CC(p_dept no %type)ASV_en ame %type;BEGINselect en ame into v en ame from emp where dept no=p dept no實 驗 內容and hiredate=(select min( hiredate) from emp where dept no=p_dept no);(v_en ame);excepti onwhe n no _data_fo und the n('無此部門或此部門沒有員工!');END;(5) 創(chuàng)建一個函數(shù)，以員工號為參數(shù)，返回該員工的工資。C

6、REATE OR REPLACE FUNCTIONRET_SAL(p_emp no %type)RETURN %typeASv_sal %type;BEGINSELECT sal INTO v_sal FROM emp WHERE emp no=p_emp no;RETURN v_sal;EXCEPTIONWHEN NO_DATA_FOUND THEN RETURN -1;END;/(6) 編寫一個PL/SQL塊調用(5)中創(chuàng)建的函數(shù)。如果不存在該 員工，則顯示“員工號不存在！”命令：DECLAREV_emp no %type;V_sal %type;BEGINV_emp no:=&x

7、;V_sal:=ret_sal(v_emp no);If v_sal=-1 the n ('員工號不存在);Else(v_sal);End if;END;/(7) 創(chuàng)建一個函數(shù)，以部門號為參數(shù)，返回該部門的平均工資。CREATE OR REPLACE FUNCTION RET_AVGSAL(p_dept no %type)RETURN %typeASv_avgsal %type;BEGINSELECT avg(sal) INTO v_avgsal FROM empWHERE dept no=p_dept no;IF v_avgsal IS NULL THENRETURN -1;ELSE

8、RETURN v_avgsal;END IF;END;/(8) 編寫一個PL/SQL塊調用(7)中創(chuàng)建的函數(shù)。如果不存在該 部門，貝U顯示“部門號不存在！”DECLAREV_dept no %type;V_avgsal %type;BEGINV_dept no:=&x; V_avgsal:=ret_avgsal(v_dept no);If v_avgsal=-1 the n ('部門號不存在！)；Else (v_avgsal);End if;END;/(9) 倉U建一個函數(shù)，以員工號為參數(shù)，返回該員工所在部門的平 均工資。CREATE OR REPLACE FUNCTIONRE

9、T_AVGSAL_EMPNO(p_emp no %type)RETURN %typeASv_dept no %type;v avgsal %type;BEGINSELECT dept no INTO v_dept no FROM emp WHERE emp no=p_emp no;SELECT avg(sal) INTO v_avgsal FROM empWHERE dept no=v_dept no;RETURN v_avgsal;EXCEPTIONWHEN NO_DATA_FOUND THENRETURN -1;END;/(10) 編寫一個PL/SQL塊調用(9)中創(chuàng)建的函數(shù)。如果不存在該

10、 部門，則顯示“員工號不存在！”DECLAREV_emp no %type;v_avgsal %type;BEGINV_emp no:=&x;V_avgsal：= RET_AVGSAL_EMPNO(v_emp no);If v_avgsal=-1 the n('員工號不存在！);Else(v_avgsal);End if;END;/(11) 創(chuàng)建一個存儲過程，以一個整數(shù)為參數(shù)，輸出工資最高的前 幾個(參數(shù)值)員工的姓名。CREATE OR REPLACE PROCEDURE max_ n(p_n int)AS v_i in t:=1;BEGINforv_empin (SELECT * FROM emp ORDER BY sal desc)loopJExit whe n v_i=p_n;v i:=v i+1;end loop;END;/(12)創(chuàng)建一個存儲過程，以兩個整數(shù)為參數(shù)，輸出工資排序在兩 個參數(shù)之間的員工姓名。CREATE OR REPLAC