Loading... > 前置步骤：需要his提供视图 v_hisitemtopacs： itemcode：项目ID itemname：项目名称 pycode：拼音码 reqcode：申请单类型ID reqname：申请单类型名称 checkid：检查方法ID checkname：检查方法名称 positid：大部位ID positname：大部位名称 * 其中reqcode和reqname需要我们自己根据申请单类型用decode函数转换一下 <div class="tip inlineBlock warning"> 下面是在HIS创建的视图（现场HIS环境RS6版本） </div> ```sql create or replace view v_hisitemtopacs as with t1 as ( select t.sheet_type as reqcode, (select d.name from hit_app.com_dictionary d where d.type = 'EXAMSHEETTYPE' and d.code = t.sheet_type ) as reqname, t.term_id as itemid, t.term_name as itemname, m.exam_method as checkid, (select c.cons_info_name from hit_app.dawn_code_info c where c.cons_type_id = 'EXAMMETHOD' and c.cons_info_code = m.exam_method) as checkname, m.EXAM_BIG_PART as positid, b.big_exam_part_name as positname from hit_app.met_ordt_undrugterm t ,hit_app.MET_ORDE_EXAMCOMPARE m, hit_app.met_orde_bigexampart b, hit_app.met_orde_exampart e where t.term_class_id = '155' and t.sheet_type is not null and t.term_id = m.term_id and b.big_exam_part_id(+) = m.EXAM_BIG_PART and e.exam_part_id(+) = m.EXAM_PART ), t2 as ( select decode(REQNAME, 'B超', '5', 'X线', '7', 'B超(妇产科)','5','CT','3') as reqcode, decode(REQNAME, 'B超', '超声', 'X线', 'DR', 'B超(妇产科)', '超声','CT','CT') as reqname, itemid, itemname, --fn_getpy(SUBSTR(itemname, 1, 6),4) as pycode, nvl(CHECKID,'0') as CHECKID, nvl(CHECKNAME,'无') as CHECKNAME, nvl(decode(POSITID,'24175',null,POSITID),'999') as POSITID , nvl(POSITNAME,'其他') as POSITNAME from t1 where reqname <> '心脑电图') ``` * 00、同步his检查项目用，先在pacs库执行，添加字段 ```sql ALTER TABLE studypositioninfo ADD (positid NUMBER, checkid NUMBER,devicetypeid NUMBER); COMMENT ON COLUMN studypositioninfo.positid IS '用于同步his检查项目关联部位'; COMMENT ON COLUMN studypositioninfo.checkid IS '用于同步his检查项目关联部位'; COMMENT ON COLUMN studypositioninfo.devicetypeid IS '用于同步his检查项目关联部位'; ``` * 01、先创建备份，需要时可恢复 ```sql create table studypositioninfo_b as select * from studypositioninfo; create table POSITIONTYPEINFO_b as select * from POSITIONTYPEINFO; create table positiontypetoposition_b as select * from positiontypetoposition; create table Checkitem_b as select * from Checkitem; create table devicetypetocheckitem_b as select * from devicetypetocheckitem; create table devicetype_position_checkitem_b as select * from devicetype_position_checkitem; ``` * ====根据上面备份的表 恢复数据====按需执行=== ```sql truncate table studypositioninfo; insert into studypositioninfo select * from studypositioninfo_b ; COMMIT; truncate table POSITIONTYPEINFO; insert into POSITIONTYPEINFO select * from POSITIONTYPEINFO_b ; COMMIT; truncate table positiontypetoposition; insert into positiontypetoposition select * from positiontypetoposition_b ; COMMIT; truncate table Checkitem; insert into Checkitem select * from Checkitem_b ; COMMIT; truncate table devicetypetocheckitem; insert into devicetypetocheckitem select * from devicetypetocheckitem_b ; COMMIT; truncate table devicetype_position_checkitem; insert into devicetype_position_checkitem select * from devicetype_position_checkitem_b ; COMMIT; ``` * 02、删除对应表数据（新现场则操作，如果是已经上线的现场则可以不用） ```sql delete studypositioninfo; commit; delete POSITIONTYPEINFO where positiontypeid <>'999' ; --不删999 其他 commit; delete positiontypetoposition; commit; delete Checkitem where checkitemid<>'0'; --不删0 无 commit; delete devicetypetocheckitem; commit; delete devicetype_position_checkitem; commit; ``` > 下面就是直接批量insert了： ```sql --03、同步检查项目 INSERT INTO studypositioninfo ( STUDYPOSITIONID, STUDYPOSITION, STUDYPOSITIONCODE, ISAVAILABLE, OPERATORID, OPERATETIME, ORDERNO, positid, checkid, devicetypeid ) SELECT COALESCE((SELECT MAX(STUDYPOSITIONID) FROM studypositioninfo), 1000) + ROW_NUMBER() OVER (ORDER BY itemname) AS STUDYPOSITIONID, itemname, fn_getpy(SUBSTR(itemname, 1, 6),4) as pycode, 1 AS ISAVAILABLE, 'SQLAuto' AS OPERATORID, SYSDATE AS OPERATETIME, 0 AS ORDERNO, positid, checkid, reqcode FROM his50.v_hisitemtopacs WHERE itemname NOT IN ( SELECT STUDYPOSITION FROM studypositioninfo ); commit; --04、同步大部位 INSERT INTO POSITIONTYPEINFO ( POSITIONTYPEID, POSITIONTYPENAME, ISAVAILABLE, OPERATORID, OPERATETIME, ORDERNO ) SELECT t2.positid, t2.positname, 1, 'SQLAuto', SYSDATE, 0 FROM (SELECT DISTINCT positid, positname FROM his50.v_hisitemtopacs ) t2 LEFT JOIN POSITIONTYPEINFO ON t2.positname = POSITIONTYPEINFO.POSITIONTYPENAME WHERE POSITIONTYPEINFO.POSITIONTYPENAME IS NULL; commit; --05、同步项目对应的大部位 INSERT INTO positiontypetoposition ( POSITIONTYPEID, STUDYPOSITIONID, OPERATORID, OPERATETIME ) SELECT studypositioninfo.positid, studypositioninfo.STUDYPOSITIONID, 'SQLAuto', SYSDATE FROM studypositioninfo LEFT JOIN positiontypetoposition ON studypositioninfo.STUDYPOSITIONID = positiontypetoposition.STUDYPOSITIONID WHERE positiontypetoposition.STUDYPOSITIONID IS NULL and studypositioninfo.positid is not null; commit; --06、同步检查方法 INSERT INTO Checkitem ( CHECKITEMID, CHECKITEM, ISAVAILABLE, OPERATORID, OPERATETIME, REMARKINFO ) SELECT t2.checkid, t2.checkname, 1, 'SQLAuto', SYSDATE, NULL FROM (SELECT DISTINCT checkid, checkname FROM his50.v_hisitemtopacs ) t2 LEFT JOIN Checkitem ON t2.checkname = Checkitem.CHECKITEM WHERE Checkitem.CHECKITEM IS NULL ; commit; --07、同步设备类型对应的检查方法 INSERT INTO devicetypetocheckitem ( DEVICETYPEID, CHECKITEMID, OPERATORID, OPERATETIME ) SELECT DISTINCT t2.reqcode, t2.checkid, 'SQLAuto', SYSDATE FROM his50.v_hisitemtopacs t2 WHERE NOT EXISTS ( SELECT 1 FROM devicetypetocheckitem WHERE devicetypetocheckitem.DEVICETYPEID = t2.reqcode AND devicetypetocheckitem.CHECKITEMID = t2.checkid ); commit; --08、同步设备类型对应的部位和检查方法 INSERT INTO devicetype_position_checkitem ( DEVICETYPEID, STUDYPOSITIONID, CHECKITEMID, OPERATORID, OPERATETIME ) SELECT sp.devicetypeid, sp.STUDYPOSITIONID, sp.checkid, 'SQLAuto' AS OPERATORID, SYSDATE AS OPERATETIME FROM studypositioninfo sp LEFT JOIN devicetype_position_checkitem dpc ON sp.devicetypeid = dpc.DEVICETYPEID AND sp.STUDYPOSITIONID = dpc.STUDYPOSITIONID AND sp.checkid = dpc.CHECKITEMID WHERE sp.devicetypeid IS NOT NULL AND sp.checkid IS NOT NULL AND dpc.DEVICETYPEID IS NULL AND dpc.STUDYPOSITIONID IS NULL AND dpc.CHECKITEMID IS NULL; commit; /* == 下面是现场特殊要求 如果默认使用his申请单接口带过来的检查方法 可不执行下面SQL */ --09、所有检查项目关联 检查方法 “无” （现场特殊要求） INSERT INTO devicetype_position_checkitem ( DEVICETYPEID, STUDYPOSITIONID, CHECKITEMID, OPERATORID, OPERATETIME ) select DEVICETYPEID, STUDYPOSITIONID, '0' as CHECKITEMID, 'SQLAuto1' as OPERATORID, SYSDATE as OPERATETIME from DEVICETYPE_POSITION_CHECKITEM t where t.checkitemid <> '0' and t.STUDYPOSITIONID not in ( select STUDYPOSITIONID from DEVICETYPE_POSITION_CHECKITEM a where a.checkitemid='0'); commit; ``` 最后修改：2024 年 04 月 25 日 © 允许规范转载 赞 如果觉得我的文章对你有用，请随意赞赏