create or replace procedure p_test(p_id number)
as
cursor cur(id1 number) is
select distinct c.id from adm_contract_tab c, adm_adplan_tab a, adm_adplanitem_tab i, adm_adresource_tab r, adm_adlocation_list_view_old v
where c.id = a.contract_id and c.state='confirm' and a.id = i.adplan_id and i.adresource_id = r.id and r.adlocation_id = v.eid
and v.aid=id1;
v_id number(10);
v_vedioTotalPrice number(20,10);
v_totalPrice number(20,10);
v_vedio_percent number(20,10);
cnt number(10);
begin
cnt:=0;
open cur(p_id) ;
loop
fetch cur
into v_id;
exit when cur%notfound;
select
nvl(sum(api.adresource_spell_times * api.flux * api.modified_price * (apid.end_date-apid.begin_date+1)),0) into v_vedioTotalPrice
from
adm_adplan_tab ap,adm_adplanitem_tab api, adm_adplanitemdate_tab apid, adm_adlocation_list_view v, adm_adresource_tab r
where
ap.contract_id=v_id
and ap.id = api.adplan_id
and api.id = apid.adplanitem_id
and api.adresource_id = r.id
and r.adlocation_id = v.eid
and v.aid=p_id;
select
nvl(sum(api.adresource_spell_times * api.flux * api.modified_price * (apid.end_date-apid.begin_date+1)),0) into
v_totalPrice
from
adm_adplan_tab ap,adm_adplanitem_tab api, adm_adplanitemdate_tab apid
where
ap.contract_id=v_id
and ap.id = api.adplan_id
and api.id = apid.adplanitem_id;
if v_vedioTotalPrice=0 or v_totalPrice=0 then
v_vedio_percent:=0;
else
v_vedio_percent:=v_vedioTotalPrice/v_totalPrice;
end if;
cnt:=cnt+1;
update adm_contract_tab set is_vedio='yes', vedio_percent=v_vedio_percent where id=v_id;
if mod(cnt,1000)=0 then
commit;
end if;
end loop;
commit;
end ;
as
cursor cur(id1 number) is
select distinct c.id from adm_contract_tab c, adm_adplan_tab a, adm_adplanitem_tab i, adm_adresource_tab r, adm_adlocation_list_view_old v
where c.id = a.contract_id and c.state='confirm' and a.id = i.adplan_id and i.adresource_id = r.id and r.adlocation_id = v.eid
and v.aid=id1;
v_id number(10);
v_vedioTotalPrice number(20,10);
v_totalPrice number(20,10);
v_vedio_percent number(20,10);
cnt number(10);
begin
cnt:=0;
open cur(p_id) ;
loop
fetch cur
into v_id;
exit when cur%notfound;
select
nvl(sum(api.adresource_spell_times * api.flux * api.modified_price * (apid.end_date-apid.begin_date+1)),0) into v_vedioTotalPrice
from
adm_adplan_tab ap,adm_adplanitem_tab api, adm_adplanitemdate_tab apid, adm_adlocation_list_view v, adm_adresource_tab r
where
ap.contract_id=v_id
and ap.id = api.adplan_id
and api.id = apid.adplanitem_id
and api.adresource_id = r.id
and r.adlocation_id = v.eid
and v.aid=p_id;
select
nvl(sum(api.adresource_spell_times * api.flux * api.modified_price * (apid.end_date-apid.begin_date+1)),0) into
v_totalPrice
from
adm_adplan_tab ap,adm_adplanitem_tab api, adm_adplanitemdate_tab apid
where
ap.contract_id=v_id
and ap.id = api.adplan_id
and api.id = apid.adplanitem_id;
if v_vedioTotalPrice=0 or v_totalPrice=0 then
v_vedio_percent:=0;
else
v_vedio_percent:=v_vedioTotalPrice/v_totalPrice;
end if;
cnt:=cnt+1;
update adm_contract_tab set is_vedio='yes', vedio_percent=v_vedio_percent where id=v_id;
if mod(cnt,1000)=0 then
commit;
end if;
end loop;
commit;
end ;