最近公司在为客户的即用升级开发,由2期升级为3期,说是说升级,其实也就是重新开发了,现在开发工作已经开发的差不多了,现在涉及到将2期的数据导入到3期中,现在将导数据的方法共享一下:
数据库:oracle
工具:PL/SQL
问题:数据迁移
详解:
由于2个项目的数据库不相同,所以,考虑到字段等特殊问题,整体思路是通过利用oracle的游标特点来实现,将主要表的数据都写成游标,在通过移动游标的指针来查找别的数据,并添加到指定的表中,具体实现如下:
执行以上游标后,数据可以成功导入到新的数据库中,如果大家有什么更好的思路,可以共享一下哦
数据库: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;
执行以上游标后,数据可以成功导入到新的数据库中,如果大家有什么更好的思路,可以共享一下哦
相关推荐
将数据库1的表A迁移到数据库2的表B里面,自己配置数据库,和表的数据库信息和字段信息就可以了!很简单
Oracle数据库数据迁移.pdf
一篇关于异构数据库间数据迁移的研究报告,研究分析的不错,有借鉴价值
Oracle数据库数据迁移
数据库数据文件迁移
去年年底做了不少系统的数据迁移,大部分系统由于平台和版本的原因,做的是逻辑迁移,少部分做的是物理迁移,有一些心得体会,与大家分享。 首先说说迁移流程,在迁移之前,写好方案,特别是实施的方案步骤一定...
现需要开发一套程序用来快速迁移数据库,要求如下: 1.使用人员可以指定迁移数据库类型 如:(orcal,sqlServer,csv 迁移至mysql) 2.在迁移数据库时,可以只迁移指定字段. 3.开发多任务的平台,按权重去执行任务,如:...
Oracle数据库数据迁移整理.pdf
postgreSQL数据迁移到达梦数据库操作
完整代码,Java高效实现数据库迁移同步
云数据库管理与数据迁移,云数据库管理与数据迁移,云数据库管理与数据迁移,云数据库管理与数据迁移,云数据库管理与数据迁移,云数据库管理与数据迁移
ORACLE数据库迁移方案,单机迁移到rac
* 源数据库库连接池配置 * */ ConnPoolConfig config = new ConnPoolConfig(); config.setMaxPoolSize(30); config.setInitialPoolSize(20); config.setUrl("jdbc:sqlserver://192.168.0.222;database...
windows环境下, db_name和instance_name都不相同的情况下的数据库迁移. 通过镜像文件实现.
Apsara Clouder云计算技能认证:云数据库管理与数据迁移--试题。这个还是挺容易考的,只要稍微有点数据库知识和认真做实验,真的挺容易的。
Oracle数据库数据迁移概览
然后,逐个执行存储过程,将原来数据迁移到新的表中。 5,删除存储过程,清理临时过程。 6,删除第1步中重命名的表。 7,分析新创建的表,生成表的统计信息 8,删除新创建的分区表。 9,创建原来存储过程使用的临时...
针对国产数据库kingbase中数据迁移到mysql和DB2的一个小工具~~ 开发的时候写的方便自己用的一个小工具,想看源码直接winrar进去看就成~
异构数据库的数据迁移方法和工具的使用说明
如果您需要将SQL的数据迁移到MYSQL,则需要这类工具; 如果您正好想开发一个这样的工具却没有思路,那本文档将能为您提供参考。 如果您遇到数据迁移工具中的一些问题,例如性能慢,本文能提供参考。 本文档仅提供...