存储过程中游标带参数

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 ;