《数据库(oracle 10g)开发技术》课程实验及参考答案

一、 实验目的

《数据库开发技术》课程之实验五

PL/SQL 的游标和异常处理

1. 掌握游标和参数化游标的使用方法。

2. 掌握游标 FOR 循环、SELECT…FOR UPDATE 游标和 WHERE CURRENT OF 子句的

使用方法。

3. 实验内容和要求

1. 编写匿名 PL/SQL 程序块，根据用户输入的部门编号实现在 PL/SQL 中逐行显示 emp 表中该部门员

工的工资级别。工资级别是：当工资为空时，为空，工资在 1000 元以下的为‘低’，在 1000 和 3000

之间的为‘中’，高于 3000 元的为‘高’。要求使用参数化游标。

declare

v_deptno emp.deptno%type;

cursor c_emp(cp_deptno emp.deptno%type)

is select empno,sal from emp where deptno=cp_deptno;

v_emp c_emp%rowtype;

v_lev varchar2(6);

begin

v_deptno:= &dept_id

open c_emp(v_deptno); –还可采用游标 FOR 循环等其他方法

loop

fetch c_emp into v_emp;

exit when c_emp%notfound;

if v_emp.sal is null then

v_lev:=null;

elsif v_emp.sal<1000 then

v_lev:=’低’;

elsif v_emp.sal>=1000 and v_emp.sal<3000 then

v_lev:=’中’;

elsif v_emp.sal>=3000 then

v_lev:=’高’;

end if;

dbms_output.put_line(v_emp.empno||’,’||v_emp.sal||’,’||v_lev);

end loop;

close c_emp;

end;

2. 创建一个 PL/SQL 块，要求根据用户输入的员工编号（EMPNO），查询出 EMP 表中该编号员工所

在的部门编号(deptno)及其直接管理者的姓名(ename)，要有异常处理(该员工编号不存在)。查询效

果形如下：

declare

v_empno emp.empno%type;

v_deptno emp.deptno%type;

v_mgrname emp.ename%type;

begin

v_empno:= &员工编号;

select e.deptno, m.ename into v_deptno, v_mgrname

from emp e left outer join emp m on e.mgr=m.empno –这里使用自身外连接确保无主管的员工也能正常处理

where e.empno=v_empno;

dbms_output.put_line(‘员工编号’||lpad(‘ ‘,5)||’部门编号’||lpad(‘ ‘,5)||’上司姓名’);

dbms_output.put_line(lpad(‘-‘,34,’-‘));

dbms_output.put_line(v_empno||lpad(‘ ‘,10)||v_deptno||lpad(‘ ‘,10)| v_mgrname);

EXCEPTION

when no_data_found then

dbms_output.put_line(‘该员工不存在！’);

end;

或者

declare

v_empno emp.empno%type;

no_exist_empno exception;

v_deptno emp.deptno%type;

v_mgrname emp.ename%type;

begin

v_empno:= &员工编号;

update emp set sal=sal where empno=v_empno;

if SQL%rowcount=0 then

raise no_exist_empno; –显式抛出用户自定义的异常

end if;

select e.deptno, m.ename into v_deptno, v_mgrname

from emp e, emp m

where e.empno=v_empno and e.mgr=m.empno;

dbms_output.put_line(‘员工编号’||lpad(‘ ‘,5)||’部门编号’||lpad(‘ ‘,5)||’上司姓名’);

dbms_output.put_line(lpad(‘-‘,34,’-‘));

dbms_output.put_line(v_empno||lpad(‘ ‘,10)| v_deptno||lpad(‘ ‘,10)||v_mgrname);

EXCEPTION

when no_exist_empno then

dbms_output.put_line(‘该员工不存在！’);

when no_data_found then

dbms_output.put_line(‘该员工是总裁，无直接主管！’);

end;

或者

declare

v_empno emp.empno%type;

no_exist_empno exception;

cursor c_emp(cp_empno emp.empno%type)

is select e.empno, e.deptno, m.ename

from emp e left outer join emp m on e.mgr=m.empno

where e.empno=cp_empno;

v_emp c_emp%rowtype;

begin

v_empno:= &emp_id;

open c_emp(v_empno);

fetch c_emp into v_emp;

if c_emp%notfound then

raise no_exist_empno;

end if;

dbms_output.put_line(‘员工编号’||lpad(‘ ‘,5)||’部门编号’||lpad(‘ ‘,5)||’上司姓名’);

dbms_output.put_line(lpad(‘-‘,34,’-‘));

dbms_output.put_line(v_emp.empno||lpad(‘ ‘,10)||v_emp.deptno||lpad(‘ ‘,10)| v_emp.ename);

close c_emp;

EXCEPTION

when no_exist_empno then

dbms_output.put_line(‘该员工不存在！’);

end;

3. *创建一个 PL/SQL 块，根据输入的部门编号，逐条输出 EMP 表中该部门每位员工的编号(empno)、

姓名(ename)和工资(sal)信息，要求用游标实现，信息显示格式要求如下：

编号

姓名

工资

declare

7396

PETER 1500.00

v_deptno emp.deptno%type := &dept_id;

cursor c_emp(cp_deptno emp.deptno%type)

is select empno, ename, sal from emp where deptno=cp_deptno;

v_emp c_emp%rowtype;

begin

if not c_emp%isopen then

open c_emp(v_deptno);

end if;

