declare

--开始时间

v_date_start date:= to_date('2015-05-28 00:00:00','yyyy-mm-dd hh24:mi:ss');

--结束时间

v_date_end date:= to_date('2015-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss');

--临时变量

v_date_plus date;

type type_policy_no is table of ply_base_info.policy_no%type;

v_policy_no type_policy_no;

type type_compensation_max_amount is table of ply_duty.compensation_max_amount%type;

v_compensation_max_amount type_compensation_max_amount;

type type_id_ply_duty is table of ply_duty.id_ply_duty%type;

v_id_ply_duty type_id_ply_duty;

type type_insurance_end_date is table of ply_base_info.insurance_end_date%type;

v_insurance_end_date type_insurance_end_date;

cursor plyNumberCur is

select

pbi.policy_no,

pd.compensation_max_amount,

pd.cover_spouse,

pdn.noclaim_rate,

pdn.noclaim_amount,

pd.id_ply_duty,

trunc(pbi.insurance_end_date)

from ply_base_info pbi,

marketproduct_info mi,

ply_risk_group prg,

ply_plan pp,

ply_duty pd,

ply_duty_noclaim pdn

where pbi.created_date >=v_date_start

and pbi.created_date < v_date_plus

and pbi.policy_no is not null

and pbi.product_code= mi.marketproduct_code

and pbi.product_version=mi.version

and mi.product_class in ('02','03')

and pbi.id_ply_base_info = prg.id_ply_base_info

and prg.id_ply_risk_group = pp.id_ply_risk_group

and pp.id_ply_plan = pd.id_ply_plan

and pd.id_ply_duty = pdn.id_ply_duty(+);

v_cnt number;--用来统计是否有数据

begin

loop

v_date_plus:=v_date_start+1;--时间变化,查询游标时，每天查询一次

open plyNumberCur;--打开游标

loop

--每1000条记录取一次，最后一次不够1000的也将取出,放到变量中

fetch plyNumberCur bulk collect into

v_policy_no,v_compensation_max_amount,v_id_ply_duty,v_insurance_end_date limit 1000 ;

for i in 1..v_policy_no.count loop

if v_compensation_max_amount(i) > 0 then

select count (pda.id_ply_duty) into v_cnt from ply_duty_attribute pda where pda.id_ply_duty = v_id_ply_duty(i) and pda.duty_attr_code='6';

if v_cnt>0 then

update ply_duty_attribute pda set pda.duty_attr_amount_value=to_char(v_compensation_max_amount(i)),pda.UPDATED_BY='lxg' where pda.id_ply_duty = v_id_ply_duty(i) and pda.duty_attr_code='6' and pda.UPDATED_BY<>'lxg';

else

insert into ply_duty_attribute(ID_PLY_DUTY_ATTRIBUTE,ID_PLY_DUTY,DUTY_ATTR_CODE,DUTY_ATTR_AMOUNT_VALUE,POLICY_NO,CREATED_DATE,CREATED_BY,UPDATED_DATE,UPDATED_BY,ARCHIVE_DATE)

values(sys_guid(),v_id_ply_duty(i),'6',to_char(v_compensation_max_amount(i)),v_policy_no(i),sysdate,'lxg',v_insurance_end_date(i));

end if;

end if;

end loop;--for end;

commit;

--当游标中没有数据将退出

exit when plyNumberCur%notfound;

end loop; --for end;

close plyNumberCur;

v_date_start:= v_date_start+1;

if v_date_start>= v_date_end then

exit;

end if;

end loop; --loop

end;

/