答案实验五：Oracle编程

1、Oracle技术实验报告(五)实验名称Oracle 编程(一)姓名学号班级成绩实 验 内 容实验(1)熟练掌握编写和调试PL/SQL块的方法。头验(2)熟练掌握编写和调试存储过程方法。曰的(3)熟练掌握编写和调试函数方法。曰的(4)熟练掌握各种控制结构语句的使用。(1) 编写一个PL/SQL块，输出emp表中所有员工的员工名、员工号、 工资和部门号。BEGINFOR v_emp IN (SELECT * FROM EMP) LOOP II '1111' Illi 'II ；END LOOP;END;/(2) 创建一个存储过程，以员工号为参数，输出该员工的工资。(提示：如

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建一个存储过程，以部门号为参数，输出该部门入职日期最 早的员工姓名。(提示：如果不存在该部门，则显示“无此部门或此部门无员工！” )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) 创建一个函数，以员工号为参数，返回该员工的工资。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)中创建的函数。如果不存在该 员工，则显示“员工号不存在！”命令：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) 创建一个函数，以部门号为参数，返回该部门的平均工资。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)中创建的函数。如果不存在该 部门，贝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建一个函数，以员工号为参数，返回该员工所在部门的平 均工资。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)中创建的函数。如果不存在该

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) 创建一个存储过程，以一个整数为参数，输出工资最高的前 几个(参数值)员工的姓名。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)创建一个存储过程，以两个整数为参数，输出工资排序在两 个参数之间的员工姓名。CREATE OR REPLAC