prompt
prompt Creating function SF_YF_GETZD0
prompt ==============================
prompt
CREATE OR REPLACE FUNCTION SD_HOSPITAL.SF_YF_GETZD0(
 p_GHH000    IN  CHAR         --输入挂号号
 )RETURN CHAR IS           --疾病诊断

CURSOR  C_YS_BRZDXX  IS                                          --定义药品盘盈数据集游标
  SELECT Trim(ZDMC00)  FROM  YS_BRZDXX
   WHERE GHH000=p_GHH000 AND ZZDBZ0 not in('3');

v_Return      VARCHAR2(1000);
V_ZDMC00      YS_BRZDXX.ZDMC00%TYPE;
v_ZZDBZ0      YS_BRZDXX.ZZDBZ0%TYPE;
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- CSF      2007.05.29     取出所有诊断
-- YANGY    2007.07.16     如果没有主要诊断，取其他诊断
-- CSF      2009.07.09     所有诊断字符长度超过200的话，则出现异常，现将长度改成1000，但返回仍旧截取200
-- laijg    2012.12.25     去除中医诊断 by YF-20121220-002
-- daihq    2014.08.08     去掉逗号后面的空格，归档 by YF-20140722-002
---------   ------------   ---------------------------------------------------------
BEGIN

  --先查询主诊断
  begin
    SELECT nvl(Trim(ZDMC00),'') INTO v_Return
    FROM YS_BRZDXX  WHERE GHH000=p_GHH000 AND ZZDBZ0='3' and rownum=1;
  exception
    when others then
    v_Return := ' ';
  end;

  --查询其他诊断
  OPEN  C_YS_BRZDXX;                                        --打开游标
  FETCH  C_YS_BRZDXX  INTO  V_ZDMC00;
  WHILE  C_YS_BRZDXX%FOUND  LOOP
    if v_Return<>' ' then
      v_Return:=v_Return||','||trim(V_ZDMC00);
    else
      v_Return:=trim(V_ZDMC00);
    end if;
    FETCH  C_YS_BRZDXX  INTO  V_ZDMC00;
  END  LOOP;      ---FOR  WHILE  ...  LOOP  OUTER
  CLOSE  C_YS_BRZDXX;

  RETURN substr(v_Return,1,200);
END;
/