您好,登錄后才能下訂單哦!
declare
--開始時(shí)間
v_date_start date:= to_date('2015-05-28 00:00:00', 'yyyy-mm-dd hh34:mi:ss');
--結(jié)束時(shí)間
v_date_end date:= to_date('2015-11-01 00:00:00', 'yyyy-mm-dd hh34:mi:ss');
--臨時(shí)變量
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;--用來(lái)統(tǒng)計(jì)是否有數(shù)據(jù)
begin
loop
v_date_plus:=v_date_start+1;--時(shí)間變化,查詢游標(biāo)時(shí),每天查詢一次
open plyNumberCur;--打開游標(biāo)
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',sysdate,'lxg',v_insurance_end_date(i));
end if;
end if;
end loop;--for end;
commit;
--當(dāng)游標(biāo)中沒(méi)有數(shù)據(jù)將退出
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;
/
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。