Oracle 数据库上机试验指导5(答案)
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验五代码参考：
5. A) create table d_student as select * from student where 1=0;
B) declare
cursor stud_cur
is select studid from student;
sstudid student.studid%type;
begin
open stud_cur;
loop
fetch stud_cur into sstudid;
exit when stud_cur%notfound;
case sstudid
when '100001' then
insert into d_student values('LB_100001','李白','男','和平区');
when '100002' then
insert into d_student values('DF_100002','杜甫','男','河西区');
when '100003' then
insert into d_student values('LQZ_100003','李清照','女','河北区');
when '100004' then
insert into d_student values('CY_100004','蔡琰','女','红桥区');
else
dbms_output.put_line('error');
end case;
end loop;
close stud_cur;
exception
when CASE_NOT_FOUND then
dbms_output.put_line('没有发现匹配选项');
when NO_DATA_FOUND then
dbms_output.put_line('没有发现数据');
when TOO_MANY_ROWS then
dbms_output.put_line('返回数据过多');
end;
/
col studid format a12;
col studname format a12;
col addr format a12;
set pagesize 16;
select * from d_student;
C) create or replace function GetCodeByName(v_name in varchar2)
return varchar2 is
v_idprex char(16);
begin
v_idprex := '';
case v_name
when '李白' then
v_idprex := 'LB_';
when '杜甫' then
v_idprex := 'DF_';
when '李清照' then
v_idprex := 'LQZ_';
when '蔡琰' then
v_idprex := 'CY_';
else
dbms_output.put_line('error');
end case;
return v_idprex;
exception
when CASE_NOT_FOUND then
dbms_output.put_line('没有发现匹配选项');
when NO_DATA_FOUND then
dbms_output.put_line('没有发现数据');
when TOO_MANY_ROWS then
dbms_output.put_line('返回数据过多');
end;
/
select GetCodeByName(studname) from d_student where studname='李白';
D) set serveroutput on;
create or replace procedure stud_pro
as
cursor stud_cur
is select studid from student;
sstudid student.studid%type;
begin
open stud_cur;
loop
fetch stud_cur into sstudid;
exit when stud_cur%notfound;
case sstudid
when '100001' then
insert into d_student values('LB_100001','李白','男','和平区');
when '100002' then
insert into d_student values('DF_100002','杜甫','男','河西区');
when '100003' then
insert into d_student values('LQZ_100003','李清照','女','河北区');
when '100004' then
insert into d_student values('CY_100004','蔡琰','女','红桥区');
else
dbms_output.put_line('error');
end case;
end loop;
close stud_cur;
exception
when CASE_NOT_FOUND then
dbms_output.put_line('没有发现匹配选项');
when NO_DATA_FOUND then
dbms_output.put_line('没有发现数据');
when TOO_MANY_ROWS then
dbms_output.put_line('返回数据过多');
end;
/
exec stud_pro;
col studid format a12;
col studname format a12;
col addr format a12;
set pagesize 16;
select * from d_student;
E) select * from d_student;。