//插入时不产生日志,alter table IALHospitalInfo nologging;//记录时间set timing on;declareCURSOR cur is select nvl(c.claim_code,c.claim_id) as claim_code,case when (select u.user_name from T_CIRC_USER u ,T_THIRD_POLICY p where u.user_id =p.user_id and p.CONFIRM_SEQUENCE_NO = c.Confirm_Sequence_No) is null then 'aaaa' else(select u.user_name from T_CIRC_USER u ,T_THIRD_POLICY p where u.user_id =p.user_id and p.CONFIRM_SEQUENCE_NO = c.Confirm_Sequence_No) end,'110000',perl.person_id,hos.hos_id,case when (select t.Na_Company_Code from T_CIRC_COMPANY t where pany_Id = pany_Id) is null then 'aaaa' else(select t.Na_Company_Code from T_CIRC_COMPANY t where pany_Id = pany_Id) end,hos.Hospital_Name,hos.Hospital_Factory_Certi_Codefrom CIITC_TMP_T_C_L c , T_CLAIM_PERSON_LOSS perl ,t_claim_person_hospital hos where hos.hos_id in (select v.hos_id from t_claim_person_hospital v) and c.claim_id = hos.claim_id and perl.person_id = hos.person_id; type rec is table of IALHospitalInfo%rowtype;recs rec;beginopen cur;while (true) loop//批量提交控制(每100w提交一次)fetch cur bulk collect into recs limit 1000000;//捆绑插入(减少与服务器交互的次数)forall i in 1..recs.count insert /*+ append */into IALHospitalInfo values recs(i); commit;exit when cur%notfound;end loop;close cur;end;/
如果觉得《oracle大数据量迁移 分批量导入样例(fetch...bulk collect)以及forall结合使用》对你有帮助,请点赞、收藏,并留下你的观点哦!