答案实验五：Oracle编程（一）

《Oracle技术》实验报告(五)

成绩 实验名称 Oracle编程（一） 姓名 实验 学号 班级 （1）熟练掌握编写和调试PL/SQL块的方法。 （2）熟练掌握编写和调试存储过程方法。 （3）熟练掌握编写和调试函数方法。 目的 （4）熟练掌握各种控制结构语句的使用。 （1） 编写一个PL/SQL块，输出emp表中所有员工的员工名、员工号、工资和部门号。 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; END; / （2） 创建一个存储过程，以员工号为参数，输出该员工的工资。 （提示：如果不存在该员工，则显示“员工号不存在” ） 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(‘员工号不存在’); END; （3） 编写一个PL/SQL块，查询名为“SMITH”的员工信息，并输

出其员工号、工资、部门号。如果该员工不存在，则插入一条新记录，员工号为2007，员工名为“SMITH”，工资为1500，部门号为10。如果存在多个名为“SMITH”的员工，则输出所有名为“SMITH”的员工号、工资和部门号。（提示：要使用

NO_DATE_FOUND和TOO_MANY_ROWS两个异常。） DECLARE

V_empno emp.empno%type; V_sal emp.sal%type;

V_deptno emp.deptno%type; BEGIN

SELECT empno,sal,deptno INTO

V_empno,v_sal,v_deptno FROM emp WHERE ename=’SMITH’;

DBMS_OUTPUT.PUT_LINE(v_empno||’ ’||v_sal||’ ’||v_dept

no);

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

DBMS_OUTPUT.PUT_LINE(v_emp.empno||’ ‘|| v_emp.sal|| ’ ‘||v_emp.deptno); END LOOP; END; /

（4） 创建一个存储过程，以部门号为参数，输出该部门入职日期最

早的员工姓名。

（提示：如果不存在该部门，则显示“无此部门或此部门无员工!” ） 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

and 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） 创建一个函数，以员工号为参数，返回该员工的工资。

CREATE OR REPLACE FUNCTION RET_SAL(p_empno emp.empno%type) RETURN emp.sal%type AS

v_sal emp.sal%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） 编写一个PL/SQL块调用（5）中创建的函数。如果不存在该

员工，则显示“员工号不存在!”。

命令： 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

DBMS_OUTPUT.PUT_LINE(’员工号不存在’); Else

DBMS_OUTPUT.PUT_LINE(v_sal); End if; END; /

实 验 内 容

（7） 创建一个函数，以部门号为参数，返回该部门的平均工资。

CREATE OR REPLACE FUNCTION RET_AVGSAL(p_deptno emp.deptno%type) RETURN emp.sal%type AS

v_avgsal emp.sal%type; BEGIN

SELECT avg(sal) INTO v_avgsal FROM emp WHERE deptno=p_deptno;

IF v_avgsal IS NULL THEN RETURN -1; ELSE

RETURN v_avgsal; END IF; END; / （8） 编写一个PL/SQL块调用（7）中创建的函数。如果不存在该

部门，则显示“部门号不存在!”。 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(’部门号不存在！’); Else DBMS_OUTPUT.PUT_LINE(v_avgsal)); End if; END; /

（9） 创建一个函数，以员工号为参数，返回该员工所在部门的平

均工资。

CREATE OR REPLACE FUNCTION

RET_AVGSAL_EMPNO(p_empno emp.empno%type) RETURN emp.sal%type AS

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 deptno=v_deptno;

RETURN v_avgsal;

EXCEPTION

WHEN NO_DATA_FOUND THEN RETURN -1;

END;

/

（10）编写一个PL/SQL块调用（9）中创建的函数。如果不存在该

部门，则显示“员工号不存在!”。

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 then

DBMS_OUTPUT.PUT_LINE(‘员工号不存在！’);

Else

DBMS_OUTPUT.PUT_LINE(v_avgsal);

End if;

END; /

（11）创建一个存储过程，以一个整数为参数，输出工资最高的前

几个(参数值)员工的姓名。

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）创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工姓名。

CREATE OR REPLACE PROCEDURE min_max(p_m int,p_n int) AS

v_i int:=1; BEGIN

for v_emp in (SELECT * FROM emp where sal between p_m and p_n ORDER BY sal) loop

DBMS_OUTPUT.PUT_LINE(v_emp.ename||' '||v_emp.sal); end loop; END; /

end loop; END; /

（12）创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工姓名。

CREATE OR REPLACE PROCEDURE min_max(p_m int,p_n int) AS

v_i int:=1; BEGIN

for v_emp in (SELECT * FROM emp where sal between p_m and p_n ORDER BY sal) loop

DBMS_OUTPUT.PUT_LINE(v_emp.ename||' '||v_emp.sal); end loop; END; /

本文来源：https://www.bwwdw.com/article/qnv5.html