平台数据对照

HIS数据库平台字典表和对照表

local_center_dic：平台字典表

pt_compare 或者 local_compare ：his与平台编码的对照表

建表语句：

复制代码
-- Create table
create table LOCAL_COMPARE
(
type VARCHAR2(100) not null,
his_code VARCHAR2(100) not null,
his_name VARCHAR2(200),
center_code VARCHAR2(100),
center_name VARCHAR2(200),
spell_code VARCHAR2(50),
wb_code VARCHAR2(50),
oper_code VARCHAR2(6),
oper_date DATE default sysdate
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 16K
next 8K
minextents 1
maxextents unlimited
);
-- Add comments to the table 
comment on table LOCAL_COMPARE
is '通用数据对照表';
-- Add comments to the columns 
comment on column LOCAL_COMPARE.type
is '类型:MZDZFP 门诊电子发票 ZYDZFP 住院电子发票';
comment on column LOCAL_COMPARE.his_code
is 'HIS编码';
comment on column LOCAL_COMPARE.his_name
is 'HIS名称';
comment on column LOCAL_COMPARE.center_code
is '中心编码';
comment on column LOCAL_COMPARE.center_name
is '中心名称';
comment on column LOCAL_COMPARE.spell_code
is '拼音码';
comment on column LOCAL_COMPARE.wb_code
is '五笔码';
comment on column LOCAL_COMPARE.oper_code
is '操作员';
comment on column LOCAL_COMPARE.oper_date
is '操作时间';

 

-- Create table
create table LOCAL_CENTER_DIC
(
type VARCHAR2(100) not null,
code VARCHAR2(100) not null,
name VARCHAR2(200),
mark VARCHAR2(200),
spell_code VARCHAR2(50),
wb_code VARCHAR2(50),
input_code VARCHAR2(100),
sort_id NUMBER default 0,
valid_state VARCHAR2(1) default '1' not null,
oper_code VARCHAR2(6),
oper_date DATE default sysdate,
mark1 VARCHAR2(50) default '99' not null,
mark2 VARCHAR2(50),
mark3 VARCHAR2(50)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 8K
minextents 1
maxextents unlimited
);
-- Add comments to the table 
comment on table LOCAL_CENTER_DIC
is '通用对照-数据中心字典表';
-- Add comments to the columns 
comment on column LOCAL_CENTER_DIC.type
is '类型';
comment on column LOCAL_CENTER_DIC.code
is '编码';
comment on column LOCAL_CENTER_DIC.name
is '名称';
comment on column LOCAL_CENTER_DIC.mark
is '备注';
comment on column LOCAL_CENTER_DIC.spell_code
is '拼音码';
comment on column LOCAL_CENTER_DIC.wb_code
is '五笔码';
comment on column LOCAL_CENTER_DIC.input_code
is '输入码';
comment on column LOCAL_CENTER_DIC.sort_id
is '顺序号';
comment on column LOCAL_CENTER_DIC.valid_state
is '有效性标志 1在用 0停用';
comment on column LOCAL_CENTER_DIC.oper_code
is '操作员';
comment on column LOCAL_CENTER_DIC.oper_date
is '操作时间';
-- Create/Recreate indexes 
create index INDEX_LOCAL_CENTER_DIC on LOCAL_CENTER_DIC (TYPE)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index INDEX_LOCAL_CENTER_DIC2 on LOCAL_CENTER_DIC (CODE, NAME, SPELL_CODE, WB_CODE)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
复制代码

--HIS对照功能配置的XML :

pt_CommonCompareConfig.xml

--HIS对照函数：

复制代码
create or replace function fun_get_pf_compare(IN_TYPE varchar2,
IN_HIS_CODE varchar2,
IN_PF_CODE_DEF varchar2,
ai_flag in number)
return varchar2 is
/*-----------------------------------------------------------------------
过程名称 ：fun_get_pf_code
功能描述 ：根据his编码获取平台编码
IN_TYPE : ；平台编码类型
IN_HIS_CODE ：his编码
IN_PF_CODE_DEF：平台默认编码
ai_flag ： 返回值类型 1编码 2名称

-----------------------------------------------------------------------*/

TEMP_CODE varchar2(200);
TEMP_NAME varchar2(200);
begin

if IN_HIS_CODE is null or IN_HIS_CODE = '' or trim(IN_HIS_CODE) = '' or trim(IN_HIS_CODE) is null then

SELECT CC.CENTER_CODE, CC.CENTER_NAME
INTO TEMP_CODE, TEMP_NAME
FROM LOCAL_COMPARE CC
WHERE CC.TYPE = IN_TYPE
AND CC.CENTER_CODE = IN_PF_CODE_DEF
and rownum=1;
else

