关于存储过程! 高分求助!

[Joy_yu]

我写了这样一个存储过程:
create or replace procedure test is
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;

因为表名为动态变量,所以采用直接execute的方式,但是无论查询条件为何,都不会捕捉到no_data_found异常,请高手告诉我如何实现动态表名的select查询,并可以捕获到no_data_found异常.谢谢!

[supkim]

改成这样试试
你要声明异常

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);

[Lastdrop]

create or replace procedure test is
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知道是否有记录符合条件。

[beckhambobo]

execute immediate语句执行查询时，多加一个into
....
is
num number;
....
begin
.....
execute immediate str into num;

[Joy_yu]

no_data_found 是系统已有的,既然没有捕获,肯定也不会调到自定义的异常情况,

[hucy_2000]

create or replace procedure test is
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;

[Joy_yu]

太感谢楼上的beckhambobo(beckham) ,高手!

[Joy_yu]

值得一提的事,如果改为
select id into ncount from '||var||' where id = ''098''
execute immediate str into var;
这样可以触发到no_data_found异常!!!