《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; /