CREATE OR REPLACE PROCEDURE sp_sf_lockhy ( as_GHHBID in number, --排班ID as_GHMZSJ in char, --出诊日期 as_GHQSSJ in char, --出诊起始时间 as_GHJSSJ in char, --出诊结束时间 as_HZSJ00 in char, --候诊时段 ad_BRID00 in number, --病人ID as_BRXM00 in varchar2,--病人姓名 ad_GHYSBH in number, --出诊医生编号 ad_GHKSBH in number, --出诊科室编号 ad_LOCKID in out varchar2, --锁号ID as_SHCJBZ in char, --锁号厂家标识 广州集约=0 医院平台=1 as_SHCJMC in varchar2,--锁号厂家名称 as_FLAG00 in char, -- 0 锁号 1 解锁 2 取号 3 自动解锁 ad_CZY000 in number, --操作员编号 as_SFYX00 in char default 'Y',--是否有效 Y 有效 N 无效 as_SYSMSG out varchar2, as_USEMSG out varchar2 ) as -- MODIFICATION HISTORY -- Person Date Comments -- linzy 2015.03.12 号源锁定写入SF_YYHYZD by YYGH-20140508-001 ----- 病人年龄大于14岁不能挂儿科 ---- piw 20160601 更新ys_hbhzxh增加条件 YYBZ00='1' -- ------ ------------- --------------------------------------------------- lv_count0 number(5); ld_GHHBID SF_YSGHHB.GHHBID%TYPE; ld_ID0000 YS_HBHZXH.ID0000%TYPE; ls_HZSJ00 YS_HBHZXH.HZSJ00%TYPE; ls_LOCKID SF_YYHYZD.LOCKID%TYPE; ls_ghksmc bm_bmbm00.bmmc00%TYPE; ls_brcsrq bm_brxxb0.brcsrq%TYPE; begin if as_FLAG00='0' then select count(*) into lv_count0 from SF_YSGHHB where GHMZSJ=as_GHMZSJ and GHQSSJ=as_GHQSSJ and GHJSSJ=as_GHJSSJ and GHYSBH=ad_GHYSBH and GHKSBH=ad_GHKSBH and TZBZ00='0'; if lv_count0>1 then as_SYSMSG:=''; as_USEMSG:='找到多条的医生排班记录'; rollback; return; elsif lv_count0=0 then as_SYSMSG:=''; as_USEMSG:='未找到医生的排班记录或已停诊'; rollback; return; end if; select GHHBID into ld_GHHBID from SF_YSGHHB where GHMZSJ=as_GHMZSJ and GHQSSJ=as_GHQSSJ and GHJSSJ=as_GHJSSJ and GHYSBH=ad_GHYSBH and GHKSBH=ad_GHKSBH and TZBZ00='0'; select count(*) into lv_count0 from YS_HBHZXH where GHHBID=ld_GHHBID and HZSJ00=as_HZSJ00 and YXBZ00='1' and GHH000 is null and YYBZ00='1' and SFZJ00='0'; if lv_count0=0 then as_SYSMSG:='时段:'||as_HZSJ00; as_USEMSG:='该时段没有剩余号源'; rollback; return; end if; begin select ID0000 into ld_ID0000 from (select * from YS_HBHZXH where GHHBID=ld_GHHBID and HZSJ00=as_HZSJ00 and YXBZ00='1' and GHH000 is null and YYBZ00='1' and SFZJ00='0' order by ID0000) where rownum=1; exception when others then as_SYSMSG:='锁号失败'; as_USEMSG:='该医生已没有足够的号源！'; rollback; return; end; select count(1) into lv_count0 from SF_YYHYZD where GHHBID=ld_GHHBID and ID0000=ld_ID0000 and YYCJBZ=as_SHCJBZ and SFYX00='Y'; if lv_count0>0 then as_SYSMSG:='锁定流水号'||ad_LOCKID||'！'; as_USEMSG:='锁定流水号已存在！'; rollback; return; end if; ----- 病人年龄大于14岁不能挂儿科 select b.bmmc00 into ls_ghksmc from sf_ysghhb a,bm_bmbm00 b where a.ghksbh=b.bmbh00 and a.ghhbid=as_GHHBID; select a.brcsrq into ls_brcsrq from bm_brxxb0 a where brid00=ad_BRID00; if INSTR(ls_GHKSMC,'儿')>'0' and to_char(ADD_MONTHS(to_date(as_GHMZSJ,'yyyymmdd'),-168),'yyyymmdd')>ls_brcsrq then as_SYSMSG:='病人年龄大于14岁不能挂儿科号！'; as_USEMSG:='病人年龄大于14岁不能挂儿科号！'; rollback; return; end if; /* if INSTR(ls_GHKSMC,'儿')>'0' and to_number(trim(replace(SF_YF_CSRQTONL(ls_brcsrq,as_GHMZSJ),'岁','')))>'14' then as_SYSMSG:='病人年龄大于14岁不能挂儿科号！'||trim(replace(SF_YF_CSRQTONL(ls_brcsrq,as_GHMZSJ),'岁','')); as_USEMSG:='病人年龄大于14岁不能挂儿科号！'; rollback; return; end if; */ /* -- 原始的多个事务并发同时锁号出现重复现象 update YS_HBHZXH set GHH000='00000000',ZY0000='挂号锁号',YYBZ00='1',BRID00=ad_BRID00,BRXM00=as_BRXM00 where ID0000=ld_ID0000; */ ----解决多个事务并发同时锁号重复的问题 Modify by Fengcx 20150909 update YS_HBHZXH set GHH000='00000000',ZY0000='挂号锁号',YYBZ00='1',BRID00=ad_BRID00,BRXM00=as_BRXM00 where ID0000=ld_ID0000 and GHH000 is null and YYBZ00='1'; if sql%rowcount<=0 then as_SYSMSG:='预约锁定失败！'; as_USEMSG:='预约锁定失败！'; rollback; return; end if; update SF_YSGHHB set YYXHS0=YYXHS0+1,YYXHMX=SF_YY_YYXHMX(YYXHMX,GHQSSJ,as_HZSJ00,1,ld_GHHBID),YYSL00=nvl(YYSL00,0)+1 where GHHBID = ld_GHHBID; select 'his'||to_char(SQ_SF_YYHYZD_LOCKID.NEXTVAL) into ls_LOCKID from dual; insert into SF_YYHYZD(LOCKID,ID0000,GHHBID,YYCJBZ,YYCJMC, CZRQ00,CZSJ00,CZY000,SHZT00,BRID00) values(ls_LOCKID,ld_ID0000,ld_GHHBID,as_SHCJBZ,as_SHCJMC,to_char(sysdate,'YYYYMMDD'), to_char(sysdate,'HH24:MI:SS'),ad_CZY000,'0',ad_BRID00); ad_LOCKID := ls_LOCKID; elsif as_FLAG00='1' then begin select ID0000,GHHBID into ld_ID0000,ld_GHHBID from SF_YYHYZD where LOCKID=ad_LOCKID and YYCJBZ=as_SHCJBZ and WLYYID is null and SFYX00='Y'; --select ID0000,GHHBID into ld_ID0000,ld_GHHBID from YS_HBHZXH where YYID00=ad_LOCKID and ZY0000='预约锁号'; exception when others then as_SYSMSG:='锁定流水号:'||ad_LOCKID||'！'; as_USEMSG:='解锁发生错误，未找到锁定记录或该锁定流水号已预约成功！'; rollback; return; end; select HZSJ00 into ls_HZSJ00 from YS_HBHZXH where ID0000=ld_ID0000 and GHHBID=ld_GHHBID; update YS_HBHZXH set GHH000=null,ZY0000=null,BRID00=null,BRXM00=null where ID0000=ld_ID0000 and GHHBID=ld_GHHBID and YYBZ00='1'; update SF_YSGHHB set YYXHS0=YYXHS0-1,YYXHMX=SF_YY_YYXHMX(YYXHMX,GHQSSJ,ls_HZSJ00,-1,ld_GHHBID),YYSL00=nvl(YYSL00,0)-1 where GHHBID = ld_GHHBID; update SF_YYHYZD set JSRQ00=to_char(sysdate,'YYYYMMDD'),JSSJ00=to_char(sysdate,'HH24:MI:SS'), SFYX00='N',SHZT00=as_FLAG00,JSCZY0=ad_CZY000 where LOCKID=ad_LOCKID and YYCJBZ=as_SHCJBZ and SFYX00='Y' and nvl(SHZT00,'0')='0'; elsif as_FLAG00='2' then begin select ID0000,GHHBID into ld_ID0000,ld_GHHBID from SF_YYHYZD where LOCKID=ad_LOCKID and YYCJBZ=as_SHCJBZ and WLYYID is null and SFYX00='Y'; --select ID0000,GHHBID into ld_ID0000,ld_GHHBID from YS_HBHZXH where YYID00=ad_LOCKID and ZY0000='预约锁号'; exception when others then as_SYSMSG:='锁定流水号:'||ad_LOCKID||'！'; as_USEMSG:='解锁发生错误，未找到锁定记录或该锁定流水号已取号成功！'; rollback; return; end; select HZSJ00 into ls_HZSJ00 from YS_HBHZXH where ID0000=ld_ID0000 and GHHBID=ld_GHHBID; update YS_HBHZXH set GHH000=null,ZY0000=null,BRID00=null,BRXM00=null where ID0000=ld_ID0000 and GHHBID=ld_GHHBID and YYBZ00='1'; update SF_YSGHHB set YYXHS0=YYXHS0-1,YYXHMX=SF_YY_YYXHMX(YYXHMX,GHQSSJ,ls_HZSJ00,-1,ld_GHHBID),YYSL00=nvl(YYSL00,0)-1 where GHHBID = ld_GHHBID; update SF_YYHYZD set JSRQ00=to_char(sysdate,'YYYYMMDD'),JSSJ00=to_char(sysdate,'HH24:MI:SS'), SFYX00='N',SHZT00=as_FLAG00,JSCZY0=ad_CZY000 where LOCKID=ad_LOCKID and YYCJBZ=as_SHCJBZ and SFYX00='Y' and nvl(SHZT00,'0')='0'; end if; exception when others then as_SYSMSG:=SQLERRM; as_USEMSG:='锁号/解锁发生异常错误！'; rollback; end; /