Oracle实验四下PLSQL的游标和异常.docx
《Oracle实验四下PLSQL的游标和异常.docx》由会员分享,可在线阅读,更多相关《Oracle实验四下PLSQL的游标和异常.docx(13页珍藏版)》请在冰点文库上搜索。
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;
新值9:
v_deptno:
=10;
7782,CLARK,2450,中
7839,KING,5000,高
7934,MILLER,1300,中
PL/SQL过程已成功完成。
SQL语句
setserveroutputon
declare
v_empnoemp.empno%type;
v_enameemp.ename%type;
v_salemp.sal%type;
v_deptnoemp.deptno%type:
=&deptno_id;
v_gradevarchar2(20);
cursoremp_cursor(emp_deptnoemp.deptno%type)is
selectempno,ename,salfromempwheredeptno=emp_deptno;
begin
openemp_cursor(v_deptno);
loop
fetchemp_cursorintov_empno,v_ename,v_sal;
exitwhenemp_cursor%notfound;
begin
ifv_sal<1000then
v_grade:
='低';
endif;
ifv_sal>=1000andv_sal<=3000
thenv_grade:
='中';
endif;
ifv_sal>3000
thenv_grade:
='高';
endif;
ifv_salisnull
thenv_grade:
='';
endif;
dbms_output.put_line(v_empno||','||v_ename||','||v_sal||','||v_grade);
end;
endloop;
closeemp_cursor;
end;
/
实验结果截图
2、创建一个PL/SQL块,要求根据用户输入的员工编号(EMPNO),查询出EMP表中该编号员工所在的部门编号(deptno)及其直接管理者的姓名(ename),要有异常处理(该员工编号不存在时或者该员工是总裁无直接主管时)。
查询效果形如下:
输入员工编号的值:
7788
原值6:
v_empno:
=&员工编号;
新值6:
v_empno:
=7788;
员工编号部门编号上司姓名
----------------------------------
778820JONES
PL/SQL过程已成功完成。
或者:
输入员工编号的值:
8001
原值6:
v_empno:
=&员工编号;
新值6:
v_empno:
=8001;
该员工不存在!
PL/SQL过程已成功完成。
或者:
输入员工编号的值:
7839
原值7:
v_empno:
=&员工编号;
新值7:
v_empno:
=7839;
该员工是总裁,无直接主管!
PL/SQL过程已成功完成。
SQL语句
setserveroutputon
declare
v_deptnoemp.deptno%type;
v_enameemp.ename%type;
v_empnoemp.empno%type:
=&员工编号;
v_mgremp.mgr%type;
e_nullexception;--声明异常
e_mgrexception;--声明异常
cursoremp_cursor(emp_empnoemp.empno%type)is
selectdeptno,mgrfromempwhereempno=emp_empno;
begin
openemp_cursor(v_empno);
fetchemp_cursorintov_deptno,v_mgr;
begin
ifemp_cursor%notfoundthen
raisee_null;--显式抛出异常
endif;
ifv_mgrisnullthen
raisee_mgr;--显式抛出异常
endif;
selectemp2.enameintov_ename
fromempemp1,empemp2whereemp1.mgr=emp2.empnoandemp1.empno=v_empno;
dbms_output.put_line('员工编号部门编号上司姓名');
dbms_output.put_line('----------------------------------');
dbms_output.put_line(v_empno||''||v_deptno||''||v_ename);
Exception
whene_nullthen--处理异常
dbms_output.put_line('此员工不存在!
');
commit;
whene_mgrthen--处理异常
dbms_output.put_line('该员工是总裁,无直接主管!
');
commit;
end;
closeemp_cursor;
end;
/
实验结果截图
3、*创建一个PL/SQL块,根据输入的部门编号,逐条输出EMP表中该部门每位员工的序号、编号(empno)、姓名(ename)和工资(sal)信息,要求用游标实现(参见教材P244例10.43)。
信息显示格式要求如下:
输入dept_id的值:
10
原值7:
v_deptno:
=&dept_id;
新值7:
v_deptno:
=10;
序号编号姓名工资
17782CLARK2450
27839KING5000
37934MILLER1300
PL/SQL过程已成功完成。
SQL语句
setserveroutputon
declare
v_deptnoemp.deptno%type:
=&部门编号;
v_enameemp.ename%type;
v_empnoemp.empno%type;
v_salemp.sal%type;
v_countnumber:
=1;
cursoremp_cursor(emp_deptnoemp.deptno%type)is
selectempno,ename,salfromempwheredeptno=emp_deptno;
begin
openemp_cursor(v_deptno);
dbms_output.put_line('序号编号姓名工资');
loop
fetchemp_cursorintov_empno,v_ename,v_sal;
exitwhenemp_cursor%notfound;
begin
dbms_output.put_line(v_count||''||v_empno||''||v_ename||''||v_sal);
v_count:
=v_count+1;
end;
endloop;
closeemp_cursor;
end;
/
实验结果截图
4、创建一个PL/SQL块,每输出DEPT表的一条记录(DEPTNO,DNAME,LOC)后,随后输出该部门的员工记录(EMPNO,ENAME,HIREDATE,SAL),输出格式形如下:
部门编号:
10部门名称:
ACCOUNTING部门位置:
NEWYORK
------------------------------------------------------------
7782CLARK06-09-81$2450
7839KING11-17-81$5000
7934MILLER01-23-82$1300
部门编号:
20部门名称:
RESEARCH部门位置:
DALLAS
------------------------------------------------------------
7369SMITH12-17-80$800
7566JONES04-02-81$2975
7788SCOTT04-19-87$3000
7876ADAMS05-23-87$1100
7902FORD12-03-81$3000
部门编号:
30部门名称:
SALES部门位置:
CHICAGO
------------------------------------------------------------
7499ALLEN02-20-81$1600
7521WARD02-22-81$1250
7654MARTIN09-28-81$1250
7698BLAKE05-01-81$2850
7844TURNER09-08-81$1500
7900JAMES12-03-81$950
部门编号:
40部门名称:
OPERATIONS部门位置:
BOSTON
------------------------------------------------------------
PL/SQL过程已成功完成。
SQL语句
setserveroutputon
declare
v_deptnodept.deptno%type;
v_dnamedept.dname%type;
v_locdept.loc%type;
v_empnoemp.empno%type;
v_enameemp.ename%type;
v_hiredateemp.hiredate%type;
v_salemp.sal%type;
v_marknumber;
v_tmp_deptnodept.deptno%type;
cursoremp_dept_cursoris
selectdeptno,empno,ename,hiredate,salfromemporderbydeptno;
begin
v_mark:
=1;
v_tmp_deptno:
=0;
openemp_dept_cursor;
loop
fetchemp_dept_cursorintov_deptno,v_empno,v_ename,v_hiredate,v_sal;
exitwhenemp_dept_cursor%notfound;
begin
ifv_tmp_deptno=v_deptnothen
v_mark:
=1;
else
v_mark:
=0;
endif;
ifv_mark=1then
dbms_output.put_line(v_empno||''||v_ename||''||v_hiredate||'$'||v_sal);
else
begin
selectdname,locintov_dname,v_locfromdeptwheredeptno=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||''||v_ename||''||v_hiredate||'$'||v_sal);
end;
endif;
end;
endloop;
closeemp_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*FROMstudentWHERE1=1ANDsname='王名'ANDsdept='MA'
95003,王名,女,18,MA
PL/SQL过程已成功完成。
SQL语句
setserveroutputon
declare
v_snamestudent.sname%type:
='&name';
v_sdeptstudent.sdept%type:
='&dept';
typecur_typeisrefcursor;
curcur_type;
recstudent%rowtype;
begin
dbms_output.put_line('select*fromstudentwheresname='''||v_sname||'''andsdept='''||v_sdept||'''');
opencurfor'select*fromstudentwheresname='''||v_sname||'''andsdept='''||v_sdept||'''';
loop
fetchcurintorec.sno,rec.sname,rec.ssex,rec.sage,rec.sdept;
exitwhencur%notfound;
dbms_output.put_line(rec.sno||','||rec.sname||','||rec.ssex||','||rec.sage||','||rec.sdept);
endloop;
end;
/
实验结果截图
附加题:
*
1、利用SELECT…FORUPDATE游标和UPDATE语句中的WHERECURRENTOF子句完成以下任务:
把所有年龄小于18岁的学员成绩增加10分。
SQL语句
DECLARE
CURSORscore_cursorIS
selects.sno,sage,grade
fromstudents,stuscoreg
wheres.sno=g.snoandsage<18
FORUPDATEOFgradeNOWAIT;
BEGIN
FORscore_recordINscore_cursorLOOP
UPDATEstuscore
SETgrade=score_record.grade+10
WHERECURRENTOFscore_cursor;
ENDLOOP;
COMMIT;
END;
/
实验结果截图
2、有这么一张表t_t,他只有一个number(8)的字段a,由于在创建表时忘记设置主键约束,导致表中有很多重复的记录。
请你编写一个程序,将表中重复的记录保留一个,删除其余的。
createtablet_t(anumber(8));
insertintot_tvalues
(1);
insertintot_tvalues(3);
insertintot_tvalues(6);
insertintot_tvalues
(1);
insertintot_tvalues(6);
insertintot_tvalues(5);
insertintot_tvalues(3);
insertintot_tvalues
(1);
insertintot_tvalues
(1);
--可以通过查看表t_t的值来验证程序的执行情况。
Select*fromt_t;
SQL语句
declare
v_anumber;
cursort_t_cursoris
selectafromt_t;
begin
opent_t_cursor;
loop
fetcht_t_cursorintov_a;
exitwhent_t_cursor%notfound;
deletefromt_twherea=v_a;
insertintot_t(a)values(v_a);
commit;
endloop;
end;
/
实验结果截图
执行失败
出现的问题:
解决方案(列出遇到的主要问题和解决办法,列出没有解决的问题):
问题一:
附加题2无法成功运行
原因:
表或视图不存在
解决方案:
建立表或视图