dbms_output.put_line(‘编号’| lpad(‘ ‘,10)||’姓名’||lpad(‘ ‘,10)||’工资’);

loop

fetch c_emp into v_emp;

exit when c_emp%notfound;

dbms_output.put_line(v_emp.empno||lpad(‘ ‘,10)||rpad(v_emp.ename,10)||lpad(‘ ‘,4)| v_emp.sal);

end loop;

close c_emp;

end;

4. 创建一个 PL/SQL 块，每输出 DEPT 表的一条记录（DEPTNO,DNAME,LOC）后，随后输出该部门

的员工记录（EMPNO,ENAME,HIREDATE,SAL），输出格式形如下：

部门编号:10 部门名称:SALES

部门位置:DALAS

――――――――――――――――――――――――――――

7369

KING

05-12-87 $1500

7135 PETER 03-22-89 $1100

……………………

部门编号:20 部门名称:PROGRAM 部门位置: BOSTON

――――――――――――――――――――――――――――

7029

JOHN

05-12-87 $1200

BEGIN

……………………

FOR dept_rec in (select * from dept) loop

dbms_output.put_line(‘部门编号:’|| dept_rec.deptno||lpad(‘ ‘,5)

||’部门名称:’||rpad(dept_rec.dname,10)||lpad(‘ ‘,5)||’部门位置:’| dept_rec.loc);

dbms_output.put_line(lpad(‘-‘,58,’-‘));

FOR emp_rec in (select empno, ename, to_char(hiredate, ‘mm-dd-yy’) HD, to_char(sal, ‘$99999’) salary

from emp Where deptno= dept_rec.deptno) LOOP

dbms_output.put_line(emp_rec.empno||lpad(‘ ‘,10)||rpad(emp_rec.ename,10)||lpad(‘ ‘,8)

|| emp_rec.HD||lpad(‘ ’,8)||emp_rec.salary);

END loop;

END;

5. *有这么一张表 t_t，他只有一个 number(8)的字段 a，由于在创建表时忘记设置主键约束，导致表中

有很多重复的记录。请你编写一个程序，将表中重复的记录保留一个，删除其余的。

create table t_t(a number(8));

insert into t_t values(1);

insert into t_t values(3);

insert into t_t values(6);

insert into t_t values(1);

insert into t_t values(6);

insert into t_t values(5);

insert into t_t values(3);

insert into t_t values(1);

declare

cursor c_t is

select distinct a from t_t ;

v_t c_t%rowtype;

begin

open c_t;

delete from t_t;

loop

fetch c_t into v_t;

exit when c_t%notfound;

dbms_output.put_line(to_char(v_t.a));

insert into t_t values(v_t.a);

end loop;

commit;

close c_t;

end;

— 可以通过查看表 t_t 的值来验证程序的执行情况。

Select * from t_t;

6. *利用 REF 游标（游标变量）完成以下组合查询任务: 根据姓名、所在系任意组合查询学员信息。

DECLARE

l_name student.sname%TYPE;

l_sdept student.sdept%TYPE;

l_sql VARCHAR2(200);

type cur_type IS ref cursor; — 以下两句可以用这句 refCur SYS_REFCURSOR 代替;

cur cur_type;

stuRec student%ROWTYPE;

BEGIN

l_name := ‘&Name’;

l_sdept := ‘&dept’;

l_sql := ‘SELECT * FROM student WHERE 1=1 ‘;

IF l_name IS NOT NULL THEN

l_sql := l_sql || ‘AND sname=”’||l_name||””;

END IF;

IF l_sdept IS NOT NULL THEN

l_sql := l_sql || ‘AND sdept=”’||l_sdept||””;

END IF;

DBMS_OUTPUT.PUT_LINE(l_sql);

OPEN cur FOR l_sql;

LOOP

FETCH cur INTO stuRec;

EXIT WHEN cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(stuRec.sname);

END LOOP;

CLOSE cur;

END;

附加题：*

利用 SELECT … FOR UPDATE 游标和 UPDATE 语句中的 WHERE CURRENT OF 子句完成以下任务：

把所有年龄小于 18 岁的学员成绩增加 10 分。

DECLARE

tempScore NUMBER;

l_score NUMBER;

l_name VARCHAR2(50);

CURSOR stuCur IS

SELECT a.score , b.stuName FROM stuScore a,stuInfo b

WHERE a.stuId = b.stuId AND b.stuAge < 18 FOR UPDATE OF a.score;

BEGIN

OPEN stuCur;

LOOP

FETCH stuCur INTO l_score,l_name;

EXIT WHEN stuCur%NOTFOUND;

tempScore := l_score + 10;

IF l_score >= 90 THEN

tempScore := 99;

END IF;

UPDATE stuScore SET score = tempScore

WHERE CURRENT OF stuCur;

DBMS_OUTPUT.PUT_LINE(‘已经更新:’||l_name);

END LOOP;

CLOSE stuCur;

COMMIT;

END;

二、 实验报告

根据实验内容要求写出相应的 PL/SQL 程序，并记录实验过程中遇到的困难和解决问

题的方法。(实验报告中不要求写加*题目和附加题的实验过程和内容)

《数据库(oracle 10g)开发技术》课程实验及参考答案

还没人围观.

还没有引用.

ORACLE常用傻瓜問題1000問(全集)

oracle期末复习资料 A

数据库（oracle）开发技术复习题纲（2009-2010二）

Oracle期末复习自测题

说说这几天的事

开发之友