完整迁移脚本 sql

2022年11月6日12:57:37
/*
-- 1、检查单表
---------------------存储过程----------------
*//*
old---旧系统数据库, newdb---新系统数据库
*/DROPPROCEDUREIFEXISTS newdb.checklist_procedure;CREATEPROCEDURE newdb.checklist_procedure()BEGIN/*
一、检查单重复问题

由于新旧系统的检查单命名规则是一直的,并且新平台从2020年4月1号上线至5月9号这段时间内,两个平台同时在使用,导致了有重复的检查单号。
现处理方式,首先,更改旧平台与新平台重复的检查单号(后面统一加上小写的a),然后,在迁移数据。
*//*
执行查询会产生81条数据记录:即有81个检查单号重复---见 重复检查单号清单.xlsx
*/SELECT
	olddb.checklist.CHECK_NOFROM
	olddb.checklistWHERE
	olddb.checklist.CHECK_NOIN(SELECT
		newdb.checklist.check_noFROM
		newdb.checklist);/*
新建一张表用于存放 重复的检查单号, 以备后续使用。 
*/DROPTABLEIFEXISTS newdb.`multi_data_checkno`;CREATETABLE newdb.`multi_data_checkno`(`CHECK_NO`VARCHAR(25)NOTNULL,PRIMARYKEY(`CHECK_NO`));/*
将数据移动到multi_data_checkno中
*/INSERTINTO newdb.multi_data_checkno(CHECK_NO)SELECT a.CHECK_NOFROM(SELECT
	olddb.checklist.CHECK_NOFROM
	olddb.checklistWHERE
	olddb.checklist.CHECK_NOIN(SELECT
		newdb.checklist.check_noFROM
		newdb.checklist))AS a;/*
旧平台中所有涉及到该81个检查单号的表中都需要做修改

*//*
1、
修改checklist表.
*/UPDATE olddb.checklistSET olddb.checklist.CHECK_NO=CONCAT(olddb.checklist.CHECK_NO,'a')WHERE olddb.checklist.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
2、
指派记录流水表
*/UPDATE olddb.assigntempSET olddb.assigntemp.CHECK_NO=CONCAT(olddb.assigntemp.CHECK_NO,'a')WHERE olddb.assigntemp.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
3、检查单支付表.
*/UPDATE olddb.checkpaySET olddb.checkpay.CHECK_NO= CONCAT(olddb.checkpay.CHECK_NO,'a')WHERE olddb.checkpay.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
4、检查单申请退款记录表.
*/UPDATE olddb.checkrefundtempSET olddb.checkrefundtemp.CHECK_NO= CONCAT(olddb.checkrefundtemp.CHECK_NO,'a')WHERE olddb.checkrefundtemp.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
5、内镜报告表
*/UPDATE olddb.endoscopicreportSET olddb.endoscopicreport.CHECK_NO= CONCAT(olddb.endoscopicreport.CHECK_NO,'a')WHERE olddb.endoscopicreport.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
6、影像文件表.
*/UPDATE olddb.imagefileSET olddb.imagefile.CHECK_NO= CONCAT(olddb.imagefile.CHECK_NO,'a')WHERE olddb.imagefile.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
7、影像文件详情表.
*/UPDATE olddb.imagedetailSET olddb.imagedetail.CHECK_NO= CONCAT(olddb.imagedetail.CHECK_NO,'a')WHERE olddb.imagedetail.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
8、电子病历表
*/UPDATE olddb.medicalrecordSET olddb.medicalrecord.CHECK_NO= CONCAT(olddb.medicalrecord.CHECK_NO,'a')WHERE olddb.medicalrecord.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
9、操作记录表
*/UPDATE olddb.operatingrecordSET olddb.operatingrecord.CHECK_NO= CONCAT(olddb.operatingrecord.CHECK_NO,'a')WHERE olddb.operatingrecord.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
10、付款记录表
*/UPDATE olddb.orderpaySET olddb.orderpay.CHECK_NO= CONCAT(olddb.orderpay.CHECK_NO,'a')WHERE olddb.orderpay.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
11、订单表.
*/UPDATE olddb.readorderSET olddb.readorder.CHECK_NO= CONCAT(olddb.readorder.CHECK_NO,'a')WHERE olddb.readorder.CHECK_NOIN(SELECT CHECK_NOFROM multi_data_checkno);/*
-- 分割线------------------------------------------------------------------------------------------------------------------------
*//*
将目标表中的病例表case和患者表patient的除主键以外的所有必填项 修改为非必填项
注意,在最后需要修改回来 
*//*
-- case 表
*/ALTERTABLE newdb.`case`MODIFY patient_noVARCHAR(20)NULL;ALTERTABLE newdb.`case`MODIFY inspection_subjectsVARCHAR(20)NULL;ALTERTABLE newdb.`case`MODIFY check_datedatetimeNULL;ALTERTABLE newdb.`case`MODIFY patient_idintNULL;ALTERTABLE newdb.`case`MODIFY inspection_idintNULL;ALTERTABLE newdb.`case`MODIFY inspection_organization_idintNULL;ALTERTABLE newdb.`case`MODIFY imcd_idintNULL;/*
-- patient 表
*/ALTERTABLE newdb.patientMODIFY nameVARCHAR(100)NULL;ALTERTABLE newdb.patientMODIFY sexintNULL;ALTERTABLE newdb.patientMODIFY phoneVARCHAR(11)NULL;ALTERTABLE newdb.patientMODIFY user_idintNULL;ALTERTABLE newdb.patientMODIFY organization_idintNULL;ALTERTABLE newdb.patientMODIFY m_c_d_idintNULL;ALTERTABLE newdb.patientMODIFY create_timedatetimeNULL;ALTERTABLE newdb.patientMODIFY city_nameVARCHAR(20)NULL;ALTERTABLE newdb.patientMODIFY district_nameVARCHAR(20)NULL;ALTERTABLE newdb.patientMODIFY province_nameVARCHAR(20)NULL;/*
-- 声明用户变量:临时病例id, 因为目标表case和patient中已经有一些数据,使用这里选择一个比较大的初始值开始
*/SET@case_id_temp=1000000;/*
-- 声明用户变量:临时患者id
*/SET@patient_id_temp=1000000;/*
-- 声明变量:临时的USER_ID(送检医生id)
*/SET@user_id_temp='';/*
-- 声明变量:临时的USER_ID(送检医生id int类型)
*/SET@user_id_temp_int='';/*
-- 声明变量:临时的PATIENT_NAME(患者姓名)
*/SET@patient_name_temp='';/*
-- 声明变量:临时的ORGANIZATIONID(送检机构ID)
*/SET@organization_id_temp='';/*
-- 声明变量:临时的CHECK_ITEM(检查项目)
*/SET@check_item_temp='';/*
-- 声明变量:临时的STATUS(检查单状态)
*/SET@status_temp='';/*
-- INSERT INTO newdb.temp_variable_table VALUES('@status_temp',-999);
*//*
-- 声明变量:临时的IS_PAY(支付状态)
*/SET@is_pay_temp='';/*
-- INSERT INTO newdb.temp_variable_table VALUES('@is_pay_temp',-999);
*//*
-- 声明变量:临时的PATIENT_AGE(患者年龄)
*/SET@patient_age_temp='';/*
-- 声明变量:临时的PATIENT_SEX(患者性别)
*/SET@patient_sex_temp='';/*
-- 声明变量:临时的PATIENT_PHONE(患者电话)
*/SET@patient_phone_temp='';/*
-- 声明变量:临时的PATIENT_NO(患者编号)
*/SET@patient_no_temp='';/*
-- 声明变量:临时的CHECK_DATE(检查日期)
*/SET@check_date_temp='';/*
-- 声明变量:临时的PATIENT_DESCRIBE(病人主述)
*/SET@patient_describe_temp='';/*
-- 声明变量:用于迁移字段REFUND_ID(退单人id),存储临时值
*/SET@is_doctor=0;/*
-- INSERT INTO newdb.temp_variable_table VALUES('@is_doctor',-999);
*/SET@is_expert=0;/*
-- INSERT INTO newdb.temp_variable_table VALUES('@is_expert',-999);
*/SET@is_operator='';/*
-- 声明变量:临时的CAPSULE_NO(胶囊编号)
*/SET@capsule_no_temp='';/*
-- 声明变量:临时的AUDIT_ID(审核人ID)
*/SET@audit_id_temp='';/*
-- INSERT INTO newdb.temp_variable_table VALUES('@audit_id_temp',-999);
*//*
-- 声明变量:临时的PAYTYPE(支付方式)
*/SET@paytype_temp='';/*
-- 声明变量:临时的MEDICALRECORD_ID(病历ID)
*/SET@medicalrecord_id='';/*
直接迁移的字段或者是简单处理的字段, 不能直接迁移的 使用触发器处理
*/INSERT newdb.checklist(ID, check_no,money,pay_state,create_time, refund_reason,audit_note,upload_flag,refund_name, refund_date,audit_time,audit_name,type)SELECT olddb.checklist.ID+1000000,olddb.checklist.CHECK_NO,olddb.checklist.MONEY,olddb.checklist.IS_PAY+1,olddb.checklist.CREATE_TIME,olddb.checklist.REFUND_REASON,olddb.checklist.AUDIT_NOTE,olddb.checklist.UPLOAD_FLAG,olddb.checklist.REFUND_NAME,olddb.checklist.REFUND_DATE,olddb.checklist.AUDIT_TIME,olddb.checklist.AUDIT_NAME,olddb.checklist.TYPEFROM olddb.checklist;end;/*
-----------------触发器------------------
*//*
检查单表触发器:用于在迁入checklist表时,需要附带处理的部分

注意:导入完成后一定要删除该触发器!导入完成后一定要删除该触发器!导入完成后一定要删除该触发器!

*/DROPTRIGGERIFEXISTS newdb.checklist_trigger;CREATETRIGGER newdb.checklist_trigger BEFOREINSERTON newdb.checklistFOR EACH ROWBEGIN/*
-- 声明局部变量:用于存储源表检查单id, 因为插入的时候在源表的id上加上了 1000000, 所以这里要减去1000000
*/DECLARE old_checklist_idINT;SET old_checklist_id= NEW.ID-1000000;/*
-- 因为源表中没有case_id及patient_id字段,但是却又有病例case及患者相关的信息,所以需要 -- 手动造case_id及patient_id的值,用于关联病例表及患者表的id: 从1000000开始自增 (int 类型)
*/SET NEW.case_id=@case_id_temp;SET NEW.patient_id=@patient_id_temp;/*
	将case的id及患者id插入到case表中
	*/INSERTINTO newdb.`case`(id,patient_id)VALUES(@case_id_temp,@patient_id_temp);INSERTINTO newdb.patient(id)VALUES(@patient_id_temp);/*
-- 迁移字段USER_ID(送检医生ID)
-- 将原表中的USER_ID(送检医生ID)迁入到目标数据库中的case表对应的inspection_id
-- 0、将USER_ID的值SELECT 到INTO 一个变量里面@user_id_temp
-- 1、根据变量值从中间表doctortem中找出对应的int类型id,
-- 2、将第1步中的int类型id设置为case表的inspection_id
*/SELECT USER_IDINTO@user_id_tempFROM olddb.checklistWHERE ID= old_checklist_id;SELECT increment_idINTO@user_id_temp_intFROM doctortemWHERE ID=@user_id_temp;UPDATE newdb.caseSET inspection_id=@user_id_temp_intWHERE id=@case_id_temp;/*
-- 迁移字段PATIENT_NAME(患者姓名)
-- 1、根据ID查询出源表中PATIENT_NAME的值,存入变量中
-- 2、根据上面插入的患者id更新患者姓名到患者表中
*/SELECT PATIENT_NAMEINTO@patient_name_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.patientSET name=@patient_name_tempWHERE id=@patient_id_temp;/*
-- 迁移字段ORGANIZATIONID(送检机构ID)
-- 1、根据ID查询出源表中迁移字段ORGANIZATIONID的值,存入变量中
-- 2、根据上面插入的病例id更新机构ID到case表的inspection_organization_id
*/SELECT ORGANIZATIONIDINTO@organization_id_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET inspection_organization_id=@organization_id_tempWHERE id=@case_id_temp;/*
-- 迁移字段CHECK_ITEM(检查项目)
-- 1、根据ID查询出源表中迁移字段CHECK_ITEM的值,存入变量中
-- 2、根据上面插入的病例id更新检查项目到case表的inspection_subjects
*/SELECT CHECK_ITEMINTO@check_item_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET inspection_subjects=@check_item_tempWHERE id=@case_id_temp;/*
-- 迁移字段STATUS(检查单状态)
-- 1、根据ID查询出源表中迁移字段STATUS的值,存入变量中
-- 2、根据源表中STATUS的值做对应的处理以适应目标表的状态值,然后迁移到目标表
*/SELECTSTATUSINTO@status_tempFROM olddb.checklistWHERE ID= old_checklist_id;IF@status_temp<5THENSET NEW.status=@status_temp+1;ELSEIF@status_temp=5THENSET NEW.status=7;ELSEIF@status_temp=6THENSET NEW.status=10;ELSEIF@status_temp=7THENSET NEW.status=11;ENDIF;/*
-- 迁移字段IS_PAY(支付状态)
-- 1、根据ID查询出源表中迁移字段IS_PAY的值,存入变量中
-- 2、迁移到目标表字段pay_status的同时处理IS_PAY的值
*/SELECT IS_PAYINTO@is_pay_tempFROM olddb.checklistWHERE ID= old_checklist_id;SET NEW.pay_state=@is_pay_temp+1;/*
-- 迁移字段PATIENT_AGE
-- 1、根据ID查询出源表中迁移字段PATIENT_AGE的值,存入变量中
-- 2、根据患者id将源表字段PATIENT_AGE设置到目标患者表patient的age字段中
*/SELECT PATIENT_AGEINTO@patient_age_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.patientSET age=@patient_age_tempWHERE id=@patient_id_temp;/*
-- 迁移字段PATIENT_SEX(患者性别)
-- 同上
*/SELECT PATIENT_SEXINTO@patient_sex_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.patientSET sex=@patient_sex_tempWHERE id=@patient_id_temp;/*
-- 迁移字段PATIENT_PHONE(患者电话)
-- 同上
*/SELECT PATIENT_PHONEINTO@patient_phone_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.patientSET phone=@patient_phone_tempWHERE id=@patient_id_temp;/*
-- 迁移字段PATIENT_NO(患者编号)
-- 1、根据ID查询出源表中迁移字段PATIENT_AGE的值,存入变量中
-- 2、根据病例的case_id_temp值将1中的值设置到目标表case中的patient_no字段
*/SELECT PATIENT_NOINTO@patient_no_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET patient_no=@patient_no_tempWHERE id=@case_id_temp;/*
-- 迁移字段CHECK_DATE(检查日期)
-- 同上
*/SELECT CHECK_DATEINTO@check_date_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET check_date=@check_date_tempWHERE id=@case_id_temp;/*
-- 迁移字段PATIENT_DESCRIBE(病人主述)
-- 同上
*/SELECT PATIENT_DESCRIBEINTO@patient_describe_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET symptom_complaint=@patient_describe_tempWHERE id=@case_id_temp;/*
-- 迁移字段REFUND_ID(退单人id)
-- 1、根据源表中的varchar类型REFUND_ID在目标表doctortem中查找出对应的int类型id并设置到变量SET @is_doctor = 0中
-- 2、根据源表中的varchar类型REFUND_ID在目标表experttem中查找出对应的int类型id并设置到变量SET @is_expert = 0中
-- 3、运营人员:通过查询源表可知,只有运营人员中只有 "超级管理员" 退过5单,所以只需要将 超级管理员 的id 设置到目标表即可,通过查询目标表sys_users可知 -- 超管的int类型id为1。这里不考虑中间表 sys_users_temp。
*/SELECT increment_idINTO@is_doctorFROM newdb.doctortemWHERE newdb.doctortem.ID=(SELECT olddb.checklist.REFUND_IDFROM olddb.checklistWHERE olddb.checklist.ID= old_checklist_id);SELECT increment_idINTO@is_expertFROM newdb.experttempWHERE newdb.experttemp.ID=(SELECT olddb.checklist.REFUND_IDFROM olddb.checklistWHERE olddb.checklist.ID= old_checklist_id);SELECT REFUND_NAMEINTO@is_operatorFROM olddb.checklistWHERE ID= old_checklist_id;IF@is_doctor>0THENSET NEW.refund_id=@is_doctor;ELSEIF@is_expert>0THENSET NEW.refund_id=@is_expert;ELSEIF@is_operator='超级管理员'THENSET NEW.refund_id=1;ENDIF;/*
-- 迁移字段CAPSULE_NO(胶囊序列号)
-- 1、根据ID查询出源表中迁移字段CAPSULE_NO的值,存入变量中
-- 2、根据上面插入的病例id更新检查项目到case表的capsule_no字段上
*/SELECT CAPSULE_NOINTO@capsule_no_tempFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET capsule_no=@capsule_no_tempWHERE id=@case_id_temp;/*
-- 迁移字段 AUDIT_ID (审核人ID)
-- 1、查询出varchar类型对应的int类型id值(在sys_user_temp中)存入变量
-- 2、将变量@audit_temp的值设置到目标表checklist中的audit_id上
*/SELECT increment_idINTO@audit_id_tempFROM newdb.sys_users_tempWHERE newdb.sys_users_temp.ID=(SELECT olddb.checklist.AUDIT_IDFROM olddb.checklistWHERE olddb.checklist.ID= old_checklist_id);SET NEW.audit_id=@audit_id_temp;/*
-- 迁移字段 PAYTYPE
-- 1、根据源表checklist的ID(NEW.ID-1000000)查询出PAYTYPE的值存入@paytpe中
-- 2、根据规则0-->1, 1-->3进行设值
*/SELECT PAYTYPEINTO@paytypeFROM olddb.checklistWHERE ID= old_checklist_id;IF@paytype=0THENSET NEW.pay_type=1;ELSEIF@paytype=1THENSET NEW.pay_type=3;ENDIF;/*
-- 迁移字段MEDICALRECORD_ID(病历id)
-- 1、根据ID查询出迁移字段MEDICALRECORD_ID的值,存入变量@medicalrecord_id中
-- 2、根据上面插入的病例id更新病历id到case表的capsule_no字段上medicalrecord_id上
*/SELECT MEDICALRECORD_IDINTO@medicalrecord_idFROM olddb.checklistWHERE ID= old_checklist_id;UPDATE newdb.caseSET medicalrecord_id=@medicalrecord_idWHERE id=@case_id_temp;/*
-- 病例id自增
*/SET@case_id_temp=@case_id_temp+1;/*
-- 患者id自增
*/SET@patient_id_temp=@patient_id_temp+1;END;/*
-------------------------------调用存储过程---------------------------------
*/CALL newdb.checklist_procedure();/*
-- 2、检查单支付表
*//*
------------------------------存储过程----------------------------------
*//*
检查单支付checkpay存储过程
*/DROPPROCEDUREIFEXISTS newdb.checkpay_procedure;CREATEPROCEDURE newdb.checkpay_procedure()BEGIN/*
游标使用变量
*/DECLARE doneBOOLEANDEFAULT0;/*
声明变量var_account_type(账户类型)
*/DECLARE var_account_typeVARCHAR(8);/*
声明变量var_check_no(检查单号)
*/DECLARE var_check_noVARCHAR(64);/*
声明变量var_pay_date(支付日期)
*/DECLARE var_pay_datedatetime;/*
创建游标:用于处理不能直接迁移的字段
*/DECLARE checkpay_cursorCURSORFORSELECT
olddb.checkpay.ACCOUNT_TYPEAS account_type,
olddb.checkpay.CHECK_NOAS check_no,
olddb.checkpay.CREATE_TIMEAS pay_dateFROM olddb.checkpay;DECLARECONTINUEHANDLERFOR SQLSTATE'02000'
  • 作者:爱看老照片
  • 原文链接:https://blog.csdn.net/qq_29025955/article/details/122825668
    更新时间:2022年11月6日12:57:37 ,共 12592 字。