以下用了三重循环,效率差的惊人,求大虾优化
i := 0;
j := 0;
k := 0;
while i < 3 loop
i := i + 1;
j := 0;
while j < 7 loop
j := j + 1;
k := 0;
while k < 3 loop
k := k + 1;
select count(1)
into l_count
from gistempctp c
where c.knumber = l_k
and c.lnumber = i
and c.mnumber = j
and c.nnumber = k
and c.endcode = v_aendcode;
if (l_count = 0) then
dosomething();//插入数据 else
select id_in_nm
into l_actpid
from gistempctp c
where c.knumber = l_k
and c.lnumber = i
and c.mnumber = j
and c.nnumber = k
and c.endcode = v_aendcode;
end if;
select count(1)
into l_count
from gistempctp c
where c.knumber = l_k
and c.lnumber = i
and c.mnumber = j
and c.nnumber = k
and c.endcode = v_zendcode;
if (l_count = 0) then
dosomething();//插入数据
else
select id_in_nm
into l_zctpid
from gistempctp c
where c.knumber = l_k
and c.lnumber = i
and c.mnumber = j
and c.nnumber = k
and c.endcode = v_zendcode;
end if;
l_count := 0;
select count(1)
into l_count
from channel
where AENDID = l_actpid
and zendid = l_zctpid;
if (l_count = 0) then
dosomething();//插入数据
end if;
end loop;
end loop;
end loop;
i := 0;
j := 0;
k := 0;
while i < 3 loop
i := i + 1;
j := 0;
while j < 7 loop
j := j + 1;
k := 0;
while k < 3 loop
k := k + 1;
select count(1)
into l_count
from gistempctp c
where c.knumber = l_k
and c.lnumber = i
and c.mnumber = j
and c.nnumber = k
and c.endcode = v_aendcode;
if (l_count = 0) then
dosomething();//插入数据 else
select id_in_nm
into l_actpid
from gistempctp c
where c.knumber = l_k
and c.lnumber = i
and c.mnumber = j
and c.nnumber = k
and c.endcode = v_aendcode;
end if;
select count(1)
into l_count
from gistempctp c
where c.knumber = l_k
and c.lnumber = i
and c.mnumber = j
and c.nnumber = k
and c.endcode = v_zendcode;
if (l_count = 0) then
dosomething();//插入数据
else
select id_in_nm
into l_zctpid
from gistempctp c
where c.knumber = l_k
and c.lnumber = i
and c.mnumber = j
and c.nnumber = k
and c.endcode = v_zendcode;
end if;
l_count := 0;
select count(1)
into l_count
from channel
where AENDID = l_actpid
and zendid = l_zctpid;
if (l_count = 0) then
dosomething();//插入数据
end if;
end loop;
end loop;
end loop;
cursor cur_sel is
select count(1)
from gistempctp c
where c.knumber = l_k
and c.lnumber > 0 and c.lnumber < 3
and c.mnumber > 0 and c.mnumber < 7
and c.nnumber > 0 and c.nnumber < 3
and c.endcode = v_aendcode;
我建议你对:
gistempctp表的knumber、lnumber、mnumber、nnumber 和endcode创建索引
channel表的zendid创建索引试试下效果咋样。
and c.mnumber > 0 and c.mnumber < 7
and c.nnumber > 0 and c.nnumber < 3不是把你循环里的所有可能值都取到了吗?
还要循环干什么?