DECLARE cursorIn CURSOR FOR SELECT sopinstanceUId FROM dicomentity a WHERE NOT EXISTS(SELECT sopinstanceUId FROM Instance b WHERE a.sopinstanceUId=b.sopinstanceUId );
DECLARE cursorOut CURSOR FOR SELECT sopinstanceUId FROM dicomentity a WHERE EXISTS(SELECT sopinstanceUId FROM Instance b WHERE a.sopinstanceUId=b.sopinstanceUId );
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _v_done = 1;
DELETE FROM move_file;
SELECT CONCAT(savepath,'/') INTO dest_filepath FROM dicom_filePath WHERE pathtype='default';
OPEN cursorIn;
REPEAT
FETCH cursorIn INTO ins_code;
IF NOT _v_done THEN
INSERT INTO instance(instanceNumber,patient_fk ,study_fk ,series_fk ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId)
SELECT instanceNumber,patientId,studyInstanceUID, seriesInstanceUID ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId FROM dicomEntity a
WHERE NOT EXISTS(SELECT 1 FROM dicomentity WHERE a.sopInstanceUID=sopInstanceUID AND a.id<id) AND sopInstanceUID =ins_code;
INSERT INTO dicom_file_info(sOPInstanceUID,fileName,filePath) VALUES (ins_code,dest_filepath,DATE_FORMAT(NOW(),'%Y/%m/%e'));
END IF;
UNTIL _v_done
END REPEAT;
CLOSE cursorIn;
DECLARE cursorOut CURSOR FOR SELECT sopinstanceUId FROM dicomentity a WHERE EXISTS(SELECT sopinstanceUId FROM Instance b WHERE a.sopinstanceUId=b.sopinstanceUId );
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _v_done = 1;
DELETE FROM move_file;
SELECT CONCAT(savepath,'/') INTO dest_filepath FROM dicom_filePath WHERE pathtype='default';
OPEN cursorIn;
REPEAT
FETCH cursorIn INTO ins_code;
IF NOT _v_done THEN
INSERT INTO instance(instanceNumber,patient_fk ,study_fk ,series_fk ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId)
SELECT instanceNumber,patientId,studyInstanceUID, seriesInstanceUID ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId FROM dicomEntity a
WHERE NOT EXISTS(SELECT 1 FROM dicomentity WHERE a.sopInstanceUID=sopInstanceUID AND a.id<id) AND sopInstanceUID =ins_code;
INSERT INTO dicom_file_info(sOPInstanceUID,fileName,filePath) VALUES (ins_code,dest_filepath,DATE_FORMAT(NOW(),'%Y/%m/%e'));
END IF;
UNTIL _v_done
END REPEAT;
CLOSE cursorIn;
解决方案 »
- 求一个MYSQL SQL优化,求大侠指教...
- mysql复制的问题
- 解析字符串问题,100100000101011111010101解析出111
- Mysql创建表 出错
- MySQL短时间大量插入,跟新数据出现异常,拒绝连接。帮个忙!!谢谢 back_log
- 一个问题10分 中文 乱码 基本问题请教 分数可增加 在线等待 随时结贴放分
- mysql线程安全问题
- 请问怎么用odbc把aceess里的数据导到linux下的mysql里?
- 如何导入*.sql文件???-------------急100分!!!
- 命令行导入txt文件到MySQL后查看数据最前面多出了个乱码。。
- 数据库多表查询,求帮忙拼一个sql语句
- 求助,分区后正常,数据达到350W(单表)出现22错误
SET done=0;
OPEN cursorOut;
REPEAT
FETCH cursorOut INTO ins_out_code;
IF NOT done THEN
DELETE FROM instance WHERE sopInstanceUID =ins_out_code;
INSERT INTO instance(instanceNumber,patient_fk ,study_fk ,series_fk ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId)
SELECT instanceNumber, patientId,studyInstanceUID, seriesInstanceUID ,sopInstanceUID ,imageType , bitsAllocated , photometricInterpretation , availability , imgrows , imgcolumns , retrieveAETitle,instanceDesc ,acquisitionDate ,contentDate,updateTime,createTime,sopclassUId FROM dicomEntity a
WHERE NOT EXISTS(SELECT 1 FROM dicomentity WHERE a.sopInstanceUID=sopInstanceUID AND a.id<id) AND sopInstanceUID =ins_out_code; INSERT INTO move_file(sopInstanceUID,filePath,createtime) SELECT sopInstanceUID, CONCAT(fileName,filePath,sopInstanceUID),NOW() FROM dicom_file_info WHERE sopInstanceUID=ins_out_code;
UPDATE dicom_file_info SET fileName=dest_filepath, filePath=DATE_FORMAT(NOW()) WHERE sopInstanceUID=ins_out_code;
END IF;
UNTIL done
END REPEAT;
CLOSE cursorOut;
INSERT INTO patient(patientId ,patientName ,PatientBirthDate ,PatientSex ,updateTime,createTime)
SELECT patientId ,patientName ,PatientBirthDate ,PatientSex ,updateTime,createTime FROM dicomEntity a WHERE NOT EXISTS (SELECT patientId FROM patient b WHERE a.patientId=b.patientId) GROUP BY patientId ORDER BY createtime DESC;
INSERT INTO study(studyId,patient_fk,studyInstanceUID ,accessionNumber, studyDatetime, refPhysician, studyDesc, availability, modality, checkedTime ,updateTime,createTime)
SELECT studyId,patientId,studyInstanceUID ,accessionNumber, studyDatetime, refPhysician, studyDesc, availability, modality, checkedTime ,updateTime,createTime FROM dicomEntity a WHERE NOT EXISTS(SELECT studyInstanceUID FROM study b WHERE a.studyInstanceUID=b.studyInstanceUID) GROUP BY studyInstanceUID ORDER BY createtime DESC;
INSERT INTO series(seriesNumber ,patient_fk, study_fk ,seriesInstanceUID ,seriesDatetime , seriesDesc , availability , modality , seriesNo , sourceAET , seriesInStudy ,updateTime,createTime)
SELECT seriesNumber,patientId,studyInstanceUID,seriesInstanceUID ,seriesDatetime , seriesDesc , availability , modality , seriesNo , sourceAET , seriesInStudy ,updateTime,createTime FROM dicomEntity a
WHERE NOT EXISTS(SELECT seriesInstanceUID FROM series b WHERE a.seriesInstanceUID=b.seriesInstanceUID) GROUP BY seriesInstanceUID ORDER BY createtime DESC;
DELETE FROM dicomEntity ;
COMMIT;
END