CREATE OR REPLACE FUNCTION SF_XT_GETGJBZBMForICD900
(
  PICD900 varchar2,            --ICD9码
  PFHLX00 varchar2,            --返回类型, 1国家医保编码2国家医保名称3用逗号拼接编码和名称
  PZDLB00 varchar2 default '0' --诊断类别, 0西医1中医疾病2中医症候
)
return  varchar2 is
-- modification history
-- Person      Date            Comments
-- linzetao    2021.12.15      create获取ICD900编码对应的国家医保编码及名称    MZSF9EX-20211214-001
-- linzetao    2022.05.16      处理诊断码未取到时再取中医疾病及中医症候   ZYSF9EX-20220516-001
  ls_RETURN  varchar2(300);
  ls_DYBZBM  XT_ICD900.DYBZBM%type;
  ls_DYJBMC  XT_ICD900.DYJBMC%type;
begin

    ls_RETURN := null;

    begin
      select DYBZBM,DYJBMC into ls_DYBZBM,ls_DYJBMC from XT_ICD900 where ICD900=PICD900 and SFYX00='1';
    exception
      when others then
        ls_DYBZBM := null;
        ls_DYJBMC := null;
    end;

    if LS_DYBZBM is null or PZDLB00='1' then
      begin
        select substrb(nvl(DYBZBM,JBBH00),1,50) as DYBZBM,substrb(nvl(DYJBMC,JBMC00),1,200) as DYJBMC
          into LS_DYBZBM,LS_DYJBMC
          from BM_ZYJB00 a where  a.JBBH00=PICD900 and  nvl(SYBZ00,'1')='1' and rownum=1;
      exception
        when others then
         LS_DYBZBM := null;
         LS_DYJBMC := null;
      end;
    end if;

  --取症候 2
    if LS_DYBZBM is null or PZDLB00='2' then
      begin
        select substrb(nvl(DYBZBM,ZHBH00),1,50) as DYBZBM,substrb(nvl(DYJBMC,ZHMC00),1,200) as DYJBMC
          into LS_DYBZBM,LS_DYJBMC
          from BM_ZYZH00 a where  a.ZHBH00=PICD900 and  nvl(SYBZ00,'1')='1' and rownum=1;
      exception
        when others then
          LS_DYBZBM := null;
          LS_DYJBMC := null;
      end;
    end if;

    if PFHLX00 = '1' then
      ls_RETURN := ls_DYBZBM;
    elsif PFHLX00 = '2' then
      ls_RETURN := ls_DYJBMC;
    elsif PFHLX00 = '3' then
      ls_RETURN := ls_DYBZBM||','||ls_DYJBMC;
    end if;

  return ls_RETURN;
end;