Oracle cursor batch processing data

Declare

-- start time

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

-- end time

V_date_end date:= to_date ('2015-11-01 00,' yyyy-mm-dd hh34:mi:ss')

-- temporary variable

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), 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

-- exit when there is no data in the cursor

Exit when plyNumberCur%notfound

End loop;-for end

Close plyNumberCur

Vested _ datestarted _ v_date_start+1

If v_date_start > = v_date_end then

Exit

End if

End loop;-loop

End

/