如何将动态sql应用到for loop循环中?

[gooce]

定义一个动态sql l_sql2:
l_sql2:='select * from '||definers.stblname||' where '||definers.tpkey||'='||p_value;
通过raise_application_error(-20001,'l_sql2='||l_sql2);可以探测出来l_sql2和预期的sql一样,
现在的问题是如何在for loop循环中使用动态sql l_sql2
for sourcers in (select l_sql2 from dual)
loop
end loop;

我的这种方式已经验证了,是错误的,编译时没问题,执行时会报错,报"ORA-00904: "SOURCERS"."FIELD1": 标识符无效"
其中field1是动态sql执行时的其中一个结果字段
请问,如何将动态sql引用到for loop循环呢?

[真的不知道取什么名]

你这样应该要用游标来处理吧

[gooce]

[Quote=引用 1 楼 zhuomingwang 的回复:]
你这样应该要用游标来处理吧
[/Quote]
用游标的话我不清楚在下面这组具体代码中,sourcers如何使用游标啊?我贴出来,您帮忙看看吧

create or replace procedure proc_checkdata(p_tblname varchar2,p_value number)
is
l_sql1 varchar2(1000);
l_sql2 varchar2(1000);
l_sql3 varchar2(1000);
l_sql4 varchar2(1000);
l_field1 varchar2(100);
l_field2 varchar2(100);

cursor c1 is select stblname,spkey,tpkey from syncdefine_table where tblname=p_tblname;
begin
for definers in c1
loop
l_sql2:='select * from '||definers.stblname||' where '||definers.tpkey||'='||p_value;
for sourcers in (select l_sql2 from dual)
loop
l_sql3:='select * from '||p_tblname||' where '||definers.tpkey||'='||p_value;
for targetrs in (select l_sql3 from dual)
loop
for syncrs in (select field,sfield from syncdefine_field where issync=-1 and tblname=p_tblname)
loop
l_field1:='sourcers.'||syncrs.sfield;
l_field2:='targetrs.'||syncrs.field;
if l_field1<>l_field2 then
l_sql4:='update '||p_tblname||' set '||l_field2||'='||l_field1||' where '||definers.tpkey||'='||p_value;
execute immediate l_sql4;
end if;
null;
end loop;
end loop;
end loop;
end loop;
end;

[gooce]

重新贴一下代码

create or replace procedure proc_checkdata(p_tblname varchar2,p_value number)
is
  l_sql1 varchar2(1000);
  l_sql2 varchar2(1000);
  l_sql3 varchar2(1000);
  l_sql4 varchar2(1000);
  l_field1 varchar2(100);
  l_field2 varchar2(100);
   
  cursor c1 is select stblname,spkey,tpkey from syncdefine_table where tblname=p_tblname;
begin
  for definers in c1
  loop
  l_sql2:='select * from '||definers.stblname||' where '||definers.tpkey||'='||p_value;
for sourcers in (select l_sql2 from dual)
  loop
  l_sql3:='select * from '||p_tblname||' where '||definers.tpkey||'='||p_value;
for targetrs in (select l_sql3 from dual)
loop
for syncrs in (select field,sfield from syncdefine_field where issync=-1 and tblname=p_tblname)
loop
  l_field1:='sourcers.'||syncrs.sfield;
  l_field2:='targetrs.'||syncrs.field;
if l_field1<>l_field2 then
l_sql4:='update '||p_tblname||' set '||l_field2||'='||l_field1||' where '||definers.tpkey||'='||p_value;
  execute immediate l_sql4;
end if;
  null;
end loop;
end loop;
end loop;
end loop;
end;

[wuyisky84]

直接execute immediate 'insert into table_1 as'|| l_sql2;
然后
for c in (select * from table_1 ) loop

end loop;

[gelyon]

楼主是不是动态SQL拼接错了？tpkey是不是字串型的？
l_sql2:='select * from '||definers.stblname||' where '||definers.tpkey||'='''||p_value||'''';