-- Start of DDL Script for Function SD_HOSPITAL.SF_ZS_GETYZYYXX
-- Generated 22-四月-2016 16:53:52 from SD_HOSPITAL@FJLNYY

CREATE OR REPLACE 
FUNCTION sf_zs_getyzyyxx(ad_ZYID00 number)
return varchar2 is
  ls_result varchar2(500);
  ls_BRXM00 varchar2(100);
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- dsm        2016.04.13   create for ZYYS-20160411-001
begin
   ls_result:=' ';
   for c_yz in (select b.BRXM00,a.ZLXMJC,b.jkYYH0,to_char(to_date(b.YYRQ00,'YYYYMMDD'),'YYYY.MM.DD') YYRQ00,b.YYSJ00,YJKSMC from YJ_YW0000 a,YJ_YYBRB0 b  where a.ZYGHID=ad_ZYID00 
                    and a.MZZYBZ='1' 
                    and a.YJDJH0=b.YJDJH0 and b.YYZT00<>'2' and b.YYRQ00>=to_char(sysdate,'YYYYMMDD') and a.XMZT00<>'6'
               )
   loop
     ls_BRXM00:=c_yz.BRXM00;
     if ls_result=' ' then 
       ls_result:='  '||c_yz.ZLXMJC||', 预约时间:'||c_yz.YYRQ00||'日 '||trim(c_yz.YYSJ00)||'时, 预约号:'||c_yz.jkYYH0;
     else
       ls_result:=ls_result||','||chr(13)||'  '||'['||c_yz.ZLXMJC||']项目, 预约时间:'||c_yz.YYRQ00||' '||c_yz.YYSJ00||' 预约号:'||c_yz.jkYYH0;
     end if;  
   end loop;               
   if ls_result<>' ' then
     ls_result:=ls_BRXM00||'病人的预约信息如下:'||chr(13)||ls_result;
   end if;
   return ls_result;
end;
/



-- End of DDL Script for Function SD_HOSPITAL.SF_ZS_GETYZYYXX