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无法成功运行
原因：
表或视图不存在
解决方案:
建立表或视图
升级会员 