CREATE OR REPLACE FUNCTION SF_YF_GETZYZD_LY(
 p_ghzyid    IN  number         --输入挂号住院Id
 )RETURN CHAR IS                --疾病诊断

CURSOR  C_YS_BRZDXX  IS                                          
  SELECT Trim(ZDMC00)  FROM  bq_brzdxx
   WHERE ZYID00=p_ghzyid AND ZDLB00<>'3';

v_Return      VARCHAR2(2000);
V_ZDMC00      VARCHAR2(2000);
--V_ZDMC00      YS_BRZDXX.ZDMC00%TYPE;
v_ZZDBZ0      YS_BRZDXX.ZZDBZ0%TYPE;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGH       2009.10.28  create：获取病人全部诊断 
-- liuj        2011.04.27   v_Return:=v_Return||', '||V_ZDMC00;---》v_Return:=v_Return||','||V_ZDMC00;
--laijg        2011.05.25   扩展v_Return返回字节大小 
-- ---------   ----------  -------------------------------------------
BEGIN
  --先查询主诊断
  SELECT nvl(Trim(ZDMC00),'') INTO v_Return
    FROM bq_brzdxx  WHERE ZYID00=p_ghzyid AND zdlb00='3' and rownum=1;
     
  
  --查询其他诊断
  OPEN  C_YS_BRZDXX;                                        --打开游标
  FETCH  C_YS_BRZDXX  INTO  V_ZDMC00;
  WHILE  C_YS_BRZDXX%FOUND  LOOP
    IF INSTR(','||v_Return||',',','||V_ZDMC00||',')<=0 then
      v_Return:=v_Return||','||V_ZDMC00;
    END IF;
    FETCH  C_YS_BRZDXX  INTO  V_ZDMC00;
  END  LOOP;      ---FOR  WHILE  ...  LOOP  OUTER
  CLOSE  C_YS_BRZDXX;
  v_Return:= replace(replace(v_Return,'[','('),']',')');
  RETURN v_Return;
END;