create or replace function SF_YF_GETZYBRZDXX(
 p_zyid00    in  number,         --输入挂号号

 p_SFXYZD    in  char --,         --输入诊断类别 ‘1’西药诊断 ‘0’中药诊断 2全部
 --p_ZDLB00    in  char default '-1'  -- 诊断标志-->XT_ZDLB00西医'3'主要,'4'其他;中医'1'疾病,'2'症候

 
 )return char is           --疾病诊断

cursor C_GETZD is
    select ZDLB00,TRIM(ZDMC00) ZDMC00 
      from BQ_BRZDXX where ZYID00=p_zyid00 and (ZDFS00=p_SFXYZD or p_SFXYZD='2')
        order by decode(ZDLB00,'2',0,'8',1,'3',2,'a',3,'b',4,'c',5,'g',6,99);
V_SYZDMC      VARchar2(500);   --所有诊断名称


v_ZDLB00      BQ_BRZDXX.ZDLB00%type;
V_ZDMC00      BQ_BRZDXX.ZDMC00%type;
-- modification history
-- Person      Date        Comments
-- qks         2015.07.15  取出所有诊断，并分西医和中医

-- liwm        2019.04.02  排序调整，初步诊断-入院诊断-主要诊断……YF9-20190328-001

--             2020.04.20  西药房主任调整。入院诊断最前
-- ---------   ----------  --------------------------------------------------
begin
  V_SYZDMC:=' ';
  open C_GETZD;
  fetch C_GETZD into v_ZDLB00,V_ZDMC00;
  while C_GETZD%found loop
    if instrb(V_SYZDMC,V_ZDMC00)<=0 then
      V_SYZDMC:=substrb(V_SYZDMC||','||V_ZDMC00,1,500);
    end if;  
  fetch C_GETZD into v_ZDLB00,V_ZDMC00;
  end loop;
  close C_GETZD;
 if instr(V_SYZDMC,',')=2 then V_SYZDMC:=substr(V_SYZDMC,3); end if;  
 return V_SYZDMC;
end;