关于数据库的数据迁移

最近公司在为客户的即用升级开发，由2期升级为3期，说是说升级，其实也就是重新开发了，现在开发工作已经开发的差不多了，现在涉及到将2期的数据导入到3期中，现在将导数据的方法共享一下：

数据库：oracle
工具：PL/SQL
问题：数据迁移

详解：
由于2个项目的数据库不相同，所以，考虑到字段等特殊问题，整体思路是通过利用oracle的游标特点来实现，将主要表的数据都写成游标，在通过移动游标的指针来查找别的数据，并添加到指定的表中，具体实现如下：

declare
  wizard_Id number;
  comName varchar(100);
  accCount number;
  endowCount number;
  socialId number;
  medCount number;
  fileCount number;
  accId number;
  Comid number;
  btId number;
  cout number;
  startYear number;
  startMonth number;
  entrustCount number;
  old aaa%rowtype;
  cursor comc is 
  select * from aaa;
  begin
  open comc;
  loop
        fetch comc into old;
        exit when comc%notfound;
        select general.nextval into wizard_Id from dual ;
        select company_id into Comid from employee_up_down_post where employee_up_down_id = old.b;
        --插入到任务通知单表
        insert into service_operate_wizard 
        (service_operate_wizard_id,EMPLOYEE_UP_DOWN_ID,employee_id,company_id,insert_date,df,is_basic_inf,is_basic_hi,IS_ENDOW,IS_FILE_READY,IS_MEDICAL,IS_UNEMP)
        values( wizard_Id,old.b,old.a,Comid,sysdate,0,1,0,0,0,0,0);
        --公积金
        select count(*) into accCount from employee_fee_period_item  efpi where efpi.employee_fee_period_id = old.e and efpi.product_id in (10026424332,1006313,6990);
        if accCount >0 then
        select general.nextval into btId from dual ;
        insert into business_task
        (business_task_id,employee_id,company_id,employee_up_down_id,business_type,df,status,is_need_operate,wizard_id) 
        values(btId,old.a,Comid,old.b,'11000',0,4,1,wizard_Id);
        update service_operate_wizard set is_basic_hi = 4,BASIC_HI_ID=btId where service_operate_wizard_id = wizard_Id;
        select count(*) into cout from e_business.acc_fund where employee_on_off_id  = old.b;
        if cout >0 then
        --说明有公积金
        select general.nextval into accId from dual;
         insert into EMPLOYEE_SI_SERVICE (EMPLOYEE_SI_SERVICE_ID,SERVICE_OPERATE_WIZARD_ID,BUSINESS_TASK_ID,
        SOCIAL_INSURANCE_ID,ACCOUNT_TYPE,OPERATE_TYPE,SOCIAL_NUMBER,ACCFUND_NUMBER,STATUS,ACCOUNT,SOCIAL_START_YEAR,SOCIAL_START_MONTH,
        CREATE_DATE,CREATE_MAN,MEMO,SERVICE_TYPE,DF,Product_Id,begindate,enddate,EMPLOYEE_FEE_PERIOD_ID) values 
        (accId,wizard_Id,btId,'','','','','',4,'','','',sysdate,1030,'数据导入','11000',0,'6990',to_date(old.c,'yyyy-mm-dd'),to_date(old.d,'yyyy-mm-dd'),old.e);
        end if;
        end if;
        --养老等四险
        select count(*) into endowCount from employee_fee_period_item  efpi where efpi.employee_fee_period_id = old.e and efpi.product_id in (10026423460,7009,9033622,18766302);
        if endowCount >0 then

        --用工
        select general.nextval into btId from dual ;
        insert into business_task
        (business_task_id,employee_id,company_id,employee_up_down_id,business_type,df,status,is_need_operate,wizard_id) 
        values(btId,old.a,Comid,old.b,'10000',0,4,1,wizard_Id);
        update service_operate_wizard set IS_HIREFIRE = -1,HIREFIRE_ID=btId where service_operate_wizard_id = wizard_Id;

        select general.nextval into btId from dual ;
        insert into business_task
        (business_task_id,employee_id,company_id,employee_up_down_id,business_type,df,status,is_need_operate,wizard_id) 
        values(btId,old.a,Comid,old.b,'10000',0,4,1,wizard_Id);
        update service_operate_wizard set IS_ENDOW = 4,ENDOW_ID=btId where service_operate_wizard_id = wizard_Id;
        --养老
        select count(*)into cout  from e_business.annuity_transaction  where employee_on_off_id =old.b 
        and annuity_unit_pay is not null;
        if cout >0 then
        select general.nextval into accId from dual;
        insert into EMPLOYEE_SI_SERVICE (EMPLOYEE_SI_SERVICE_ID,SERVICE_OPERATE_WIZARD_ID,BUSINESS_TASK_ID,
        SOCIAL_INSURANCE_ID,ACCOUNT_TYPE,OPERATE_TYPE,SOCIAL_NUMBER,ACCFUND_NUMBER,STATUS,ACCOUNT,SOCIAL_START_YEAR,SOCIAL_START_MONTH,
        CREATE_DATE,CREATE_MAN,MEMO,SERVICE_TYPE,DF,Product_Id,begindate,enddate,EMPLOYEE_FEE_PERIOD_ID) values 
        (accId,wizard_Id,btId,'','','','','',4,'','','',sysdate,1030,'数据导入','10000',0,'7009',to_date(old.c,'yyyy-mm-dd'),to_date(old.d,'yyyy-mm-dd'),old.e);
        end if;
        --失业
        select count(*)into cout  from e_business.annuity_transaction  where employee_on_off_id =old.b 
        and unemployment_unit_pay is not null;
        if cout >0 then
        select general.nextval into accId from dual;
        insert into EMPLOYEE_SI_SERVICE (EMPLOYEE_SI_SERVICE_ID,SERVICE_OPERATE_WIZARD_ID,BUSINESS_TASK_ID,
        SOCIAL_INSURANCE_ID,ACCOUNT_TYPE,OPERATE_TYPE,SOCIAL_NUMBER,ACCFUND_NUMBER,STATUS,ACCOUNT,SOCIAL_START_YEAR,SOCIAL_START_MONTH,
        CREATE_DATE,CREATE_MAN,MEMO,SERVICE_TYPE,DF,Product_Id,begindate,enddate,EMPLOYEE_FEE_PERIOD_ID) values 
        (accId,wizard_Id,btId,'','','','','',4,'','','',sysdate,1030,'数据导入','10000',0,'7008',to_date(old.c,'yyyy-mm-dd'),to_date(old.d,'yyyy-mm-dd'),old.e);
        end if;
        --工伤
        select count(*)into cout  from e_business.annuity_transaction  where employee_on_off_id =old.b 
        and occupational_unit_pay is not null;
        if cout >0 then
        select general.nextval into accId from dual;
        insert into EMPLOYEE_SI_SERVICE (EMPLOYEE_SI_SERVICE_ID,SERVICE_OPERATE_WIZARD_ID,BUSINESS_TASK_ID,
        SOCIAL_INSURANCE_ID,ACCOUNT_TYPE,OPERATE_TYPE,SOCIAL_NUMBER,ACCFUND_NUMBER,STATUS,ACCOUNT,SOCIAL_START_YEAR,SOCIAL_START_MONTH,
        CREATE_DATE,CREATE_MAN,MEMO,SERVICE_TYPE,DF,Product_Id,begindate,enddate,EMPLOYEE_FEE_PERIOD_ID) values 
        (accId,wizard_Id,btId,'','','','','',4,'','','',sysdate,1030,'数据导入','10000',0,'9033622',to_date(old.c,'yyyy-mm-dd'),to_date(old.d,'yyyy-mm-dd'),old.e);
        end if;
        --生育
        select count(*)into cout  from e_business.annuity_transaction  where employee_on_off_id =old.b 
        and birth_unit_pay is not null;
        if cout >0 then
        select general.nextval into accId from dual;
        insert into EMPLOYEE_SI_SERVICE (EMPLOYEE_SI_SERVICE_ID,SERVICE_OPERATE_WIZARD_ID,BUSINESS_TASK_ID,
        SOCIAL_INSURANCE_ID,ACCOUNT_TYPE,OPERATE_TYPE,SOCIAL_NUMBER,ACCFUND_NUMBER,STATUS,ACCOUNT,SOCIAL_START_YEAR,SOCIAL_START_MONTH,
        CREATE_DATE,CREATE_MAN,MEMO,SERVICE_TYPE,DF,Product_Id,begindate,enddate,EMPLOYEE_FEE_PERIOD_ID) values 
        (accId,wizard_Id,btId,'','','','','',4,'','','',sysdate,1030,'数据导入','10000',0,'18766302',to_date(old.c,'yyyy-mm-dd'),to_date(old.d,'yyyy-mm-dd'),old.e);
        end if;

        end if;
        --医疗保险
        select count(*) into medCount from employee_fee_period_item  efpi where efpi.employee_fee_period_id = old.e and efpi.product_id in (10026423453,10026423417,31844,31860,7010);
        if medCount >0 then
        select general.nextval into btId from dual ;
        insert into business_task
        (business_task_id,employee_id,company_id,employee_up_down_id,business_type,df,status,is_need_operate,wizard_id) 
        values(btId,old.a,Comid,old.b,'01100',0,4,1,wizard_Id);
        update service_operate_wizard set IS_MEDICAL =4,MEDICAL_ID=btId where service_operate_wizard_id = wizard_Id;
        select count(*)into cout  from e_business.annuity_transaction  where employee_on_off_id =old.b 
        and medical_unit_pay is not null;
        if cout >0 then
        select general.nextval into accId from dual;
        insert into EMPLOYEE_SI_SERVICE (EMPLOYEE_SI_SERVICE_ID,SERVICE_OPERATE_WIZARD_ID,BUSINESS_TASK_ID,
        SOCIAL_INSURANCE_ID,ACCOUNT_TYPE,OPERATE_TYPE,SOCIAL_NUMBER,ACCFUND_NUMBER,STATUS,ACCOUNT,SOCIAL_START_YEAR,SOCIAL_START_MONTH,
        CREATE_DATE,CREATE_MAN,MEMO,SERVICE_TYPE,DF,Product_Id,begindate,enddate,EMPLOYEE_FEE_PERIOD_ID) values 
        (accId,wizard_Id,btId,'','','','','',4,'','','',sysdate,1030,'数据导入','1100',0,'7010',to_date(old.c,'yyyy-mm-dd'),to_date(old.d,'yyyy-mm-dd'),old.e);
        end if;
        end if;
        --档案
        select count(*) into fileCount from employee_fee_period_item  efpi where efpi.employee_fee_period_id = old.e and efpi.product_id in (7014);
        if fileCount >0 then
        select general.nextval into btId from dual ;
        insert into business_task
        (business_task_id,employee_id,company_id,employee_up_down_id,business_type,df,status,is_need_operate,wizard_id) 
        values(btId,old.a,Comid,old.b,'01100',0,4,1,wizard_Id);
        update service_operate_wizard set IS_FILE_READY = 4,FILE_ID=btId where service_operate_wizard_id = wizard_Id ;


        end if;
   end loop;
end;

执行以上游标后，数据可以成功导入到新的数据库中，如果大家有什么更好的思路，可以共享一下哦