答案实验五: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; /