課件實驗十plsql高級應用

1、實驗十 PL/SQL 高級應用實驗目的掌握 PL/SQL 高級應用，包括過程及函數的創(chuàng)建與使用。實驗內容1.過程的創(chuàng)建與使用2.函數的創(chuàng)建使用實驗步驟1.創(chuàng)建一個過程 SELECT_DEPT，以部門號為 IN 參數，查詢顯示該部門的平均工資，并顯示輸出該部門中比平均工資高的員工號、員工名，如果部門號對應出“該部門不存在，請重新輸入?！保徊淮嬖?，提示輸測試調用：編寫一個的相關內容。PL/SQL 塊調用過程 SELECT_DEPT，查詢部門號為 30 部門create or replace procedure SELECT_DEPT(fied_deptno dept.deptno%type) as

2、deptno_temp dept.deptno%type; avg_sal emp.sal%type;beginselect deptnoo deptno_temp from dept where deptno=fied_deptno;select round(avg(sal)o avg_sal from emp wheredeptno=fied_deptno;dbms_output.put_line(fied_deptno |號部門平均工資為 | avg_sal); dbms_output.put_line(*);for v_emp in (select * from emp where s

3、alavg_sal)loopdbms_output.put_line(v_emp.empno | end loop;exception| v_emp.ename);when no_data_found then dbms_output.put_line(該部門不存在，請重新輸入。); end;begin SELECT_DEPT(20);end;2. 編寫過程 UPDATE_SAL，實現漲工資的功能，輸入參數為員工，如果他的工作年限是 10-15 年，工資增加 1300，工作年限是 16-20 年工資增加 2000，年限超過 20 年工資增加 3000；輸出參數為員工名和現在的工資數目；測試調用

4、：編寫一個PL/SQL 塊調用過程 UPDATE_SAL，實現員工“SMITH”及“CLARK”的工資增長，并顯示輸出工資增長前后的數目。create or replace procedure UPDATE_SAL(fied_ename emp.ename%type) asename_temp emp.ename%type; add_temp emp.sal%type;sal_temp emp.sal%type;nian number:=0; beginselect trunc(months_betn(sysdate,hiredate)/12)o nian from emp where ena

5、me=fied_ename;select salo sal_temp from emp where ename=fied_ename;if nian 9 and nian 16 and nian 20 then add_temp:=3000;end if;update emp set sal=sal+ sal_temp where ename=fied_ename;dbms_output.put_line(fied_ename | sal_temp:=sal_temp+add_temp; dbms_output.put_line(fied_ename | end;begin UPDATE_SA

6、L(CLARK);end;修改前工資為 | sal_temp);修改后工資為 | sal_temp);3. 創(chuàng)建一個過程 FIND_NUMBER，以部門號為參數，返回該部門的總人數與該部門MANAGER 對應的員工名，如果部門號對應新輸入?！?；測試調用：編寫一個PL/SQL 塊調用的總人數及經理名并顯示輸出。不存在，提示輸出“該部門不存在，請重過程 FIND_NUMBER，查詢部門號為 10 部門create or replace procedure FIND_NUMBER(fied_deptno dept.deptno%type) asdeptno_temp dept.deptno%type

7、; count_emp number; manager_ename emp.ename%type;beginselect deptno select count(*)select enameo deptno_temp from dept where deptno=fied_deptno;o count_emp from emp where deptno=fied_deptno;o manager_ename from emp where deptno=fied_deptno and job=MANAGER;dbms_output.put_line(fied_deptno | 號部門總人數為 |

8、 count_emp); dbms_output.put_line(MANAGER: | manager_ename);exceptionwhen no_data_found then dbms_output.put_line(該部門不存在，請重新輸入。); end;begin FIND_NUMBER(20);end;4. 創(chuàng)建一個以部門號為參數，返回該部門最高工資的函數 RETURN_MAXSAL；測試調用：編寫一個PL/SQL 塊循環(huán)游標遍歷 EMP 表，取得部門號賦給該函數，并依次打印函數返回值。create or replace function RETURN_MAXSAL(fied_

9、deptno emp.deptno%type)return emp.sal%type asmax_sal emp.sal%type; beginselect max(sal)o max_sal from emp where deptno=fied_deptno; return max_sal;end ; declaremax_sal emp.sal%type; beginfor deptno_emp in (select deptno from emp)loopselect RETURN_MAXSAL(deptno_emp.deptno) dbms_output.put_line(max_sa

10、l);end loop;end;o max_sal from dual;5. 編寫一個函數 FUNC_FIND，輸入參數是員工號，功能是如果該職工的職務是經理，而且工資大于 5000，返回（工資+工資*20%），否則返回（工資+工資*40%）；測試調用：編寫一個次打印函數返回值。PL/SQL 塊循環(huán)游標遍歷 EMP 表，取得員工號賦給該函數，并依create or replace function FUNC_FIND(fied_empno emp.empno%type) return emp.sal%type astemp_job emp.job%type; temp_sal emp.sal%type; add_sal emp.sal%type;beginselect job,salo temp_job,temp_sal from emp where empno=fied_empno;if temp_job=MANAGER and temp_sal5000 then add_sal:=temp_sal*1.2; else add_sal:=temp_sal*1.4;end if;return add_sal; end ;de