程序更新了 但是似乎 内循环有问题 比如内游标有2条数据的时候 第一条有值但是没被INSERT 每次都是游标的最后一条被INSERT我对游标有点晕了.open cur_Table;
loop --1
fetch cur_Table into v_table;
open cur_test;
loop --2
fetch cur_test into v_a1,v_a2,v_a3,v_a4,v_a5,v_a6,v_a7;
if v_a1 = 1 then
str:= ' insert into ' ||v_table || ' values('||v_a2||','||v_a3||','||v_a4||','||v_a5||','||v_a6||','||v_a7||') ';
else
str:= ' update ' ||v_table || ' set MaxValue='||v_a2||', MinValue='||v_a3||', AvgValue='||v_a4||' where
DayHour='||v_a5||' and X0='||v_a6||' and Y0='||v_a7||' ';
end if;
exit when cur_test%notfound;
end loop;
execute immediate str;
close cur_test;
exit when cur_Table%notfound;
end loop;
close cur_Table;
loop --1
fetch cur_Table into v_table;
open cur_test;
loop --2
fetch cur_test into v_a1,v_a2,v_a3,v_a4,v_a5,v_a6,v_a7;
if v_a1 = 1 then
str:= ' insert into ' ||v_table || ' values('||v_a2||','||v_a3||','||v_a4||','||v_a5||','||v_a6||','||v_a7||') ';
else
str:= ' update ' ||v_table || ' set MaxValue='||v_a2||', MinValue='||v_a3||', AvgValue='||v_a4||' where
DayHour='||v_a5||' and X0='||v_a6||' and Y0='||v_a7||' ';
end if;
exit when cur_test%notfound;
end loop;
execute immediate str;
close cur_test;
exit when cur_Table%notfound;
end loop;
close cur_Table;
loop --1
fetch cur_Table into v_table;
exit when cur_Table%notfound; open cur_test;
loop --2
fetch cur_test into v_a1,v_a2,v_a3,v_a4,v_a5,v_a6,v_a7;
exit when cur_test%notfound;
if v_a1 = 1 then
str:= ' insert into ' ¦ ¦v_table ¦ ¦ ' values(' ¦ ¦v_a2 ¦ ¦',' ¦ ¦v_a3 ¦ ¦',' ¦ ¦v_a4 ¦ ¦',' ¦ ¦v_a5 ¦ ¦',' ¦ ¦v_a6 ¦ ¦',' ¦ ¦v_a7 ¦ ¦') ';
else
str:= ' update ' ¦ ¦v_table ¦ ¦ ' set MaxValue=' ¦ ¦v_a2 ¦ ¦', MinValue=' ¦ ¦v_a3 ¦ ¦', AvgValue=' ¦ ¦v_a4 ¦ ¦' where
DayHour=' ¦ ¦v_a5 ¦ ¦' and X0=' ¦ ¦v_a6 ¦ ¦' and Y0=' ¦ ¦v_a7 ¦ ¦' ';
end if;
end loop;
execute immediate str;
close cur_test;
end loop;
close cur_Table;没有注意看你的内容,但游标的格式有点问题,
应该是fetch完后,马上进行判断,我改了一下,楼主再试一下
我要不再检索时候写个函数判断下好了如果B表有对应条件的值就NOT INSERT ?