oracle 游标批量处理数据
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; / (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |