问题：在使用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;
create or replace procedure proc_test_cur(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_cur(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_cur','测试'||var,v_begin_time,v_end_time);
commit;
exception
when others then
v_err_code:=sqlcode;
v_err_txt:='测试cur:'||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;
C、
----10次
begin
proc_test_max(10);
end;
/
begin
proc_test_cur(10);
end;
----100次
begin
proc_test_max(100);
end;
/
begin
proc_test_cur(100);
end;
---1000次
begin
proc_test_max(1000);
end;
/
begin
proc_test_cur(1000);
end;
---10000次
begin
proc_test_max(10000);
end;
/
begin
proc_test_cur(10000);
end;
----100000次
begin
proc_test_max(100000);
end;
/
begin
proc_test_cur(100000);
end;