ORA-01403:nodatafoundexception的解决小道

问题：在使用select into from tablename这样的隐式游标的时候，如果查不到数据，那么会报ora-01403的错误。而每次使用显式游标，

问题：在使用select into from tablename这样的隐式游标的时候，如果查不到数据，那么会报ora-01403的错误。而每次使用显式游标，再打开取值就显得很麻烦。一个经验丰富的老师提出可以用类似max min等这样的聚合函数，，来避免select into带来的异常，又避免每次使用游标的麻烦。

效率和可行性测试：

①临时表

CREATE TABLE EDU.TEST
(
ID NUMBER NULL
)
TABLESPACE EDUCATION
NOLOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE

② 用PL/SQL块测试

A、create or replace procedure proc_max(id varchar2)
as
var dba_tables.table_name%type:=null;
v_err_code number(10);
v_err_txt varchar2(400);
begin
select max(d.table_name)
into var
from dba_tables d
where d.OWNER='JF'
and d.TABLE_NAME='ORGCONNECTION1012'||id;
if var is not null
then
insert into test values(id);
commit;
end if;
exception
when others then
v_err_code:=sqlcode;
v_err_txt:='测试:'||sqlerrm||DBMS_UTILITY.format_error_backtrace();
insert into proc_err_logs(code,message,info,occur_date)
values(v_err_code,v_err_txt,'EXCEPTION',SYSDATE);
commit;
end;

/
create or replace procedure proc_cur(id varchar2)
as
var dba_tables.table_name%type:=null;
v_err_code number(10);
v_err_txt varchar2(400);
type cursor_type is ref cursor;
cur cursor_type;
begin
open cur for
select d.table_name
from dba_tables d
where d.OWNER='JF'
and d.TABLE_NAME='ORGCONNECTION1012'||id;

fetch cur into var;
close cur;

if var is not null then
insert into test
values(id);
commit;
end if;
exception
when others then
v_err_code:=sqlcode;
v_err_txt:='测试:'||sqlerrm||DBMS_UTILITY.format_error_backtrace();
insert into proc_err_logs(code,message,info,occur_date)
values(v_err_code,v_err_txt,'EXCEPTION',SYSDATE);
commit;
end;

B、

create or replace procedure proc_test_max(var in number)
as
v_err_code number(10);
v_err_txt varchar2(400);
v_begin_time timestamp;
v_end_time timestamp;

begin
select current_timestamp into v_begin_time from dual;

for i in 1..var loop
proc_max(i);
end loop;
select current_timestamp into v_end_time from dual;

insert into proc_use_times(PROC_NAME,
PROC_DESC,
BEGIN_TIME,
END_TIME)
values('proc_test_max','测试'||var,v_begin_time,v_end_time);
commit;
exception
when others then
v_err_code:=sqlcode;
v_err_txt:='测试max:'||var||sqlerrm||DBMS_UTILITY.format_error_backtrace();
insert into proc_err_logs(code,message,info,occur_date)
values(v_err_code,v_err_txt,'EXCEPTION',SYSDATE);
end;