create or replace procedure SP_BA_BCSYQKRYB( J_KSRQYM in char, -- 开始日期 J_JSRQYM in char -- 结束日期 ) as --病床使用情况任意报 vBZCW00 number(6); --编制床位 vSYCW00 number(6); --实有床位 cursor CUR_KS is select BMBH00,BMMC00,Xh0000 from ba_bmpx00 order by Xh0000; Row_KS CUR_KS%rowtype; dayCount number(10);--总计 begin delete from BA_YLZLZB; dayCount := TO_NUMBER((TO_DATE(J_JSRQYM,'yyyy-mm-dd')- TO_DATE(J_KSRQYM,'yyyy-mm-dd'))+1); open CUR_KS; loop fetch CUR_KS into Row_KS; exit when CUR_KS%notfound; ------------全院 ------------------- if(Row_KS.BMBH00=0) then insert into BA_YLZLZB(f1,f4,f5,f6,f7,f8,f9,f10,f11,f12) select Row_KS.BMMC00,sum(SJKFCR) SJKFCR, round(sum(SJKFCR)/ dayCount ,2) PJKFBC, sum(SJZYCR) SJZYCR,sum(CYZZYR) CYZZYR, case when sum(SYCW00)=0 then 0 else round(sum(SJZYCR)*100/sum(SYCW00),2) end SYL000, case when sum(CYRS00)=0 then '0' else to_char(round(sum(CYZZYR)/sum(CYRS00),2),'fm9999990.00') end PJZYR0, case when sum(SJKFCR)=0 then '0' else to_char(round((sum(CYRS00))*100/(sum(SJKFCR)*100/dayCount),2),'fm9999990.00') end BCZZCS, case when sum(SJKFCR)=0 then '0' else to_char(round(sum(SJZYCR)/(sum(SJKFCR)/dayCount),2),'fm9999990.00') end BCGZR0, sum(ZYZZYR) ZYZZYR from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM ; select sum(BZCW00),sum(SYCW00) into vBZCW00,vSYCW00 from ZS_ZYRSJ0 ZS where rq0000=J_KSRQYM ; update BA_YLZLZB set f2=vBZCW00,f3=vSYCW00 where f1=Row_KS.BMMC00; -----------住院内科---------------------- elsif(Row_KS.BMBH00=18) then insert into BA_YLZLZB(f1,f4,f5,f6,f7,f8,f9,f10,f11,f12) select Row_KS.BMMC00,sum(SJKFCR) SJKFCR, round(sum(SJKFCR)/ dayCount ,2) PJKFBC, sum(SJZYCR) SJZYCR,sum(CYZZYR) CYZZYR, case when sum(SYCW00)=0 then 0 else round(sum(SJZYCR)*100/sum(SYCW00),2) end SYL000, case when sum(CYRS00)=0 then '0' else to_char(round(sum(CYZZYR)/sum(CYRS00),2),'fm9999990.00') end PJZYR0, case when sum(SJKFCR)=0 then '0' else to_char(round((sum(CYRS00))*100/(sum(SJKFCR)*100/dayCount),2),'fm9999990.00') end BCZZCS, case when sum(SJKFCR)=0 then '0' else to_char(round(sum(SJZYCR)/(sum(SJKFCR)/dayCount),2),'fm9999990.00') end BCGZR0, sum(ZYZZYR) ZYZZYR from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and bmbh00 in ('4110001','4120001','42000','4210001','4140001','4270001','53'); select sum(BZCW00),sum(SYCW00) into vBZCW00,vSYCW00 from ZS_ZYRSJ0 ZS where rq0000=J_KSRQYM and bmbh00 in ('4110001','4120001','42000','4210001','4140001','4270001','53'); update BA_YLZLZB set f2=vBZCW00,f3=vSYCW00 where f1=Row_KS.BMMC00; ---------住院外科---------------------------------------- elsif(Row_KS.BMBH00=25) then insert into BA_YLZLZB(f1,f4,f5,f6,f7,f8,f9,f10,f11,f12) select Row_KS.BMMC00,sum(SJKFCR) SJKFCR, round(sum(SJKFCR)/ dayCount ,2) PJKFBC, sum(SJZYCR) SJZYCR,sum(CYZZYR) CYZZYR, case when sum(SYCW00)=0 then 0 else round(sum(SJZYCR)*100/sum(SYCW00),2) end SYL000, case when sum(CYRS00)=0 then '0' else to_char(round(sum(CYZZYR)/sum(CYRS00),2),'fm9999990.00') end PJZYR0, case when sum(SJKFCR)=0 then '0' else to_char(round((sum(CYRS00))*100/(sum(SJKFCR)*100/dayCount),2),'fm9999990.00') end BCZZCS, case when sum(SJKFCR)=0 then '0' else to_char(round(sum(SJZYCR)/(sum(SJKFCR)/dayCount),2),'fm9999990.00') end BCGZR0, sum(ZYZZYR) ZYZZYR from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00 in ('4010001','4020001','28','4040001','41800','4030001','4030003','4040101'); select sum(BZCW00),sum(SYCW00) into vBZCW00,vSYCW00 from ZS_ZYRSJ0 ZS where rq0000=J_KSRQYM and BMBH00 in ('4010001','4020001','28','4040001','41800','4030001','4030003','4040101'); update BA_YLZLZB set f2=vBZCW00,f3=vSYCW00 where f1=Row_KS.BMMC00; --------------住院小儿科 elsif(Row_KS.BMBH00=34) then insert into BA_YLZLZB(f1,f4,f5,f6,f7,f8,f9,f10,f11,f12) select Row_KS.BMMC00,sum(SJKFCR) SJKFCR, round(sum(SJKFCR)/ dayCount ,2) PJKFBC, sum(SJZYCR) SJZYCR,sum(CYZZYR) CYZZYR, case when sum(SYCW00)=0 then 0 else round(sum(SJZYCR)*100/sum(SYCW00),2) end SYL000, case when sum(CYRS00)=0 then '0' else to_char(round(sum(CYZZYR)/sum(CYRS00),2),'fm9999990.00') end PJZYR0, case when sum(SJKFCR)=0 then '0' else to_char(round((sum(CYRS00))*100/(sum(SJKFCR)*100/dayCount),2),'fm9999990.00') end BCZZCS, case when sum(SJKFCR)=0 then '0' else to_char(round(sum(SJZYCR)/(sum(SJKFCR)/dayCount),2),'fm9999990.00') end BCGZR0, sum(ZYZZYR) ZYZZYR from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00 in ('4150001','4150002','4150003'); select sum(BZCW00),sum(SYCW00) into vBZCW00,vSYCW00 from ZS_ZYRSJ0 ZS where rq0000=J_KSRQYM and BMBH00 in ('4150001','4150002','4150003'); update BA_YLZLZB set f2=vBZCW00,f3=vSYCW00 where f1=Row_KS.BMMC00; ------妇产科------------------------ elsif(Row_KS.BMBH00=31) then insert into BA_YLZLZB(f1,f4,f5,f6,f7,f8,f9,f10,f11,f12) select Row_KS.BMMC00,sum(SJKFCR) SJKFCR, round(sum(SJKFCR)/ dayCount ,2) PJKFBC, sum(SJZYCR) SJZYCR,sum(CYZZYR) CYZZYR, case when sum(SYCW00)=0 then 0 else round(sum(SJZYCR)*100/sum(SYCW00),2) end SYL000, case when sum(CYRS00)=0 then '0' else to_char(round(sum(CYZZYR)/sum(CYRS00),2),'fm9999990.00') end PJZYR0, case when sum(SJKFCR)=0 then '0' else to_char(round((sum(CYRS00))*100/(sum(SJKFCR)*100/dayCount),2),'fm9999990.00') end BCZZCS, case when sum(SJKFCR)=0 then '0' else to_char(round(sum(SJZYCR)/(sum(SJKFCR)/dayCount),2),'fm9999990.00') end BCGZR0, sum(ZYZZYR) ZYZZYR from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00 in ('4050002','4050001'); select sum(BZCW00),sum(SYCW00) into vBZCW00,vSYCW00 from ZS_ZYRSJ0 ZS where rq0000=J_KSRQYM and BMBH00 in ('4050002','4050001'); update BA_YLZLZB set f2=vBZCW00,f3=vSYCW00 where f1=Row_KS.BMMC00; --------住院中医综合科------------ elsif(Row_KS.BMBH00=38) then insert into BA_YLZLZB(f1,f4,f5,f6,f7,f8,f9,f10,f11,f12) select Row_KS.BMMC00,sum(SJKFCR) SJKFCR, round(sum(SJKFCR)/ dayCount ,2) PJKFBC, sum(SJZYCR) SJZYCR,sum(CYZZYR) CYZZYR, case when sum(SYCW00)=0 then 0 else round(sum(SJZYCR)*100/sum(SYCW00),2) end SYL000, case when sum(CYRS00)=0 then '0' else to_char(round(sum(CYZZYR)/sum(CYRS00),2),'fm9999990.00') end PJZYR0, case when sum(SJKFCR)=0 then '0' else to_char(round((sum(CYRS00))*100/(sum(SJKFCR)*100/dayCount),2),'fm9999990.00') end BCZZCS, case when sum(SJKFCR)=0 then '0' else to_char(round(sum(SJZYCR)/(sum(SJKFCR)/dayCount),2),'fm9999990.00') end BCGZR0, sum(ZYZZYR) ZYZZYR from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00 in ('4160001','21','4170001'); select sum(BZCW00),sum(SYCW00) into vBZCW00,vSYCW00 from ZS_ZYRSJ0 ZS where rq0000=J_KSRQYM and BMBH00 in ('4160001','21','4170001'); update BA_YLZLZB set f2=vBZCW00,f3=vSYCW00 where f1=Row_KS.BMMC00; --------------- else insert into BA_YLZLZB(f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12) select ' '||Row_KS.BMMC00,round(avg(BZCW00),2) BZCW00,round(avg(SYCW00),2) SYCW00,sum(SJKFCR) SJKFCR, round(sum(SJKFCR)/ dayCount ,2) PJKFBC, sum(SJZYCR) SJZYCR,nvl(sum(CYZZYR),0) CYZZYR, case when sum(SYCW00)=0 then 0 else round(sum(SJZYCR)*100/sum(SYCW00),2) end SYL000, case when sum(CYRS00)=0 then '0' else to_char(round(sum(CYZZYR)/sum(CYRS00),2),'fm9999990.00') end PJZYR0, case when sum(SJKFCR)=0 then '0' else to_char(round((sum(CYRS00))*100/(sum(SJKFCR)*100/dayCount),2),'fm9999990.00') end BCZZCS, case when sum(SJKFCR)=0 then '0' else to_char(round(sum(SJZYCR)/(sum(SJKFCR)/dayCount),2),'fm9999990.00') end BCGZR0, sum(ZYZZYR) ZYZZYR from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00=Row_KS.BMBH00; end if; end loop; close CUR_KS; commit; end; -------------------------------------------------------------------------------------------------------------------- create or replace procedure SP_BA_ZYZRYB( J_KSRQYM in char, -- 开始日期 J_JSRQYM in char -- 结束日期 ) as vQSRQ00 varchar2(8); --起始日期 vZZRQ00 varchar2(8); --终止日期 vYYRS00 number(11); --原有人数 vSYRS00 number(11); --实有人数 vSYCW00 number(11); --实有病床数 vKSMC00 varchar2(98); --科室名称 --住院者任意报 cursor CUR_KS is select BMBH00,BMMC00,Xh0000 from ba_bmpx00 where LB0000=1 order by Xh0000; Row_KS CUR_KS%rowtype; -- MODifICATION HISTORY -- Person Date Comments -- hul 2022.01.27 create 重新整理脚本归档 begin delete from BA_YLZLZB; open CUR_KS; loop fetch CUR_KS into Row_KS; exit when CUR_KS%notfound; -------------------------------- --住院内科 if(Row_KS.BMBH00=18) then vKSMC00 :=Row_KS.BMMC00; insert into BA_YLZLZB(f1,f3,f4,f5,f6,f7,f8,f9,f10,f11, f13,f14,f15,f16,f17,f18,f19, f22,f23,f24,f25,f26,f27,f28,f29,f30,f31,f32,f33,f34 ,f35,f36,f37,f38,f39,f40,f41,f42) select vKSMC00 BMMC00, sum(RYRS00) RYRS00,sum(TKZR00) TKZR00,sum(CYRS00) CYRS00, sum(ZYRS00) ZYRS00,sum(HZRS00) HZRS00,sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, nvl(sum(WZRS00),0) WZRS00,nvl(sum(QJCS00),0) QJCS00,nvl(sum(CGCS00),0) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) , sum(CYSQXC), sum(CYSQJX), sum(CYSQQT), sum(CYBS00), sum(CYWS00), sum(CYWG00) , sum(CYBSQ0), decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00 in ('4110001','4120001','42000','4210001','4140001','4270001','53'); select min(RQ0000) into vQSRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4110001','4120001','42000','4210001','4140001','4270001','53'); select max(RQ0000) into vZZRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4110001','4120001','42000','4210001','4140001','4270001','53'); if vQSRQ00 is not null then select sum(YYRS00) into vYYRS00 from ZS_ZYRSJ0 where rq0000=vQSRQ00 and BMBH00 in ('4110001','4120001','42000','4210001','4140001','4270001','53'); update BA_YLZLZB set f2=vYYRS00 where f1=vKSMC00; end if; if vZZRQ00 is not null then select sum(SYRS00) into vSYRS00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4110001','4120001','42000','4210001','4140001','4270001','53'); update BA_YLZLZB set f12=vSYRS00 where f1=vKSMC00; select sum(SYCW00)+nvl(sum(GXBC00),0) into vSYCW00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4110001','4120001','42000','4210001','4140001','4270001','53'); update BA_YLZLZB set f20=vSYCW00 where f1=vKSMC00; end if; --------------------------------- --住院外科 elsif(Row_KS.BMBH00=25) then vKSMC00 :=Row_KS.BMMC00; insert into BA_YLZLZB(f1,f3,f4,f5,f6, f7,f8,f9,f10,f11, f13,f14,f15,f16,f17, f18,f19,f22,f23,f24,f25,f26,f27,f28,f29,f30,f31,f32,f33,f34 ,f35,f36,f37,f38,f39,f40,f41,f42) select vKSMC00 BMMC00, sum(RYRS00) RYRS00,sum(TKZR00) TKZR00,sum(CYRS00) CYRS00, sum(ZYRS00) ZYRS00,sum(HZRS00) HZRS00,sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, nvl(sum(WZRS00),0) WZRS00,nvl(sum(QJCS00),0) QJCS00,nvl(sum(CGCS00),0) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) , sum(CYSQXC), sum(CYSQJX), sum(CYSQQT), sum(CYBS00), sum(CYWS00), sum(CYWG00) , sum(CYBSQ0), decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00 in ('4010001','4020001','28','4040001','41800','4030001','4030003','4040101'); select min(RQ0000) into vQSRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4010001','4020001','28','4040001','41800','4030001','4030003','4040101'); select max(RQ0000) into vZZRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4010001','4020001','28','4040001','41800','4030001','4030003','4040101'); if vQSRQ00 is not null then select sum(YYRS00) into vYYRS00 from ZS_ZYRSJ0 where rq0000=vQSRQ00 and BMBH00 in ('4010001','4020001','28','4040001','41800','4030001','4030003','4040101'); update BA_YLZLZB set f2=vYYRS00 where f1=vKSMC00; end if; if vZZRQ00 is not null then select sum(SYRS00) into vSYRS00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4010001','4020001','28','4040001','41800','4030001','4030003','4040101'); update BA_YLZLZB set f12=vSYRS00 where f1=vKSMC00; select sum(SYCW00)+nvl(sum(GXBC00),0) into vSYCW00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4010001','4020001','28','4040001','41800','4030001','4030003','4040101'); update BA_YLZLZB set f20=vSYCW00 where f1=vKSMC00; end if; --------------------------------- ---------------住院妇产科----------------- elsif(Row_KS.BMBH00=31) then vKSMC00 :=Row_KS.BMMC00; insert into BA_YLZLZB(f1,f3,f4,f5,f6, f7,f8,f9,f10,f11, f13,f14,f15,f16,f17, f18,f19,f22,f23,f24,f25,f26,f27,f28,f29,f30,f31,f32,f33,f34 ,f35,f36,f37,f38,f39,f40,f41,f42) select vKSMC00 BMMC00, sum(RYRS00) RYRS00,sum(TKZR00) TKZR00,sum(CYRS00) CYRS00, sum(ZYRS00) ZYRS00,sum(HZRS00) HZRS00,sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, nvl(sum(WZRS00),0) WZRS00,nvl(sum(QJCS00),0) QJCS00,nvl(sum(CGCS00),0) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) , sum(CYSQXC), sum(CYSQJX), sum(CYSQQT), sum(CYBS00), sum(CYWS00), sum(CYWG00) , sum(CYBSQ0), decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00 in ('4050002','4050001'); select min(RQ0000) into vQSRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4050002','4050001'); select max(RQ0000) into vZZRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4050002','4050001'); if vQSRQ00 is not null then select sum(YYRS00) into vYYRS00 from ZS_ZYRSJ0 where rq0000=vQSRQ00 and BMBH00 in ('4050002','4050001'); update BA_YLZLZB set f2=vYYRS00 where f1=vKSMC00; end if; if vZZRQ00 is not null then select sum(SYRS00) into vSYRS00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4050002','4050001'); update BA_YLZLZB set f12=vSYRS00 where f1=vKSMC00; select sum(SYCW00)+nvl(sum(GXBC00),0) into vSYCW00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4050002','4050001'); update BA_YLZLZB set f20=vSYCW00 where f1=vKSMC00; end if; ---------------住院小儿科----------------------------- elsif(Row_KS.BMBH00=34) then vKSMC00 :=Row_KS.BMMC00; insert into BA_YLZLZB(f1,f3,f4,f5,f6, f7,f8,f9,f10,f11, f13,f14,f15,f16,f17, f18,f19,f22,f23,f24,f25,f26,f27,f28,f29,f30,f31,f32,f33,f34 ,f35,f36,f37,f38,f39,f40,f41,f42) select vKSMC00 BMMC00, sum(RYRS00) RYRS00,sum(TKZR00) TKZR00,sum(CYRS00) CYRS00, sum(ZYRS00) ZYRS00,sum(HZRS00) HZRS00,sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, nvl(sum(WZRS00),0) WZRS00,nvl(sum(QJCS00),0) QJCS00,nvl(sum(CGCS00),0) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) , sum(CYSQXC), sum(CYSQJX), sum(CYSQQT), sum(CYBS00), sum(CYWS00), sum(CYWG00) , sum(CYBSQ0), decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00 in ('4150001','4150002','4150003'); select min(RQ0000) into vQSRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4150001','4150002','4150003'); select max(RQ0000) into vZZRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4150001','4150002','4150003'); if vQSRQ00 is not null then select sum(YYRS00) into vYYRS00 from ZS_ZYRSJ0 where rq0000=vQSRQ00 and BMBH00 in ('4150001','4150002','4150003'); update BA_YLZLZB set f2=vYYRS00 where f1=vKSMC00; end if; if vZZRQ00 is not null then select sum(SYRS00) into vSYRS00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4150001','4150002','4150003'); update BA_YLZLZB set f12=vSYRS00 where f1=vKSMC00; select sum(SYCW00)+nvl(sum(GXBC00),0) into vSYCW00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4150001','4150002','4150003'); update BA_YLZLZB set f20=vSYCW00 where f1=vKSMC00; end if; --------住院中医综合科------ elsif(Row_KS.BMBH00=38) then vKSMC00 :=Row_KS.BMMC00; insert into BA_YLZLZB(f1,f3,f4,f5,f6, f7,f8,f9,f10,f11, f13,f14,f15,f16,f17, f18,f19,f22,f23,f24,f25,f26,f27,f28,f29,f30,f31,f32,f33,f34 ,f35,f36,f37,f38,f39,f40,f41,f42) select vKSMC00 BMMC00, sum(RYRS00) RYRS00,sum(TKZR00) TKZR00,sum(CYRS00) CYRS00, sum(ZYRS00) ZYRS00,sum(HZRS00) HZRS00,sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, nvl(sum(WZRS00),0) WZRS00,nvl(sum(QJCS00),0) QJCS00,nvl(sum(CGCS00),0) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) , sum(CYSQXC), sum(CYSQJX), sum(CYSQQT), sum(CYBS00), sum(CYWS00), sum(CYWG00) , sum(CYBSQ0), decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00 in ('4160001','21','4170001'); select min(RQ0000) into vQSRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4160001','21','4170001'); select max(RQ0000) into vZZRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00 in ('4160001','21','4170001'); if vQSRQ00 is not null then select sum(YYRS00) into vYYRS00 from ZS_ZYRSJ0 where rq0000=vQSRQ00 and BMBH00 in ('4160001','21','4170001'); update BA_YLZLZB set f2=vYYRS00 where f1=vKSMC00; end if; if vZZRQ00 is not null then select sum(SYRS00) into vSYRS00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4160001','21','4170001'); update BA_YLZLZB set f12=vSYRS00 where f1=vKSMC00; select sum(SYCW00)+nvl(sum(GXBC00),0) into vSYCW00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00 in ('4160001','21','4170001'); update BA_YLZLZB set f20=vSYCW00 where f1=vKSMC00; end if; --------------------------全院------- elsif(Row_KS.BMBH00=0) then insert into BA_YLZLZB(f1,f3,f4,f5,f6,f7,f8,f9,f10,f11,f13,f14,f15,f16,f17, f18,f19,f22,f23,f24,f25,f26,f27,f28,f29,f30,f31,f32,f33,f34 ,f35,f36,f37,f38,f39,f40,f41,f42) select '全院' BMMC00,sum(RYRS00) RYRS00,sum(TKZR00) TKZR00, sum(CYRS00) CYRS00,sum(ZYRS00) ZYRS00,sum(HZRS00) HZRS00, sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, nvl(sum(WZRS00),0) WZRS00,nvl(sum(QJCS00),0) QJCS00,nvl(sum(CGCS00),0) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) , sum(CYSQXC), sum(CYSQJX), sum(CYSQQT), sum(CYBS00), sum(CYWS00), sum(CYWG00) , sum(CYBSQ0), decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM; select min(RQ0000) into vQSRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM; select max(RQ0000) into vZZRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM; if vQSRQ00 is not null then select sum(YYRS00) into vYYRS00 from ZS_ZYRSJ0 where rq0000=vQSRQ00; update BA_YLZLZB set f2=vYYRS00 where f1='全院'; end if; if vZZRQ00 is not null then select sum(SYRS00) into vSYRS00 from ZS_ZYRSJ0 where rq0000=vZZRQ00; update BA_YLZLZB set f12=vSYRS00 where f1='全院'; select sum(SYCW00)+nvl(sum(GXBC00),0) into vSYCW00 from ZS_ZYRSJ0 where rq0000=vZZRQ00; update BA_YLZLZB set f20=vSYCW00 where f1='全院'; end if; -------------------------------------------- else vKSMC00 :=' '||Row_KS.BMMC00; if(Row_KS.BMBH00 ='4030001' or Row_KS.BMBH00 ='4030003' or Row_KS.BMBH00 ='93' or Row_KS.BMBH00 ='466' or Row_KS.BMBH00 ='730') then vKSMC00 :=' '||Row_KS.BMMC00; end if; insert into BA_YLZLZB(f1,f3,f4,f5,f6, f7,f8,f9,f10,f11, f13,f14,f15,f16,f17,f18,f19,f22,f23,f24,f25,f26,f27,f28,f29,f30,f31,f32,f33,f34 ,f35,f36,f37,f38,f39,f40,f41,f42) select vKSMC00 BMMC00,sum(RYRS00) RYRS00,sum(TKZR00) TKZR00,sum(CYRS00) CYRS00,sum(ZYRS00) ZYRS00, sum(HZRS00) HZRS00,sum(WYRS00) WYRS00,sum(SWRS00) SWRS00,sum(QTRS00) QTRS00,sum(ZWTK00) ZWTK00, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(ZYRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end ZYL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(HZRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end HZL000, case when nvl((sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),0)=0 then '0' else to_char(round(sum(SWRS00)*100/(sum(ZYRS00)+sum(HZRS00)+sum(WYRS00)+sum(SWRS00)),2),'fm9999990.00') end SWL000, nvl(sum(WZRS00),0) WZRS00,nvl(sum(QJCS00),0) QJCS00,nvl(sum(CGCS00),0) CGCS00, case when nvl(sum(QJCS00),0)=0 then '0' else to_char(round(sum(CGCS00)*100/sum(QJCS00),2),'fm9999990.00') end CGL000 , sum(SW2400) , nvl(sum(QTRS00),0)+nvl(sum(ZCFMRS),0)+nvl(sum(JHSYRS),0) , sum(ZCFMRS) , sum(JHSYRS) , 0 , sum(PHRRS0) , decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) , sum(ZLHJ00) , sum(EXZL00) , sum(YWA000) , sum(LXZL00) , sum(JJEXZL) , sum(WZZL00) , sum(CYSQXC), sum(CYSQJX), sum(CYSQQT), sum(CYBS00), sum(CYWS00), sum(CYWG00) , sum(CYBSQ0), decode(nvl(sum(SYRS00),0),0,0,to_char(round(sum(PHRRS0)*100/sum(SYRS00),2),'fm9999990.00')) from ZS_ZYRSJ0 ZS where rq0000>=J_KSRQYM and rq0000<=J_JSRQYM and BMBH00=Row_KS.BMBH00; select min(RQ0000) into vQSRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00=Row_KS.BMBH00; select max(RQ0000) into vZZRQ00 from ZS_ZYRSJ0 where RQ0000>=J_KSRQYM and RQ0000<=J_JSRQYM and BMBH00=Row_KS.BMBH00; if vQSRQ00 is not null then select YYRS00 into vYYRS00 from ZS_ZYRSJ0 where rq0000=vQSRQ00 and BMBH00=Row_KS.BMBH00; update BA_YLZLZB set f2=vYYRS00 where f1=vKSMC00; end if; if vZZRQ00 is not null then select SYRS00 into vSYRS00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00=Row_KS.BMBH00; update BA_YLZLZB set f12=vSYRS00 where f1=vKSMC00; select SYCW00 into vSYCW00 from ZS_ZYRSJ0 where rq0000=vZZRQ00 and BMBH00=Row_KS.BMBH00; update BA_YLZLZB set f20=vSYCW00 where f1=vKSMC00; end if; end if; end loop; close CUR_KS; commit; end;