关于处理execute immediate的问题

[有情男]

我写了一个存储过程
全部代码如下
============================================================================


create or replace procedure check_person(in_aae017 in varchar2, --查询被合并人员操作人所在机构代码
in_aac001 in varchar2, --将被合并人员内码
msg out varchar2) is
v_aae017 varchar2(20);
v_aac002 varchar2(18);
v_aac003 varchar2(18);
v_aab300 varchar2(60);
v_bunes varchar2(2000);
v_tname varchar2(2000);
v_sql_str varchar2(2000);
cursor t_name is
select table_name
from user_tab_cols
where column_name = 'AAC001'
and (length(table_name) = 4 or length(table_name) = 5)
and table_name not like 'S%' and table_name not like '%AC01%';
begin
msg := null;
v_aae017 :=null;
open t_name;
loop
fetch t_name
into v_tname;
exit when t_name%notfound;
v_sql_str := 'select aae017 from ' || v_tname || ' where aac001 = '''||
in_aac001||'''';
execute immediate v_sql_str into v_aae017;
if v_aae017 is not null and v_aae017 <> in_aae017 then
select comments
into v_bunes
from user_tab_comments
where table_name = v_tname;

select aab300 into v_aab300 from sc01 where bsc001 = v_aae017;
msg := msg || ' ' || v_aab300 || ' ' ||
substr(v_bunes, 0, length(v_bunes) - 4);
end if;
end loop;
if msg is not null then
select aac002, aac003
into v_aac002, v_aac003
from ac01
where aac001 = in_aac001;
msg := v_aac002 || ' ' || v_aac003 ||
' 在其他地区存在以下业务数据,请与当地业务科室联系确认之后再做人员合并! ' || msg;
end if;
return;
EXCEPTION
WHEN OTHERS THEN
begin
msg := sqlerrm;
return;
end;
end check_person;



========================================================

但是现在出现了一个问题我不知道怎么解决
就是
execute immediate v_sql_str into v_aae017;这里
一旦动态执行的sql语句查询结果为空，这个赋值就会报错说没有数据
要想让程序正常运行
应该怎么改进呢
求高手指点~！！！

[hanzs]

execute immediate v_sql_str into v_aae017;
--改成：
begin
  execute immediate v_sql_str into v_aae017;
exception
  when others then
    v_aa3017 := '';
end;

[boyofcity]

[Quote=引用楼主 lwlmj2008 的回复:]
我写了一个存储过程
全部代码如下
============================================================================


create or replace procedure check_person(in_aae017 in varchar2, --查询被合并人员操作人所在机构代码
……
[/Quote]

GOOD

[有情男]

这是我结合2楼的建议后修改过的代码


=========================================================================


create or replace procedure check_person(
in_aae017 in varchar2, --查询被合并人员操作人所在机构代码
in_aac001 in varchar2, --将被合并人员内码
msg out varchar2) is
v_aae017 varchar2(20);
v_aac002 varchar2(18);
v_aac003 varchar2(18);
v_aab300 varchar2(60);
v_aab003 varchar2(18);
v_bunes varchar2(2000);
v_tname varchar2(2000);
v_sql_str varchar2(2000);
cursor t_name is
select table_name
from user_tab_cols
where column_name = 'AAC001'
and (length(table_name) = 4 or length(table_name) = 5)
and table_name not like 'S%'
and table_name not like '%AC01%';
--AC01表是人员基础信息表,AAC001是该表的主键也是各业务表需要引用的外键字段
--AAC002和AAC003分别表示人员的身份证号码和姓名
begin
msg := null;
v_aae017 := null;
open t_name;
loop
fetch t_name
into v_tname;
exit when t_name%notfound;
v_sql_str := 'select aae017 from ' || v_tname || ' where aac001 = ''' ||
in_aac001 || '''';
begin
execute immediate v_sql_str
into v_aae017;
exception
when others then
v_aae017 := null;
end;

if v_aae017 is not null and v_aae017 <> in_aae017 then
select comments
into v_bunes
from user_tab_comments
where table_name = v_tname;
select aab003 into v_aab003 from sc01 where bsc001 = v_aae017;
select aab300
into v_aab300
from sc01
where aab003 = (case when length(v_aab003) > 6 then
substr(v_aab003, 0, 4) || '01' else v_aab003 end);
if v_bunes is not null then
if v_bunes like '%意愿%' then
v_bunes := '就业培训报名';
end if;
msg := msg || ' ' || v_aab300 || ' ' || (case when v_bunes like '%$%' then substr(v_bunes, 0, length(v_bunes) - 4) else v_bunes end);
end if;
end if;
end loop;
if msg is not null then
begin
select aac002, aac003
into v_aac002, v_aac003
from ac01
where aac001 = in_aac001;
exception
when others then
v_aac002 := null;
v_aac003 := null;
end;
msg := (case when v_aac002 is not null and v_aac003 is not null then v_aac002 || ' ' || v_aac003 || ' 在其他地区存在以下业务数据,请与当地业务科室联系确认之后再做人员合并! ' || msg else '系统中不存在该人员的基础信息，请核实！' end);
end if;
return;
EXCEPTION
WHEN OTHERS THEN
begin
msg := sqlerrm;
return;
end;
end check_person;