CREATE PROCEDURE SP_SF_BRLXBRTJ ( as_KSRQ00 IN CHAR, --开始日期 as_KSSJ00 IN CHAR, --开始时间 as_JSRQ00 IN CHAR, --结束日期 as_JSSJ00 IN CHAR, --结束时间 ad_ID0000 OUT NUMBER, --统计报表对应的ID as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR --系统提示的错误信息 ) as ls_XMBH00 SF_FYMX00.XMBH00%TYPE; ls_HJJE00 SF_FYMX00.HJJE00%TYPE; ls_ZYID00 SF_BRXXB0.BRID00%TYPE; ls_BH0000 BM_FPXM00.BH0000%TYPE; CURSOR CUR_ZY_CYBRQFTJ IS select C.XMBH00,sum(C.HJJE00),A.BRID00 from SF_BRXXB0 A,SF_BRFY00 B,SF_FYMX00 C,bm_brfbb0 D where B.MZID00=A.GHID00 AND B.DJH000=C.DJH000 AND B.CZRQ00>=as_KSRQ00 AND B.CZRQ00<=as_JSRQ00 AND B.CZRQ00||B.CZSJ00>=as_KSRQ00||as_KSSJ00 AND B.CZRQ00||B.CZSJ00<=as_JSRQ00||as_JSSJ00 AND A.FBBH00=D.FBBH00 AND d.fbmc00 like '外市诊疗' group by C.XMBH00,A.BRID00 ; BEGIN select SQ_BM_YYSFTJ_ID0000.nextval into ad_ID0000 from dual; OPEN CUR_ZY_CYBRQFTJ ; LOOP FETCH CUR_ZY_CYBRQFTJ INTO ls_XMBH00,ls_HJJE00,ls_ZYID00; EXIT WHEN CUR_ZY_CYBRQFTJ%NOTFOUND; BEGIN select A.BH0000 into ls_BH0000 from BM_FPXM00 A,BM_YYSFXM B where A.FPXMID = B.ZYFPID and B.SFXMID=ls_XMBH00 and SYBZ00 <> '1'; EXCEPTION WHEN OTHERS THEN ls_BH0000:='13'; --当作其它费处理 END; if ls_BH0000 = '01' then Update BM_YYSFTJ Set SFXM01 = NVL(SFXM01,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM01,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '02' then Update BM_YYSFTJ Set SFXM02 = NVL(SFXM02,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM02,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '03' then Update BM_YYSFTJ Set SFXM03 = NVL(SFXM03,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM03,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '04' then Update BM_YYSFTJ Set SFXM04 = NVL(SFXM04,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM04,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '05' then Update BM_YYSFTJ Set SFXM05 = NVL(SFXM05,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM05,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '06' then Update BM_YYSFTJ Set SFXM06 = NVL(SFXM06,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM06,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '07' then Update BM_YYSFTJ Set SFXM07 = NVL(SFXM07,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM07,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '08' then Update BM_YYSFTJ Set SFXM08 = NVL(SFXM08,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM08,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '09' then Update BM_YYSFTJ Set SFXM09 = NVL(SFXM09,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM09,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '10' then Update BM_YYSFTJ Set SFXM10 = NVL(SFXM10,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM10,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '11' then Update BM_YYSFTJ Set SFXM11 = NVL(SFXM11,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM11,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '12' then Update BM_YYSFTJ Set SFXM12 = NVL(SFXM12,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM12,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '13' then Update BM_YYSFTJ Set SFXM13 = NVL(SFXM13,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM13,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '14' then Update BM_YYSFTJ Set SFXM14 = NVL(SFXM14,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM14,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '15' then Update BM_YYSFTJ Set SFXM15 = NVL(SFXM15,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM15,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '16' then Update BM_YYSFTJ Set SFXM16 = NVL(SFXM16,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM16,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '17' then Update BM_YYSFTJ Set SFXM17 = NVL(SFXM17,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM17,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '18' then Update BM_YYSFTJ Set SFXM18 = NVL(SFXM18,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM18,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '19' then Update BM_YYSFTJ Set SFXM19 = NVL(SFXM19,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM19,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; elsif ls_BH0000 = '20' then Update BM_YYSFTJ Set SFXM20 = NVL(SFXM20,0)+ls_HJJE00,XMHJ00=NVL(XMHJ00,0)+ls_HJJE00 where BMBH00 = ls_zyid00 and ID0000=ad_ID0000 ; if SQL%NOTFOUND then Insert into BM_YYSFTJ(BMBH00,SFXM20,ID0000,XMHJ00) values(ls_zyid00,ls_HJJE00,ad_ID0000,ls_HJJE00); end if; end if; END LOOP; CLOSE CUR_ZY_CYBRQFTJ; --添加一条总计行 Insert into BM_YYSFTJ(ID0000,BMBH00,SFXM01,SFXM02,SFXM03,SFXM04,SFXM05,SFXM06,SFXM07,SFXM08,SFXM09,SFXM10,SFXM11,SFXM12,SFXM13,SFXM14,SFXM15,SFXM16,SFXM17,SFXM18,SFXM19,SFXM20,SFXM80,XMHJ00) select ad_ID0000,999999999,sum(SFXM01),sum(SFXM02),sum(SFXM03),sum(SFXM04),sum(SFXM05),sum(SFXM06),sum(SFXM07),sum(SFXM08),sum(SFXM09),sum(SFXM10),sum(SFXM11),sum(SFXM12),sum(SFXM13), sum(SFXM14),sum(SFXM15),sum(SFXM16),sum(SFXM17),sum(SFXM18),sum(SFXM19),sum(SFXM20),SUM(SFXM80),sum(XMHJ00) from BM_YYSFTJ where ID0000 = ad_ID0000; commit; EXCEPTION WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_SF_BRLXBRTJ('||as_KSRQ00||','||as_JSRQ00||',ad_ID0000,as_YHMSG0,as_SYSMSG',1,150); ROLLBACK; END; /