Oracle实验四下PLSQL的游标和异常.docx

1、Oracle实验四下PLSQL的游标和异常数据库开发技术实验4下 报告实验题目：PL_SQL的游标和异常日期2015 11 - 21班级计算机1301姓名实验环境：Win10 +Oracle11g一、实验内容与完成情况(记录所有的实验过程)：1、编写匿名PL/SQL程序块，根据用户输入的部门编号实现在PL/SQL中逐行显示emp表中该部门员工的工资级别。工资级别是：当工资为空时，为空，工资在1000元以下的为低，在1000和3000之间的为中，高于3000元的为高。要求使用参数化游标。运行过程和效果如下：输入 dept_id 的值: 10原值 9: v_deptno:= &dept_id;新值

2、 9: v_deptno:= 10;7782,CLARK,2450,中7839,KING,5000,高7934,MILLER,1300,中PL/SQL 过程已成功完成。SQL语句set serveroutput ondeclare v_empno emp.empno%type; v_ename emp.ename%type; v_sal emp.sal%type; v_deptno emp.deptno%type := & deptno_id; v_grade varchar2(20); cursor emp_cursor(emp_deptno emp.deptno%type) is sele

3、ct empno,ename,sal from emp where deptno = emp_deptno;beginopen emp_cursor(v_deptno);loop fetch emp_cursor into v_empno,v_ename,v_sal; exit when emp_cursor%notfound; begin if v_sal = 1000 and v_sal 3000 then v_grade := 高; end if; if v_sal is null then v_grade := ; end if; dbms_output.put_line(v_empn

4、o|,|v_ename|,|v_sal|,|v_grade); end;end loop;close emp_cursor;end;/实验结果截图2、创建一个PL/SQL块，要求根据用户输入的员工编号（EMPNO），查询出EMP表中该编号员工所在的部门编号(deptno)及其直接管理者的姓名(ename)，要有异常处理(该员工编号不存在时或者该员工是总裁无直接主管时)。查询效果形如下：输入 员工编号 的值: 7788原值 6: v_empno:= &员工编号;新值 6: v_empno:= 7788;员工编号 部门编号 上司姓名-7788 20 JONESPL/SQL 过程已成功完成。或者：输

5、入 员工编号 的值: 8001原值 6: v_empno:= &员工编号;新值 6: v_empno:= 8001;该员工不存在！PL/SQL 过程已成功完成。或者：输入 员工编号 的值: 7839原值 7: v_empno:= &员工编号;新值 7: v_empno:= 7839;该员工是总裁，无直接主管！PL/SQL 过程已成功完成。SQL语句set serveroutput ondeclare v_deptno emp.deptno%type; v_ename emp.ename%type; v_empno emp.empno%type := &员工编号; v_mgr emp.mgr%t

6、ype; e_null exception; -声明异常 e_mgr exception; -声明异常 cursor emp_cursor(emp_empno emp.empno%type) is select deptno,mgr from emp where empno = emp_empno;beginopen emp_cursor(v_empno);fetch emp_cursor into v_deptno,v_mgr;begin if emp_cursor%notfound then raise e_null; -显式抛出异常 end if; if v_mgr is null th

7、en raise e_mgr; -显式抛出异常 end if; select emp2.ename into v_ename from emp emp1,emp emp2 where emp1.mgr = emp2.empno and emp1.empno = v_empno; dbms_output.put_line(员工编号 部门编号 上司姓名); dbms_output.put_line(-); dbms_output.put_line(v_empno| |v_deptno| |v_ename);Exception when e_null then -处理异常 dbms_output.p

8、ut_line(此员工不存在!); commit; when e_mgr then -处理异常 dbms_output.put_line(该员工是总裁，无直接主管！); commit;end;close emp_cursor;end;/实验结果截图 3、*创建一个PL/SQL块，根据输入的部门编号，逐条输出EMP表中该部门每位员工的序号、编号(empno)、姓名(ename)和工资(sal)信息，要求用游标实现（参见教材P244例10.43）。信息显示格式要求如下：输入 dept_id 的值: 10原值 7: v_deptno:= &dept_id;新值 7: v_deptno:= 10;序号

9、 编号 姓名 工资1 7782 CLARK 24502 7839 KING 50003 7934 MILLER 1300PL/SQL 过程已成功完成。SQL语句set serveroutput ondeclare v_deptno emp.deptno%type := &部门编号; v_ename emp.ename%type; v_empno emp.empno%type; v_sal emp.sal%type; v_count number := 1; cursor emp_cursor(emp_deptno emp.deptno%type) is select empno,ename,s

10、al from emp where deptno = emp_deptno;beginopen emp_cursor(v_deptno);dbms_output.put_line(序号 编号 姓名 工资);loopfetch emp_cursor into v_empno,v_ename,v_sal;exit when emp_cursor%notfound; begin dbms_output.put_line(v_count| |v_empno| |v_ename| |v_sal); v_count := v_count + 1; end;end loop;close emp_cursor

