prompt 创建过程处理科室排班医生叫号队列sp_ys_ksysdlcl
CREATE OR REPLACE 
PROCEDURE sd_hospital.sp_ys_ksysdlcl(
 P_GHHBID in number,--输入挂号号表id
 P_YSKSBH in number,--医生科室编号
 P_BRID00 in number,--输入病人id
 P_HZXH00 in number,--候诊序号 
 P_ID0000 in number,--候诊id
 as_YHMSG0    OUT   VARCHAR,      --存储过程提示的中文错误信息
 as_SYSMSG    OUT   VARCHAR      --系统提示的错误信息
 )
AS
 v_count number(5);--计数器
 ls_GHHZCK YS_KSJHDL.GHHZCK%TYPE;--医生诊室窗口
 ls_JLID00 YS_KSJHDL.JLID00%TYPE;--记录id
 ls_YSYGBH YS_KSJHDL.YGBH00%TYPE;--医生员工编号
 i_YXJHGS      number(5,0);  --允许叫号个数
 Vcounter      number(10);                 --计数器变量 
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- CSF      2009.12.03    创建过程处理科室排班医生叫号队列
-- ----------  ----------  --------------------------------------------------------------
begin
	
	--取参数
  BEGIN
	select to_number(trim(VALUE0)) into i_YXJHGS from XT_XTCS00 where NAME00='YS_YXJHGS';
  EXCEPTION
    WHEN others then
      i_YXJHGS:=0;
  END;
  if i_YXJHGS=0 then
     i_YXJHGS:=1;
  end if;  

  as_YHMSG0:='修改医生叫号队列,并进行叫号处理'||'sp_ys_ksysdlcl( '||to_char(P_GHHBID)||','||to_char(P_YSKSBH)||','||
    to_char(P_BRID00)||','||to_char(P_HZXH00)||','||to_char(P_ID0000)||')';
  
  --select count(*)  into v_count --判断是否存在等待记录
    --from YS_KSJHDL where  CZRQ00=to_char(sysdate,'YYYYMMDD') and GHKSBH=P_YSKSBH  and GHHBID=P_GHHBID and BRID00=0 and SFYX00='1';
  
  --if v_count>0 then
  	select nvl(min(JLID00),0) into  ls_JLID00 
  	  from YS_KSJHDL a where  CZRQ00=to_char(sysdate,'YYYYMMDD') and GHKSBH=P_YSKSBH  and GHHBID=P_GHHBID and BRID00=0 and SFYX00='1' 
  	  and exists (select 1 from XT_YHCS00 where NAME00='YS_JZZTSZ' and VALUE0='1' and YGBH00=a.YGBH00);
  	
  	if ls_JLID00>0 then
       --叫号处理 

         select count(*) into Vcounter from sf_hzbr00
         where  hzzt00 in ('4','5','1') and ghys00=ls_YSYGBH and jzks00=P_YSKSBH and
              hzqsrq=to_char(sysdate,'yyyymmdd'); 
         if  Vcounter<i_YXJHGS  then
      	   select GHHZCK,YGBH00 into ls_GHHZCK,ls_YSYGBH from YS_KSJHDL where JLID00=ls_JLID00;
      	   
  	       update YS_KSJHDL set BRID00=P_BRID00,HZXH00=P_HZXH00,ID0000=P_ID0000 where JLID00=ls_JLID00;    
  	       --叫号处理
           Update SF_HZBR00 set hzzt00='4',JZRQ00=to_char(sysdate,'yyyymmdd'),
            JZSJ00=to_char(sysdate,'hh24:mi:ss') where id0000=P_ID0000;

           
           update SF_BRXXB0 SET JZYS00=ls_YSYGBH,JZYSXM=(Select ZWXM00 from BM_YGBM00 where YGBH00=ls_YSYGBH) where
            GHH000=(Select GHH000 from SF_HZBR00 where ID0000=P_ID0000);
            --修改
           update SF_HZBR00 SET ghys00=ls_YSYGBH,ghysxm=(select ZWXM00 from BM_YGBM00 where YGBH00=ls_YSYGBH ),GHHZCK=ls_GHHZCK where id0000=P_ID0000;
         end if;            
    end if;
 -- end if;
  as_YHMSG0:='';
exception
  when others then
     RAISE_APPLICATION_ERROR(-20001,substrb(as_YHMSG0||as_SYSMSG,1,200));
     rollback;
     return;
  		  
end;
/

SHOW ERROR;