SELECT CC.CENTER_CODE, CC.CENTER_NAME
INTO TEMP_CODE, TEMP_NAME
FROM LOCAL_COMPARE CC
WHERE CC.TYPE = IN_TYPE
AND CC.HIS_CODE = IN_HIS_CODE
and rownum=1;

end if;

if TEMP_NAME is null THEN
TEMP_CODE := IN_PF_CODE_DEF;
TEMP_NAME := '';
END IF;

--返回值
if (ai_flag = 1) then
return TEMP_CODE;
else
if (ai_flag = 2) then
return TEMP_NAME;
else
return TEMP_CODE;
end if;
end if;

end fun_get_pf_compare;
复制代码

--应用对照函数：

fun_get_pf_compare('ICD-10',d.icd_code ,'Z00.001',1) --诊断编码
fun_get_pf_compare('ICD-10',d.icd_code ,'Z00.001',2) --诊断名称

--导入平台字典数据

先建dblink，然后参照以下SQL

insert into local_center_dic
SELECT range_code,value_code,value_name,type_,'','','',0,1,'',sysdate,'99','99','99' FROM nhip_mdm.COM_DICT@DRPT where range_code = 'ICD-10'

--诊断模糊匹配

复制代码
insert into pt_compare
select distinct 'ICD-10',a.icd10_code,a.name,
(select b.code from local_center_dic b where  b.TYPE = 'ICD-10' and (a.icd10_code = b.code or a.name = b.name) and rownum =1),
(select b.name from local_center_dic b where  b.TYPE = 'ICD-10' and (a.icd10_code = b.code or a.name = b.name) and rownum =1),
a.spell_code,a.wb_code,'admin',sysdate
from dxt_western_disease a, local_center_dic b 
where 
 b.TYPE = 'ICD-10' and (a.icd10_code = b.code or a.name = b.name);
commit;

insert into pt_compare
select distinct
 'ICD-10',a.icd10_code,a.name,
(select b.code from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,7) = b.code and rownum =1),
(select b.name from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,7) = b.code and rownum =1),
a.spell_code,a.wb_code,'admin',sysdate
from dxt_western_disease a ,local_center_dic b
where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10')
and substr(a.icd10_code,1,7) = b.code;
commit;


insert into pt_compare
select distinct
 'ICD-10',a.icd10_code,a.name,
(select b.code from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,7) = substr(b.code,1,7)  and rownum =1),
(select b.name from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,7) = substr(b.code,1,7) and rownum =1),
a.spell_code,a.wb_code,'admin',sysdate
from dxt_western_disease a ,local_center_dic b
where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10')
and substr(a.icd10_code,1,7) = substr(b.code,1,7) ;
commit;

insert into pt_compare
select distinct
 'ICD-10',a.icd10_code,a.name,
(select b.code from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,6) = substr(b.code,1,6)  and rownum =1),
(select b.name from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,6) = substr(b.code,1,6) and rownum =1),
a.spell_code,a.wb_code,'admin',sysdate
from dxt_western_disease a ,local_center_dic b
where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10')
and substr(a.icd10_code,1,6) = substr(b.code,1,6) ;
commit;

insert into pt_compare
select distinct
 'ICD-10',a.icd10_code,a.name,
(select b.code from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,5) = substr(b.code,1,5)  and rownum =1),
(select b.name from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,5) = substr(b.code,1,5) and rownum =1),
a.spell_code,a.wb_code,'admin',sysdate
from dxt_western_disease a ,local_center_dic b
where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10')
and substr(a.icd10_code,1,5) = substr(b.code,1,5) ;
commit;


insert into pt_compare
select distinct
 'ICD-10',a.icd10_code,a.name,
(select b.code from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,4) = substr(b.code,1,4)  and rownum =1),
(select b.name from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,4) = substr(b.code,1,4) and rownum =1),
a.spell_code,a.wb_code,'admin',sysdate
from dxt_western_disease a ,local_center_dic b
where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10')
and substr(a.icd10_code,1,4) = substr(b.code,1,4) ;
commit;


insert into pt_compare
select distinct
 'ICD-10',a.icd10_code,a.name,
(select b.code from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,3) = substr(b.code,1,3)  and rownum =1),
(select b.name from local_center_dic b where  b.TYPE = 'ICD-10' and substr(a.icd10_code,1,3) = substr(b.code,1,3) and rownum =1),
a.spell_code,a.wb_code,'admin',sysdate
from dxt_western_disease a ,local_center_dic b
where a.icd10_code not in (select his_code from pt_compare where type = 'ICD-10')
and substr(a.icd10_code,1,3) = substr(b.code,1,3);
commit;

--未匹配的诊断查询
select t.* from dxt_western_disease t 
where t.icd10_code not in (select his_code from pt_compare where type = 'ICD-10')
复制代码

 

 

posted on   巍巍之道  阅读(146)  评论(0)  编辑  收藏  举报

编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· 一文读懂知识蒸馏
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下

导航

< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示