用游标的话我不清楚在下面这组具体代码中,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;
重新贴一下代码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;
直接execute immediate 'insert into table_1 as'|| l_sql2; 然后 for c in (select * from table_1 ) loopend loop;
楼主是不是动态SQL拼接错了?tpkey是不是字串型的? l_sql2:='select * from '||definers.stblname||' where '||definers.tpkey||'='''||p_value||'''';
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;
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;
然后
for c in (select * from table_1 ) loopend loop;
l_sql2:='select * from '||definers.stblname||' where '||definers.tpkey||'='''||p_value||'''';