--临时先改了给用户用，用户提交需求： --以下需求：第1、3点，已经改了过程，先给用户用了。第2、4两点，涉及表结构的更改，未做。 /*XMGL-20170622-001 医保项目在导入时如果是修改其中的数据的时候无法更新，请帮忙修改成导入旧数据时能更新其中的数据 ------------------------- sp_xm_ybdr_xls需要修改： 1、变更的要同时修改 bm_ybsfxm select YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00,ypgg00 from bm_ybsfxm --update YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00,ypgg00 2、备份表要把医保最高限价等字段同时备份 select * from BM_YBXMBF --add YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00 3、对应表要同时修改价格等 select * from bm_ybsfdy --update YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1 4、备份对应表要把医保最高限价等字段同时备份 select * from bm_ybdybf --add YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1 */ -------------------------- --5、自付比例按公式计算：round((ybzgdj-ybjsdj)/ybzgdj,2),--自付比例按公式计算 -------------------------- create or replace PROCEDURE SP_XM_YBDR_XLS ( ad_FBBH00 IN NUMBER, --费别编号 ad_YBZXLB IN CHAR, --医保中心类别 ad_MLBH00 IN CHAR, --目录包含类型 '1':全部目录,'2'只包含药品,'3'只包含收费项目 --没用到 ad_GXLX00 IN CHAR, --更新类型 '1':完整更新,'2'部分更新 ad_CZY000 IN NUMBER, --操作员编码 ad_GXHJLS OUT NUMBER, --更新之后记录数 as_YHMSG0 OUT VARCHAR, --存储过程提示的错误信息 as_SYSMSG OUT VARCHAR --系统提示的错误信息 ) as ls_COUNT0 NUMBER(5); ls_CZRQ00 CHAR(8); ls_CZSJ00 CHAR(8); cursor CUR_YBFPXM is select distinct ybfpxm from bm_ybypml_temp09 a where not exists (select 1 from bm_ybfpxm b where b.xmmc00=a.ybfpxm and YBZXLB=ad_YBZXLB); -- MODIFICATION HISTORY -- Person Date Comments -- qks 2010.08.30 Create -- qks 2010.09.21 解决update bm_ybsfdy表速度慢问题,ad_MLBH00入参不再使用 -- zhangwz 2010.10.15 解决导入excel时如果是已经存在的医保编码时,没有导入zfbl00,YBLB00,SFYBXM,BZ0000相关信息. -- zhangwz 2011.01.29 zfbl00的decode函数增加以下几种自付比例:非医保,甲类,乙类.by XMGL-20110129-001 -- wusq 2016.03.09 解决了修改excel中的生产厂家名称时，没有导入SCCJ00相关信息 by XMGL-20160302-001 -- dsm 2017.05.04 增加YBJSDJ,YBZGDJ for XMGL-20170502-001 -- dsm 2017.05.11 增加YBJSDJ1,YBZGDJ1 for XMGL-20170510-001 BEGIN ls_CZRQ00 := to_char(sysdate,'YYYYMMDD'); ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS'); select count(1) into ls_COUNT0 from bm_ybypml_temp09 where rownum=1; if ls_COUNT0=0 then as_YHMSG0:='中间临时表bm_ybypml_temp09数据为空!'; as_SYSMSG:=SQLERRM; rollback; return; end if; for C_YBFPXM in CUR_YBFPXM loop as_YHMSG0:=as_YHMSG0||C_YBFPXM.ybfpxm||','; end loop; if as_YHMSG0 is not null then as_YHMSG0:='以下医保发票项目不存在于表bm_ybfpxm中:'||as_YHMSG0; as_SYSMSG:='请直接修改字段bm_ybypml_temp09.YBFPXM或者重新修改Excel文件后再导入！'; rollback; return; end if; --1、从临时表bm_ybypml_temp09导入到bm_ybypml_temp07 SP_EXECUTE_SQL('truncate table bm_ybypml_temp07'); --处理导入的excel编码与现有库的差集 SP_EXECUTE_SQL('truncate table bm_ybypml_temp10'); --处理导入的excel编码与现有库的交集 update bm_ybypml_temp09 set XMBH00=Trim(XMBH00); insert into bm_ybypml_temp07 --差集 (XMBH00,YPTYM0,YPMC00,YPJX00,YPJB00,YPJJDW,YPGG00,YPSCCJ, SFYB00, ZFBL00, YBLB00, YBFPXM, SFOTC0,BZ0000,CKJG00,GXY000,TNB000,MXXS00,SFSX00,GXRQ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00 ) select a.XMBH00,YPTYM0,YPMC00,YPJX00,YPJB00,YPJJDW,YPGG00,YPSCCJ, decode(SFYB00,'是','Y','N'), /*decode(sfyb00,'否',1,decode(zfbl00,'无',decode(yblb00,'非医保',1,0),'非医保',1,'甲类',0,'乙类',0, '自付100%',1,'无自付',0,'自付5%',0.05,'自付10%',0.1,'自付15%',0.15,'自付20%',0.2,'自付25%',0.25, '自付30%',0.3,'自付35%',0.35,'自付40%',0.4,'自付60%',0.6)),*/ round((ybzgdj-ybjsdj)/ybzgdj,2),--自付比例按公式计算 decode(sfyb00,'否',7,decode(yblb00,'非医保',7,'甲类',4,'乙类',5)), B.BH0000, SFOTC0,BZ0000,CKJG00,GXY000,TNB000,MXXS00,SFSX00,GXRQ00,a.YBJSDJ,a.YBZGDJ,a.YBJSDJ1,a.YBZGDJ1,a.XMDW01,a.CFBL00 from bm_ybypml_temp09 a,bm_ybfpxm b where b.xmmc00=a.ybfpxm and b.YBZXLB=ad_YBZXLB and b.FBBH00=ad_FBBH00 and a.xmbh00 in ( select xmbh00 from bm_ybypml_temp09 minus select trim(xmbh00) from bm_ybsfxm where YBZXLB=ad_YBZXLB and FBBH00=ad_FBBH00 ); insert into bm_ybypml_temp10 --交集 (XMBH00,YPTYM0,YPMC00,YPJX00,YPJB00,YPJJDW,YPGG00,YPSCCJ, SFYB00, ZFBL00, YBLB00, YBFPXM, SFOTC0,BZ0000,CKJG00,GXY000,TNB000,MXXS00,SFSX00,GXRQ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00 ) select a.XMBH00,YPTYM0,YPMC00,YPJX00,YPJB00,YPJJDW,YPGG00,YPSCCJ, decode(SFYB00,'是','Y','N'), /*decode(sfyb00,'否',1,decode(zfbl00,'无',decode(yblb00,'非医保',1,0),'非医保',1,'甲类',0,'乙类',0, '自付100%',1,'无自付',0,'自付5%',0.05,'自付10%',0.1,'自付15%',0.15,'自付20%',0.2,'自付25%',0.25, '自付30%',0.3,'自付35%',0.35,'自付40%',0.4,'自付60%',0.6)),*/ round((ybzgdj-ybjsdj)/ybzgdj,2),--自付比例按公式计算 decode(sfyb00,'否',7,decode(yblb00,'非医保',7,'甲类',4,'乙类',5)), B.BH0000, SFOTC0,BZ0000,CKJG00,GXY000,TNB000,MXXS00,SFSX00,GXRQ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00 from bm_ybypml_temp09 a,bm_ybfpxm b where b.xmmc00=a.ybfpxm and b.YBZXLB=ad_YBZXLB and b.FBBH00=ad_FBBH00 and a.xmbh00 in ( select xmbh00 from bm_ybypml_temp09 intersect select trim(xmbh00) from bm_ybsfxm where YBZXLB=ad_YBZXLB and FBBH00=ad_FBBH00 ); commit; --2、备份表bm_ybsfxm数据 insert into BM_YBXMBF ( YBZXLB,XMBH00,FLAG00,ZYFPXM,MZFPXM,XMMC00,YPJX00,XMDW00,ZFBL00,SFYX00, SFYBXM,YBLB00,PYSM00,WBSM00,GXXLH0,YBBRLB,SFSX00,SCCJ00,YPGG00,BZ0000, FBBH00,CZY000,CZRQ00,CZSJ00) select YBZXLB ,XMBH00 ,FLAG00 ,ZYFPXM ,MZFPXM ,XMMC00 ,YPJX00 ,XMDW00 ,ZFBL00 ,SFYX00 , SFYBXM ,YBLB00 ,PYSM00 ,WBSM00 ,GXXLH0 ,YBBRLB ,SFSX00 ,SCCJ00 ,YPGG00 ,BZ0000 , FBBH00 ,ad_CZY000 ,ls_CZRQ00 ,ls_CZSJ00 from bm_ybsfxm a where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00; commit; --3、临时表bm_ybypml_temp09导入到BM_YBSFXM insert into bm_ybsfxm ( YBZXLB,XMBH00,FLAG00,ZYFPXM, MZFPXM,XMMC00,YPJX00,XMDW00, ZFBL00,SFYX00,SFYBXM,YBLB00, PYSM00,WBSM00,GXXLH0,YBBRLB, SFSX00,YPGG00,BZ0000,SCCJ00,fbbh00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00 ) select ad_YBZXLB,XMBH00,'1',YBFPXM, YBFPXM,nvl(YPTYM0,YPMC00),YPJX00,YPJJDW, nvl(ZFBL00,1),'Y',SFYB00,YBLB00, substrb(SF_YJ_GETPYSM(nvl(YPTYM0,YPMC00)),1,10),substrb(SF_YJ_GETWBSM(nvl(YPTYM0,YPMC00)),1,10),0,'Z', SFSX00,YPGG00,BZ0000,YPSCCJ,ad_FBBH00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00 from bm_ybypml_temp07; SP_EXECUTE_SQL('alter table bm_ybsfxm disable all triggers'); -- update bm_ybsfxm a set (zfbl00,yblb00,SFYBXM)= -- (select zfbl00,YBLB00,SFYB00 from bm_ybypml_temp07 b where b.xmbh00=trim(a.xmbh00)) -- where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00 -- and exists (select 1 from bm_ybypml_temp07 b where b.xmbh00=trim(a.xmbh00)); --应该是处理交集而不是差集 同时该写法执行比较慢，主要是trim(a.xmbh00)慢 --update YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00,ypgg00 by CC 20170622 update bm_ybsfxm a set (zfbl00,yblb00,SFYBXM,BZ0000,SCCJ00,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00,ypgg00)= (select zfbl00,YBLB00,SFYB00,BZ0000,YPSCCJ,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1,XMDW01,CFBL00,ypgg00 from bm_ybypml_temp10 b where substr(b.xmbh00||' ',1,20)=a.xmbh00) where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00 and exists (select 1 from bm_ybypml_temp10 b where substr(b.xmbh00||' ',1,20)=a.xmbh00); commit; SP_EXECUTE_SQL('alter table bm_ybsfxm enable all triggers'); --4、备份表bm_ybsfdy数据 insert into BM_YBDYBF ( SFXMID,SFYP00,YBZXLB,YBXMBH,YBZYFP,YBMZFP, ZFBL00,SFYBXM,YBLB00,YBBRLB,FBBH00,YYDJ00, ZFJE00,JMBL00,JMJE00,SFSX00,BZ0000,YXBZ00,CZY000,CZRQ00,CZSJ00 ) select SFXMID,SFYP00,YBZXLB,YBXMBH,YBZYFP,YBMZFP, ZFBL00,SFYBXM,YBLB00,YBBRLB,FBBH00,YYDJ00, ZFJE00,JMBL00,JMJE00,SFSX00,BZ0000,YXBZ00,ad_CZY000,ls_CZRQ00,ls_CZSJ00 from bm_ybsfdy a where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00; commit; --5、修改对应表的自付比例、医保类别、是否医保项目（以医保项目表的数据为准） --if ad_GXLX00='1' then SP_EXECUTE_SQL('alter table bm_ybsfdy disable all triggers'); --end if; --5.1 修改对应表药品 --if ad_MLBH00 in ('1','2') then -- select count(1) into ls_COUNT0 from bm_ybypml_temp07 where length(trim(xmbh00))=20 and rownum=1; 应该是有更新的就做，而不是有新增时才做，而且不应该是全表bm_ybsfxm更新 select count(1) into ls_COUNT0 from bm_ybypml_temp09 where length(trim(xmbh00))=20 and rownum=1; if ls_COUNT0>0 then if ad_GXLX00='1' then update bm_ybsfdy a set ybxmbh=null,zfbl00='1',yblb00='7',sfybxm='N' where length(trim(YBXMBH))=20 and ybzxlb=ad_YBZXLB and sfyp00='Y' and FBBH00=ad_FBBH00 and not exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh); end if; --update YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1 by CC 20170622 update bm_ybsfdy a set (zfbl00,yblb00,SFYBXM,BZ0000,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1)= (select zfbl00,yblb00,SFYBXM,BZ0000,YBJSDJ,YBZGDJ,YBJSDJ1,YBZGDJ1 from bm_ybsfxm b where b.xmbh00=a.ybxmbh and ybzxlb=a.ybzxlb and FBBH00=a.FBBH00) where length(trim(YBXMBH))=20 and ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00 and a.sfyp00='Y' -- and exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh); and exists (select 1 from bm_ybypml_temp09 where substr(xmbh00||' ',1,20)=a.ybxmbh); end if; --end if; --5.2 修改对应表项目 --if ad_MLBH00 in ('1','3') then -- select count(1) into ls_COUNT0 from bm_ybypml_temp07 where length(trim(xmbh00))=12 and rownum=1; 应该是有更新的就做，而不是有新增时才做，而且不应该是全表bm_ybsfxm更新 select count(1) into ls_COUNT0 from bm_ybypml_temp09 where length(trim(xmbh00))=12 and rownum=1; if ls_COUNT0>0 then if ad_GXLX00='1' then update bm_ybsfdy a set ybxmbh=null,zfbl00='1',yblb00='7',sfybxm='N' where length(trim(YBXMBH))=12 and ybzxlb=ad_YBZXLB and sfyp00='N' and FBBH00=ad_FBBH00 and not exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh); end if; update bm_ybsfdy a set (zfbl00,yblb00,SFYBXM,BZ0000)= (select zfbl00,yblb00,SFYBXM,BZ0000 from bm_ybsfxm b where b.xmbh00=a.ybxmbh and ybzxlb=a.ybzxlb and FBBH00=a.FBBH00) where length(trim(YBXMBH))=12 and ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00 and a.sfyp00='N' -- and exists (select 1 from bm_ybsfxm where FBBH00=a.FBBH00 and ybzxlb=a.YBZXLB and xmbh00=a.ybxmbh); and exists (select 1 from bm_ybypml_temp09 where substr(xmbh00||' ',1,20)=a.ybxmbh); end if; --end if; commit; --if ad_GXLX00='1' then SP_EXECUTE_SQL('alter table bm_ybsfdy enable all triggers'); --end if; select count(1) into ad_GXHJLS from bm_ybsfxm where ybzxlb=ad_YBZXLB and FBBH00=ad_FBBH00; EXCEPTION WHEN NO_DATA_FOUND THEN as_YHMSG0:='数据没有找到，请核对您所输入的票据类别是否正确!'; as_SYSMSG:=SQLERRM; SP_EXECUTE_SQL('alter table bm_ybsfdy enable all triggers'); ROLLBACK; WHEN OTHERS THEN as_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！'; as_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_XM_YBDR_XLS('||ad_YBZXLB||','||ad_MLBH00||','||ad_GXLX00||',as_YHMSG0,as_SYSMSG)',1,200); SP_EXECUTE_SQL('alter table bm_ybsfdy enable all triggers'); ROLLBACK; END; / show error; --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%