课件实验十plsql高级应用

1、实验十 PL/SQL 高级应用实验目的掌握 PL/SQL 高级应用，包括过程及函数的创建与使用。实验内容1.过程的创建与使用2.函数的创建使用实验步骤1.创建一个过程 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. 创建一个过程 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. 创建一个以部门号为参数，返回该部门最高工资的函数 RETURN_MAXSAL；测试调用：编写一个PL/SQL 块循环游标遍历 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 块循环游标遍历 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