oracle的存量,oracle存量数据迁移脚本示例（适用小数据量）

declare

nHomeCounty     number(3);

nResult         number(2);

nEntrustID      number(12);

nEctrustAgentID number(12);

nCount          number(8);

begin

for cur_data in (select *

from ccs.warrant_info

where status in (0, 1, 2) and warrant_type = 2) loop

nCount := 0;

for nHomeCity in 591 .. 599 loop

nResult := 0;

nCount := nCount + 1;

begin

if (cur_data.warrant_type = 2) then

select home_county

into nHomeCounty

from ccs.group_customer

where home_city = nHomeCity

and group_id = cur_data.object_id;

elsif (cur_data.warrant_type = 1) then

select home_county

into nHomeCounty

from ccs.users

where home_city = nHomeCity

and msisdn = cur_data.object_id;

end if;

exception

when no_data_found then

--数据异常,不迁移

nResult := 1;

end;

if (nResult = 0 ) then

select ccs.seq_entrust_id.nextval into nEntrustID from dual;

insert into ccs.entrust_deed_info(home_city, home_county, entrust_id, entrust_type, object_type,object_id,status,inure_time,expire_time,audit_status,audit_result,

audit_content,entrust_attachement,create_id,create_time,modify_id,modify_time,remark,history_id,audit_time)

values (nHomeCity,nHomeCounty,nEntrustID,cur_data.warrant_type,cur_data.object_type,cur_data.object_id,'0',cur_data.inure_time,cur_data.expire_time,'0',

'0',cur_data.audit_memo,cur_data.memo,cur_data.operator_id,cur_data.create_time,cur_data.modify_operator_id,cur_data.modify_time,cur_data.modify_content,cur_data.history_id,cur_data.audit_time);

for cur_data2 in (select *

from ccs.warrant_proxy_info

where proxy_grp_id = cur_data.proxy_grp_id) loop

begin

if(cur_data2.proxy_object_name is not null and cur_data2.history_id is not null) then

select ccs.seq_entrusted_agent_id.nextval into nEctrustAgentID from dual;

insert into ccs.entrusted_agent_info(entrust_id,entrusted_agent_id,entrusted_agent_name,licence_type,licence_number,licence_address,notify_msisdn,history_id,create_id,create_time,entrusted_agent_type)

values(nEntrustID,nEctrustAgentID,cur_data2.proxy_object_name,'',cur_data2.proxy_object_icno,'','',cur_data2.history_id,'9999999',cur_data2.create_time,'1');

end if;

end;

end loop;

end if;

if(nCount = 1000) then

nCount := 0;

commit;

end if;

end loop;

end loop;

end;

/