CREATE OR REPLACE TRIGGER TR_SF_YSGHHB_BEFINSAPDEL BEFORE INSERT OR DELETE OR UPDATE OF YYXHS0, DHYYMX, DHMX02, FZYYS0, WLYYS0, FZYYMX, PJZZSJ, WLYYMX, GHXHS0, DHYYS0, DHYY02 ON SD_SF.SF_YSGHHB REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare ErrStr varchar2(200); Cs_Ys_Xzygh0 CHAR(1); E_Ghxhs0 EXCEPTION; E_Yyxhs0 EXCEPTION; V_Ghxhs0 NUMBER(6); V_Count0 NUMBER(5); V_Yyxhs0 NUMBER(6); Vmessage varchar2(20); V_HZQSXH NUMBER(5);--候诊起始序号 v_YYHFS char(1);--预约号处理方式 ‘0’ 指定预约号并按偶数位处理 ‘1’ 所有3的倍数为预约号。 v_FZYYMX sf_yspbb0.FZYYMX%type; v_DHYYMX sf_yspbb0.DHYYMX%type; v_WLYYMX sf_YSPBB0.WLYYMX%type; v_DHMX02 sf_YSPBB0.DHMX02%type; v_PJZZSJ sf_YSPBB0.PJZZSJ%type; v_HZSJTS char(1); --‘1’:写入YS_HBHZXH.HZQSSJ起始时间：早上08:00-11:30,下午：夏季 15:00-18:00,其他 14:30-17:30 v_GHQSSJ YS_HBHZXH.HZQSSJ%type; v_GHJSSJ YS_HBHZXH.HZQSSJ%type; v_HZQSSJ YS_HBHZXH.HZQSSJ%type; v_HZXH00 YS_HBHZXH.HZXH00%type; ls_YSPBSFHYWH XT_XTCS00.VALUE0%TYPE; v_COUNT1 number(5); ----记录YS_YSPBXH记录数 ls_YSPBID SF_YSPBB0.YSPBID%TYPE; ls_XQ0000 char(1); --存放星期几 v_COUNT2 number(5); --记录已挂号人数 -- MODIFICATION HISTORY -- Person Date Comments -- YANGY 2007.08.15 门诊护士产生挂号表时，也同时产生所有的候诊序号 -- 叶新燕 2008.12.30 候诊序号不能从指定的序号开始 给与纠正 -- CSF 2008.12.31 限号数增加，候诊序号没有正常增加 -- CSF 2009.05.13 南平市一 3倍数都为预约号 -- CSF 2010.09.29 排班维护，增加分诊台预约、电话预约、网络预约的维护 -- CSF 2010.09.29 纠正bug,分诊台预约、电话预约、网络预约的维护,只有一个数，且数据后面没有","则存在问题。 -- qks 2012.05.14 YS_HBHZXH增加HZQSSJ。 by YYGH-20120518-002 -- qks 2012.05.22 处理"网络预约数3"对应YS_HBHZXH.YYBZ00 by YYGH-20120522-001 -- qks 2012.06.02 只修改排班表中的"网络预约数3"，直接生成挂号号表时，要求YS_HBHZXH.HZQSSJ也要写入值 by YYGH-20120604-002 -- qks 2012.06.05 新增参数YY_HBHZQSSJ，当参数值为1时：1、属于南平市一模式,上午08:00-11:30,下午：夏季 15:00-18:00,其他 14:30-17:30，超过时段范围不写入YS_HBHZXH.HZQSSJ；by YYGH-20120605-001 -- csf 2013.02.26 YS_HBHZXH.HZQSSJ 判断号表时间段以外的时间为空的处理，判断时间段应根据日期和时间合并判断，否则判断不准确。 YYGH-20130219-001 -- csf 2014.01.28 南平市一，开展全预约 "总号数是预约限号数2倍的限制" MZSF-20140128-001 -- dsm 2014.02.07 YY_HBHZQSSJ参数设置为0时，排班时间内的才生成HZQSSJ for MZHS-20140207-001 -- linzy 2014.12.01 从YS_YSPBXH模板中生成号源 by MZHS-20141120-001 -- linzy 2014.12.17 修正排班表对号源重新进行维护后未同步到号表中 by MZSF-20141210-001 --- ---------- ------------ -------------------------------------------------------------------------------------------------------- Begin select upper(trim(nvl(max(VALUE0),'N'))) into Cs_Ys_Xzygh0 from XT_XTCS00 where NAME00='YS_HSGHXZHZXH'; select upper(trim(nvl(max(VALUE0),'0'))) into v_YYHFS from XT_XTCS00 where NAME00='MZHS_YYHFS'; select upper(trim(nvl(max(VALUE0),'0'))) into v_HZSJTS from XT_XTCS00 where NAME00='YY_HBHZQSSJ'; --取系统参数MZHS_YSPBSFHYWH select upper(trim(nvl(max(VALUE0),'N'))) into ls_YSPBSFHYWH from XT_XTCS00 where NAME00='MZHS_YSPBSFHYWH'; if v_HZSJTS = '1' then --目前只分上午和下午 if :new.GHQSSJ>='06:00' and :new.GHQSSJ<='10:00' then v_GHQSSJ:='08:00'; v_GHJSSJ:='11:30'; elsif :new.GHQSSJ>='14:00' and :new.GHQSSJ<='17:00' then if to_char(sysdate,'MM')>='06' and to_char(sysdate,'MM')<='09' then v_GHQSSJ:='15:00'; v_GHJSSJ:='18:00'; else v_GHQSSJ:='14:30'; v_GHJSSJ:='17:30'; end if; else --不特殊处理 v_HZSJTS:='0'; end if; end if; v_COUNT1:=0; ls_XQ0000 := to_char(to_Date(:new.GHMZSJ,'YYYY.MM.DD'),'D'); select nvl(max(YSPBID),0) into ls_YSPBID from SF_YSPBB0 where YSPBXQ = ls_XQ0000 and tzbz00='0' and YSKSBH=:new.GHKSBH and PBLX00='0' and YSYGBH=:new.GHYSBH and YSPBSJ=(select MZSJ00 from BM_MZSJB0 where KSSJ00=:new.GHQSSJ and JSSJ00=:new.GHJSSJ and SFYX00='Y'); if ls_YSPBSFHYWH='Y' then select count(*) into v_COUNT1 from YS_YSPBXH where YSPBID=ls_YSPBID and ls_YSPBID<>0; end if; if Cs_Ys_Xzygh0 in ('Y','M') then --2014.11.28 add by linzy for MZHS-20141120-001 if v_COUNT1>0 then update YS_HBHZXH a set (a.YYBZ00,A.ZZGHBZ)=(select b.YYBZ00,B.ZZGHBZ from YS_YSPBXH b where b.YSPBID=ls_YSPBID and b.PBXH00=a.HZXH00 and rownum=1 ) where a.BRID00 is null and GHHBID=:new.GHHBID and YXBZ00='1' and exists(select 1 from YS_YSPBXH c where c.YSPBID=ls_YSPBID and c.PBXH00=a.HZXH00); end if; if inserting then--增加号表记录 :NEW.MRXHS0 := :NEW.GHXHS0; V_HZQSXH:=:NEW.HZQSXH; V_Ghxhs0 := 1; --取平均诊治时间 v_PJZZSJ := nvl(trim(:NEW.PJZZSJ),Floor((to_date(:NEW.GHMZSJ||:NEW.GHJSSJ,'yyyymmddhh24:mi:ss')-to_date(:NEW.ghmzsj||:NEW.ghqssj,'yyyymmddhh24:mi:ss'))*24*60/:NEW.GHXHS0)); if v_COUNT1>0 then --删除未使用的旧数据 delete from YS_HBHZXH a where GHHBID=:new.GHHBID and BRID00 is null and not exists(select 1 from YS_YSPBXH where PBXH00=a.HZXH00 and YSPBID=ls_YSPBID); select count(*) into v_COUNT2 from YS_HBHZXH a where GHHBID=:new.GHHBID; for YS_YSPBXHDATA in (select PBXH00,YYBZ00,ZZGHBZ from YS_YSPBXH a where YSPBID=ls_YSPBID and not exists(select 1 from YS_HBHZXH where HZXH00=a.PBXH00 and GHHBID=:new.GHHBID)) loop if V_Ghxhs0<=(:new.GHXHS0-v_COUNT2) then Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),V_Ghxhs0); insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ) Values (SQ_YS_HBHZXH_ID0000.nextval,:new.GHHBID,YS_YSPBXHDATA.PBXH00,YS_YSPBXHDATA.YYBZ00,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'), YS_YSPBXHDATA.ZZGHBZ); V_Ghxhs0:= V_Ghxhs0+1; end if; end loop; else while V_Ghxhs0<=:NEW.GHXHS0 loop Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),V_Ghxhs0); if v_HZSJTS = '1' then if to_char(to_date(:NEW.ghmzsj||v_GHQSSJ,'yyyymmddhh24:mi:ss')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'yyyymmddhh24:mi:ss')>:NEW.ghmzsj||v_GHJSSJ or to_char(to_date(:NEW.ghmzsj||v_GHQSSJ,'yyyymmddhh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'yyyymmddhh24:mi:ss')<:NEW.ghmzsj||v_GHQSSJ then v_HZQSSJ := ''; else v_HZQSSJ:= to_char(to_date(v_GHQSSJ,'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'); end if; end if; Insert into YS_HBHZXH (ID0000, GHHBID, HZXH00,HZDXH0,HZSJ00,HZQSSJ) Values (SQ_YS_HBHZXH_ID0000.nextval, :new.GHHBID, V_Ghxhs0+V_HZQSXH-1,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), decode(v_HZSJTS,'1',v_HZQSSJ, to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi')) ); V_Ghxhs0:= V_Ghxhs0+1; end loop; end if; elsif Updating then--修改挂号限号数 V_HZQSXH := :NEW.HZQSXH; V_Ghxhs0 := :OLD.GHXHS0; --取平均诊治时间 v_PJZZSJ := nvl(trim(:NEW.PJZZSJ),Floor((to_date(:NEW.ghmzsj||:NEW.ghjssj,'yyyymmddhh24:mi:ss')-to_date(:NEW.ghmzsj||:NEW.ghqssj,'yyyymmddhh24:mi:ss'))*24*60/:NEW.GHXHS0)); if V_Ghxhs0<:NEW.GHXHS0 then--号改多 V_Ghxhs0 := :OLD.GHXHS0+1; if v_COUNT1>0 then delete from YS_HBHZXH a where GHHBID=:new.GHHBID and BRID00 is null and not exists(select 1 from YS_YSPBXH where PBXH00=a.HZXH00 and YSPBID=ls_YSPBID); select count(*) into v_COUNT2 from YS_HBHZXH where GHHBID=:new.GHHBID; for YSPBXHDATA in (select PBXH00,YYBZ00,ZZGHBZ from YS_YSPBXH a where YSPBID=ls_YSPBID and not exists(select 1 from YS_HBHZXH where HZXH00=a.PBXH00 and GHHBID=:new.GHHBID)) loop if v_COUNT2<=:NEW.GHXHS0 then Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),:new.GHXHS0); insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ) Values (SQ_YS_HBHZXH_ID0000.nextval,:new.GHHBID,YSPBXHDATA.PBXH00,YSPBXHDATA.YYBZ00,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'),YSPBXHDATA.ZZGHBZ ); V_Ghxhs0:= V_Ghxhs0+1; v_COUNT2:= v_COUNT2+1; end if; end loop; else while V_Ghxhs0<=:NEW.GHXHS0 loop if v_HZSJTS = '1' then if to_char(to_date(:NEW.ghmzsj||v_GHQSSJ,'yyyymmddhh24:mi:ss')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'yyyymmddhh24:mi:ss')>:NEW.ghmzsj||v_GHJSSJ or to_char(to_date(:NEW.ghmzsj||v_GHQSSJ,'yyyymmddhh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'yyyymmddhh24:mi:ss')<:NEW.ghmzsj||v_GHQSSJ then v_HZQSSJ := ''; else v_HZQSSJ:= to_char(to_date(v_GHQSSJ,'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'); end if; end if; Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),:new.GHXHS0); Insert into YS_HBHZXH (ID0000, GHHBID, HZXH00,HZDXH0,HZSJ00,HZQSSJ) Values (SQ_YS_HBHZXH_ID0000.nextval, :new.GHHBID, V_Ghxhs0+V_HZQSXH-1,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), decode(v_HZSJTS,'1',v_HZQSSJ, to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi')) ); V_Ghxhs0:= V_Ghxhs0+1; end loop; end if; elsif V_Ghxhs0>:NEW.GHXHS0 then--号改少 -- while (V_Ghxhs0+1)>=:NEW.GHXHS0 loop select count(1) into V_Count0 from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZXH00>=V_HZQSXH+(:NEW.GHXHS0) and BRID00 is not null; if V_Count0>0 then raise E_Ghxhs0; end if; if v_COUNT1>0 then delete from YS_HBHZXH a where GHHBID=:new.GHHBID and BRID00 is null and not exists(select 1 from YS_YSPBXH where PBXH00=a.HZXH00 and YSPBID=ls_YSPBID); select count(*) into v_COUNT2 from YS_HBHZXH where GHHBID=:new.GHHBID; if v_COUNT2>:new.GHXHS0 then delete from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZXH00 in (select HZXH00 from (select HZXH00,rownum from YS_HBHZXH where GHHBID=:NEW.GHHBID and BRID00 is null and rownum<=(v_COUNT2-:new.GHXHS0) order by HZXH00 desc)); elsif v_COUNT2<:new.GHXHS0 then v_COUNT2:=v_COUNT2+1; for YSPBXHDATA in (select PBXH00,YYBZ00,ZZGHBZ from YS_YSPBXH a where YSPBID=ls_YSPBID and not exists(select 1 from YS_HBHZXH where HZXH00=a.PBXH00 and GHHBID=:new.GHHBID)) loop if v_COUNT2<=:NEW.GHXHS0 then Vmessage:= SF_SF_HBHZSJ(:new.GHQSSJ,:new.GHJSSJ,least(:new.mrxhs0,:new.ghxhs0),:new.GHXHS0); insert into YS_HBHZXH (ID0000,GHHBID,HZXH00,YYBZ00,HZDXH0,HZSJ00,HZQSSJ,ZZGHBZ) Values (SQ_YS_HBHZXH_ID0000.nextval,:new.GHHBID,YSPBXHDATA.PBXH00,YSPBXHDATA.YYBZ00,substrb(Vmessage,1,instrb(Vmessage,',')-1),substrb(Vmessage,instrb(Vmessage,',')+1,11), to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(V_Ghxhs0-1)*to_number(v_PJZZSJ)/(24*60),'hh24:mi'),YSPBXHDATA.ZZGHBZ ); V_Ghxhs0:= V_Ghxhs0+1; v_COUNT2:=v_COUNT2+1; end if; end loop; end if; else Delete from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZXH00>=V_HZQSXH+(:NEW.GHXHS0) and BRID00 is null; end if; end if; --修改平均诊治时间 if nvl(:NEW.PJZZSJ,' ')<>nvl(:OLD.PJZZSJ,' ') or nvl(:NEW.DHYY02,0)<>nvl(:OLD.DHYY02,0) then if v_HZSJTS = '1' then update YS_HBHZXH set HZQSSJ=to_char(to_date(v_GHQSSJ,'hh24:mi')+(HZXH00-nvl(V_HZQSXH,1))*to_number(v_PJZZSJ)/(24*60),'hh24:mi') where GHHBID=:NEW.GHHBID and BRID00 is null and to_char(to_date(:NEW.ghmzsj||v_GHQSSJ,'yyyymmddhh24:mi:ss')+(HZXH00-nvl(V_HZQSXH,1))*to_number(v_PJZZSJ)/(24*60),'yyyymmddhh24:mi:ss')>=:NEW.ghmzsj||v_GHQSSJ and to_char(to_date(:NEW.ghmzsj||v_GHQSSJ,'yyyymmddhh24:mi:ss')+(HZXH00-nvl(V_HZQSXH,1))*to_number(v_PJZZSJ)/(24*60),'yyyymmddhhhh24:mi:ss')<=:NEW.ghmzsj||v_GHJSSJ --对于平均诊治时间太小，导致超过时间段后，时间点从第二天开始设置，导致同一个时间点出现多次 ; update YS_HBHZXH set HZQSSJ='' where GHHBID=:NEW.GHHBID and BRID00 is null and (to_char(to_date(:NEW.ghmzsj||v_GHQSSJ,'yyyymmddhh24:mi:ss')+(HZXH00-nvl(V_HZQSXH,1))*to_number(v_PJZZSJ)/(24*60),'yyyymmddhh24:mi:ss')<:NEW.ghmzsj||v_GHQSSJ or to_char(to_date(:NEW.ghmzsj||v_GHQSSJ,'yyyymmddhh24:mi:ss')+(HZXH00-nvl(V_HZQSXH,1))*to_number(v_PJZZSJ)/(24*60),'yyyymmddhh24:mi:ss')>:NEW.ghmzsj||v_GHJSSJ); else update YS_HBHZXH set HZQSSJ=to_char(to_date(substr(:new.GHQSSJ,1,5),'hh24:mi')+(HZXH00-nvl(V_HZQSXH,1))*to_number(v_PJZZSJ)/(24*60),'hh24:mi') where GHHBID=:NEW.GHHBID and BRID00 is null; end if; end if; else--删除号表记录 Delete from YS_HBHZXH where GHHBID=:OLD.GHHBID; end if; end if; --省二等医院要求预约号数不能多于总号数的一半 if Cs_Ys_Xzygh0='Y' and ls_YSPBSFHYWH='N' then if updating OR inserting then V_Yyxhs0 := :NEW.YYXHS0; --南平市一，开展全预约 去掉此限制 MZSF-20140128-001 -- if (V_Yyxhs0*2)> :NEW.GHXHS0 then -- Raise E_Yyxhs0; -- end if; update YS_HBHZXH set YYBZ00='1' where GHHBID=:new.GHHBID and YYBZ00='3'; --南平市一所有3的倍数为预约号 if v_YYHFS='1' then Update YS_HBHZXH set YYBZ00='3' where GHHBID=:new.GHHBID and MOD(HZXH00,3)=0 AND HZXH00<>0; SELECT COUNT(*) INTO V_COUNT0 FROM YS_HBHZXH WHERE GHHBID=:new.GHHBID AND YYBZ00='3'; IF V_COUNT0>0 THEN :NEW.YYXHS0:=V_COUNT0; :NEW.YYXHMX:=V_COUNT0; END IF; else--偶数为预约号 while V_Yyxhs0>0 loop Update YS_HBHZXH set YYBZ00='3' where GHHBID=:new.GHHBID and HZXH00=(:NEW.HZQSXH-1)+(V_Yyxhs0*2) ; V_Yyxhs0:= V_Yyxhs0-1; end loop; end if; --处理分诊预约数 if :NEW.FZYYS0>0 and substr(:new.FZYYMX,-1)=','then --v_FZYYMX:=:new.FZYYMX; v_FZYYMX:=substr(:new.FZYYMX,1,length(:new.FZYYMX)-1); /*while instr(v_FZYYMX,',')>0 loop ls_hzxh00:=to_number(substr(v_FZYYMX,1,instr(v_FZYYMX,',')-1)); update YS_HBHZXH SET YYBZ00='4' where GHHBID=:new.GHHBID and HZXH00=ls_hzxh00; v_FZYYMX:=substr(v_FZYYMX,instr(v_FZYYMX,',')+1,length(v_FZYYMX)-instr(v_FZYYMX,',')); end loop; */ SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''5'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_FZYYMX||')'); elsif :NEW.FZYYS0>0 and substr(:new.FZYYMX,-1)<>','then v_FZYYMX:=substr(:new.FZYYMX,1,length(:new.FZYYMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''5'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_FZYYMX||')'); end if; --处理电话预约数 if :NEW.DHYYS0>0 and substr(:new.DHYYMX,-1)=',' then v_DHYYMX:=substr(:new.DHYYMX,1,length(:new.DHYYMX)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''4'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_DHYYMX||')'); elsif :NEW.DHYYS0>0 and substr(:new.DHYYMX,-1) <>',' then v_DHYYMX:=substr(:new.DHYYMX,1,length(:new.DHYYMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''4'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_DHYYMX||')'); end if; --处理网络预约数 if :NEW.WLYYS0>0 and substr(:new.WLYYMX,-1)=',' then v_WLYYMX:=substr(:new.WLYYMX,1,length(:new.WLYYMX)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_WLYYMX||')'); elsif :NEW.WLYYS0>0 and substr(:new.WLYYMX,-1)<>',' then v_WLYYMX:=substr(:new.WLYYMX,1,length(:new.WLYYMX)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_WLYYMX||')'); end if; --处理网络预约数3,导入后处理 if :NEW.DHYY02>0 and substr(:new.DHMX02,-1)=',' then v_DHMX02:=substr(:new.DHMX02,1,length(:new.DHMX02)-1); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_DHMX02||')'); elsif :NEW.DHYY02>0 and substr(:new.DHMX02,-1)<>',' then v_DHMX02:=substr(:new.DHMX02,1,length(:new.DHMX02)); SP_EXECUTE_SQL('update YS_HBHZXH SET YYBZ00=''6'' where GHHBID='||:new.GHHBID||' and HZXH00 in ('||v_DHMX02||')'); end if; end if; elsif Cs_Ys_Xzygh0='M' then --广东中院 --写预约号 if inserting and :new.GHYGHS=0 and :new.YYXHS0>0 then SP_SF_YYXHCL(:new.GHHBID,:new.YYXHS0,:new.YYXHMX); elsif updating and :new.GHYGHS=0 and :new.YYXHS0<>:old.YYXHS0 then Update YS_HBHZXH set YYBZ00='0' where GHHBID=:new.GHHBID and YYBZ00='3'; SP_SF_YYXHCL(:new.GHHBID,:new.YYXHS0,:new.YYXHMX); end if; end if; if v_HZSJTS=0 then -- for MZHS-20140207-001 select nvl(min(HZXH00),-1) into v_HZXH00 from YS_HBHZXH where GHHBID=:NEW.GHHBID and HZQSSJ||':00'>:new.GHJSSJ and yxbz00='1'; if v_HZXH00>0 then update YS_HBHZXH set HZQSSJ='' where GHHBID=:NEW.GHHBID and HZXH00>=v_HZXH00 ; end if; end if ; exception when E_Ghxhs0 then raise_application_error(-20281, '修改挂号号表ID'||:NEW.GHHBID||'的挂号号表失败,原因:挂号限号数已经超过了已经挂的病人数！*'); rollback; when E_Yyxhs0 then raise_application_error(-20281, '修改或增加挂号号表ID'||:NEW.GHHBID||'的挂号号表失败,原因:挂号限号数至少应该是预约限号数的两倍！*'); rollback; When no_data_found then raise_application_error(-20010,'数据没有找到,'||errStr||'！*'); when others then raise_application_error(-20020, DBMS_UTILITY.format_error_backtrace||'@'||nvl(sqlerrm, '原因不明出错！*')); End; /