11、;end;/实验结果截图4、创建一个PL/SQL块，每输出DEPT表的一条记录（DEPTNO,DNAME,LOC）后，随后输出该部门的员工记录（EMPNO,ENAME,HIREDATE,SAL），输出格式形如下：部门编号:10 部门名称:ACCOUNTING 部门位置:NEW YORK-7782 CLARK 06-09-81 $24507839 KING 11-17-81 $50007934 MILLER 01-23-82 $1300部门编号:20 部门名称:RESEARCH 部门位置:DALLAS-7369 SMITH 12-17-80 $8007566 JONES 04-02-81 $29

12、757788 SCOTT 04-19-87 $30007876 ADAMS 05-23-87 $11007902 FORD 12-03-81 $3000部门编号:30 部门名称:SALES 部门位置:CHICAGO-7499 ALLEN 02-20-81 $16007521 WARD 02-22-81 $12507654 MARTIN 09-28-81 $12507698 BLAKE 05-01-81 $28507844 TURNER 09-08-81 $15007900 JAMES 12-03-81 $950部门编号:40 部门名称:OPERATIONS 部门位置:BOSTON-PL/SQL

13、 过程已成功完成。SQL语句set serveroutput ondeclare v_deptno dept.deptno%type; v_dname dept.dname%type; v_loc dept.loc%type; v_empno emp.empno%type; v_ename emp.ename%type; v_hiredate emp.hiredate%type; v_sal emp.sal%type; v_mark number; v_tmp_deptno dept.deptno%type; cursor emp_dept_cursor is select deptno,em

14、pno,ename,hiredate,sal from emp order by deptno;beginv_mark := 1;v_tmp_deptno := 0;open emp_dept_cursor;loopfetch emp_dept_cursor into v_deptno,v_empno,v_ename,v_hiredate,v_sal;exit when emp_dept_cursor%notfound; begin if v_tmp_deptno = v_deptno then v_mark := 1; else v_mark := 0; end if; if v_mark

15、= 1 then dbms_output.put_line(v_empno| |v_ename| |v_hiredate| $|v_sal); else begin select dname,loc into v_dname,v_loc from dept where deptno = v_deptno; v_tmp_deptno := v_deptno; dbms_output.put_line(部门编号:|v_deptno| 部门名称:|v_dname| 部门位置:|v_loc); dbms_output.put_line(-); dbms_output.put_line(v_empno|

16、 |v_ename| |v_hiredate| $|v_sal); end; end if; end;end loop;close emp_dept_cursor;end;/实验结果截图5、*利用REF游标（游标变量）在student表中完成以下组合查询任务: 根据姓名、所在系任意组合查询学员信息。输出格式形如下：输入 name 的值: 王名原值 9: l_name := &Name;新值 9: l_name := 王名;输入 dept 的值: MA原值 10: l_sdept := &dept;新值 10: l_sdept := MA;SELECT * FROM student WHERE

17、1=1 AND sname=王名AND sdept=MA95003, 王名, 女, 18, MAPL/SQL 过程已成功完成。SQL语句set serveroutput ondeclare v_sname student.sname%type := &name; v_sdept student.sdept%type := &dept; type cur_type is ref cursor; cur cur_type; rec student%rowtype;begindbms_output.put_line(select * from student where sname =|v_snam

18、e| and sdept =|v_sdept|);open cur for select * from student where sname = |v_sname| and sdept = | v_sdept|;loop fetch cur into rec.sno,rec.sname,rec.ssex,rec.sage,rec.sdept; exit when cur%notfound; dbms_output.put_line(rec.sno|,|rec.sname|,|rec.ssex|,|rec.sage|,|rec.sdept);end loop;end;/实验结果截图附加题：*1

19、、利用SELECT FOR UPDATE游标和UPDATE语句中的WHERE CURRENT OF子句完成以下任务：把所有年龄小于18岁的学员成绩增加10分。SQL语句DECLARE CURSOR score_cursor ISselect s.sno,sage,grade from student s,stuscore g where s.sno = g.sno and sage 18 FOR UPDATE OF grade NOWAIT;BEGIN FOR score_record IN score_cursor LOOP UPDATE stuscore SET grade = score

20、_record.grade + 10 WHERE CURRENT OF score_cursor; END LOOP; COMMIT;END;/实验结果截图 2、有这么一张表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);

21、insert into t_t values(6);insert into t_t values(5);insert into t_t values(3);insert into t_t values(1);insert into t_t values(1);- 可以通过查看表t_t的值来验证程序的执行情况。Select * from t_t;SQL语句declare v_a number; cursor t_t_cursor is select a from t_t;beginopen t_t_cursor;loopfetch t_t_cursor into v_a;exit when t_t_cursor%notfound; delete from t_t where a = v_a; insert into t_t(a) values(v_a); commit;end loop;end;/实验结果截图执行失败出现的问题：解决方案（列出遇到的主要问题和解决办法，列出没有解决的问题）：问题一：附加题2无法成功运行原因：表或视图不存在 解决方案:建立表或视图