真的是原创~!我没抄别人的!噼里啪啦,先给自己点掌声。declare
type weak_cursor is ref cursor;
my_c1 weak_cursor;
emp s_emp%rowtype;
dept s_dept%rowtype;
reg s_region%rowtype;
uid number(1);
begin
uid := &x;
case uid
when 1 then
open my_c1 for select * from s_emp;
loop
fetch my_c1 into emp;
exit when my_c1%notfound;
dbms_output.put_line(emp.id||','||emp.first_name);
end loop;
close my_c1;
when 2 then
open my_c1 for select * from s_dept;
loop
fetch my_c1 into dept;
exit when my_c1%notfound;
dbms_output.put_line(dept.id||','||dept.name);
end loop;
close my_c1;
when 3 then
open my_c1 for select * from s_region;
loop
fetch my_c1 into reg;
exit when my_c1%notfound;
dbms_output.put_line(reg.id||','||reg.name);
end loop;
close my_c1;
end case;
end;
扩展问题:
这段,如果输入123之外的数字和不输入,会报错。
那该如何解决呢?
type weak_cursor is ref cursor;
my_c1 weak_cursor;
emp s_emp%rowtype;
dept s_dept%rowtype;
reg s_region%rowtype;
uid number(1);
begin
uid := &x;
case uid
when 1 then
open my_c1 for select * from s_emp;
loop
fetch my_c1 into emp;
exit when my_c1%notfound;
dbms_output.put_line(emp.id||','||emp.first_name);
end loop;
close my_c1;
when 2 then
open my_c1 for select * from s_dept;
loop
fetch my_c1 into dept;
exit when my_c1%notfound;
dbms_output.put_line(dept.id||','||dept.name);
end loop;
close my_c1;
when 3 then
open my_c1 for select * from s_region;
loop
fetch my_c1 into reg;
exit when my_c1%notfound;
dbms_output.put_line(reg.id||','||reg.name);
end loop;
close my_c1;
end case;
end;
扩展问题:
这段,如果输入123之外的数字和不输入,会报错。
那该如何解决呢?
else
dbms_output.put_line('请在1~3选择输入!');
end case;
第 9 行出现错误:
ORA-06550: 第 9 行, 第 10 列:
PLS-00103: 出现符号 ";"在需要下列之一时: ( - + case
mod
new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<一个带有字符集说明的可带引号的字符串文字> <一个可带引号的
SQL 字符串>
符号 "null" 被
你可以给这个变量加个默认值
exception
when others then
dbms_output.put_line('输入错误,必须输入1到3之间的数字!');报同样的错误。
uid := &x;
改为
uid:=nvl('&x',0);