改成这样试试
你要声明异常 E_NO_DATA_FOUND EXCEPTION;然后还要触发异常 RAISE E_NO_DATA_FOUND;在最后加下下面这条语句再试试,when others then
v_error_code :=SQLCODE;
V_ERROR_TEXT :=SUBSTR(SQLERRM, 1, 200);
dbms_output.put_line(v_error_code ||'---------'||v_error_text);
你要声明异常 E_NO_DATA_FOUND EXCEPTION;然后还要触发异常 RAISE E_NO_DATA_FOUND;在最后加下下面这条语句再试试,when others then
v_error_code :=SQLCODE;
V_ERROR_TEXT :=SUBSTR(SQLERRM, 1, 200);
dbms_output.put_line(v_error_code ||'---------'||v_error_text);
var varchar2(20);
str varchar2(200);
begin
begin
var:='USER_INFO';
str := 'select count(*) from '||var||' where id = ''098''';
execute immediate str;
exception
when no_data_found then
var:='ddd';
end;
end test;改为
create or replace procedure test is
var varchar2(20);
str varchar2(200);
begin
begin
var:='USER_INFO';
str := 'select * from '||var||' where id = ''098''';
execute immediate str;
exception
when no_data_found then
var:='ddd';
end;
end test;count(*)无论如何都是会有结果返回的(可以是0),因而也不会触发异常,不过存储过程中一般不直接调用select语句,动态SQL可以用动态游标,然后通过fetch知道是否有记录符合条件。
....
is
num number;
....
begin
.....
execute immediate str into num;
var varchar2(20);
str varchar2(200);
ncount number;
begin
begin
var:='USER_INFO';
str := 'select count(*) into ncount from '||var||' where id = ''098''';
execute immediate str;
exception
when no_data_found then
var:='ddd';
when others then
var:='add'
end;
end test;
select id into ncount from '||var||' where id = ''098''
execute immediate str into var;
这样可以触发到no_data_found异常!!!