最近在oracle中通过存储过程实现一个报表查询，查询涉及到数据计算这里使用了临时表和存储过程实现输出查询，java接受游标变量返回结果集
第一步、创建统计使用的临时表
CREATE GLOBAL TEMPORARY TABLE T_TMP_REPORT_YFCHSLZZ( DRUG_ID NUMBER(22) not null, --药品id MAX_UNIT_ID NUMBER(22) not null, --最大包装id DRUG_UNIT_ID NUMBER(22), --药房包装单位id RECORD_FLAG NUMBER(4) not null, --记录类型0药品记录,1期初,2入库,3盘点盈亏,4出库,5报损 PRE_QUANTITY NUMBER(18,4) default 0 not null, --期初发生数量 QUANTITY NUMBER(18,4) default 0 not null, --发生数量 RELATION NUMBER(12) --业务单据包装单位换算比 ) ON COMMIT PRESERVE ROWS / create index IDX_TMP_REPORT_YFCHSLZZ_DF on T_TMP_REPORT_YFCHSLZZ (DRUG_ID,RECORD_FLAG); /
第二步、创建存储过程，过程代码省略部分统计语句
create or replace PROCEDURE P_RPT_YFCHSLZZ ( --药房统计存货数量总账用过程 liuyc 20230317 v_typeIds IN VARCHAR2, --药品类别id逗号隔开字符串 v_drugId IN VARCHAR2, --药品id v_deptId IN INT, --药房id v_hosId IN INT, --医院id v_includeDisable IN INT, --是否包含停用药品0否1是 v_beginDate IN DATE, --统计起日期 v_endDate IN DATE, --统计止日期 v_result out SYS_REFCURSOR --返回结果列表 ) IS v_sql varchar2(2000); --动态执行的语句 v_expType varchar2(500); --药品类别查询条件 v_expDrug varchar2(100); --药品id查询条件 v_expDis varchar2(100); --药品停用查询条件 v_firstDay DATE; --统计期间月份的第一天 BEGIN --取得要统计的药品记录 delete from T_TMP_REPORT_YFCHSLZZ; --构建查询药品的条件 v_expType:=''; if length(nvl(v_typeIds,''))>0 then v_expType:=' and a.DRUG_TYPE_ID in ('||v_typeIds||')'; end if; v_expDrug:=''; if length(nvl(v_drugId,''))>0 then v_expDrug:=' and a.ID='||v_drugId; end if; v_expDis:=' a.STATUS=''1'''; if v_includeDisable=1 then v_expDis:=' 1=1'; end if; v_sql:='insert into T_TMP_REPORT_YFCHSLZZ(DRUG_ID,MAX_UNIT_ID,DRUG_UNIT_ID,RECORD_FLAG) select a.ID,min(b.ID) MAX_UNIT_ID,nvl(min(c.PACKAGE_UNIT_ID),min(b.ID)) DRUG_UNIT_ID,0 RECORD_FLAG from T_PHARMACY_DRUG a inner join T_PHARMACY_DRUG_PACKAGE b on b.DRUG_ID=a.ID and b.IS_MAX_PACKAGE_UNIT=''1'' and b.STATUS=''1'' left join T_PHARMACY_DRUGSTORE_PACKAGE c on c.DRUG_ID=a.ID and c.DEPT_ID='||v_deptId||' and c.STATUS=''1'' where '||v_expDis||v_expType||v_expDrug||' and a.HOSPITAL_ID='||v_hosId||' GROUP BY a.ID order by a.ID'; execute immediate v_sql; --更新药房包装单位换算比 merge into T_TMP_REPORT_YFCHSLZZ a using T_PHARMACY_DRUG_PACKAGE b on (a.DRUG_UNIT_ID=b.ID) when matched then update set a.RELATION=b.RELATION where a.RECORD_FLAG=0; --添加期初数据,药房定时盘点库存数量是药房包装单位的数量,非基本单位数量 select trunc(v_beginDate,'MM') into v_firstDay from dual; insert into T_TMP_REPORT_YFCHSLZZ(DRUG_ID,MAX_UNIT_ID,RECORD_FLAG,PRE_QUANTITY,DRUG_UNIT_ID) select t1.DRUG_ID,0,1,t1.STOCK_COUNT,t1.MAX_PACKAGE_UNIT_ID from T_DRUGSTORE_INVENTORY_VOUCHER t join T_DRUGSTORE_INVENTORY_DETAIL t1 on t1.VOUCHER_ID = t.ID and t1.STOCK_COUNT!=0 join T_TMP_REPORT_YFCHSLZZ c on t1.DRUG_ID=c.DRUG_ID and c.RECORD_FLAG=0 where t.INVENTORY_METHOD_ID = '-99999' and t.IS_TALLY = '1' and t.TALLY_DATE between v_firstDay-1 and v_firstDay+1 and t.INVENTORY_DEPT_ID = v_deptId and t.hospital_id = v_hosId; commit; --统计药库入库药房数据 insert into T_TMP_REPORT_YFCHSLZZ(DRUG_ID,MAX_UNIT_ID,RECORD_FLAG,PRE_QUANTITY,QUANTITY,DRUG_UNIT_ID) select t1.DRUG_ID,0,2, SUM(case when t.APPROVAL_DATE<v_beginDate then t1.PURCHASE_QUANTITY else 0 end) PRE_QUANTITY, SUM(case when t.APPROVAL_DATE<v_beginDate then 0 else t1.PURCHASE_QUANTITY end) QUANTITY,t1.MAX_PACKAGE_UNIT_ID from T_PHARMACY_OUT_BACK_VOUCHER t join T_PHARMACY_OUT_BACK_DETAIL t1 on t1.VOUCHER_ID = t.ID join T_TMP_REPORT_YFCHSLZZ c on t1.DRUG_ID=c.DRUG_ID and c.RECORD_FLAG=0 where t.IS_APPROVAL='1' and t.IS_TALLY = '1' and t.TYPE in (1, 2) and t.APPROVAL_DATE between v_firstDay and v_endDate+0.99999 and t.UNIT_ID = v_deptId and t.hospital_id = v_hosId group by t1.DRUG_ID,t1.MAX_PACKAGE_UNIT_ID; commit; --统计药房移库调拨数据 insert into T_TMP_REPORT_YFCHSLZZ(DRUG_ID,MAX_UNIT_ID,RECORD_FLAG,PRE_QUANTITY,QUANTITY,DRUG_UNIT_ID) with ect_trans_bill as ( select t1.DRUG_ID,decode(t.TO_DEPT_ID,v_deptId,2,4) RECORD_FLAG, SUM(case when t.TALLY_DATE<v_beginDate then t1.QUANTITY else 0 end) PRE_QUANTITY, SUM(case when t.TALLY_DATE<v_beginDate then 0 else t1.QUANTITY end) QUANTITY,t1.PACKAGE_UNIT from T_DRUGSTORE_TRANSFER t join T_DRUGSTORE_TRANSFER_DETAIL t1 on t1.RECORD_ID = t.ID join T_TMP_REPORT_YFCHSLZZ c on t1.DRUG_ID=c.DRUG_ID and c.RECORD_FLAG=0 where t.TALLY_STATUS = '1' and t.TO_DEPT_ID!=t.FROM_DEPT_ID and (t.TO_DEPT_ID=v_deptId or t.FROM_DEPT_ID=v_deptId) and t.TALLY_DATE between v_firstDay and v_endDate+0.99999 and t.hospital_id = v_hosId group by t1.DRUG_ID,t1.PACKAGE_UNIT,decode(t.TO_DEPT_ID,v_deptId,2,4) ) select a.DRUG_ID,0,a.RECORD_FLAG,a.PRE_QUANTITY,a.QUANTITY, (select ID from T_PHARMACY_DRUG_PACKAGE where PACKAGE_UNIT=a.PACKAGE_UNIT and STATUS='1' and DRUG_ID=a.DRUG_ID and ROWNUM=1) from ect_trans_bill a; commit; --统计药房盘点盈亏数据 insert into T_TMP_REPORT_YFCHSLZZ(DRUG_ID,MAX_UNIT_ID,RECORD_FLAG,PRE_QUANTITY,QUANTITY,DRUG_UNIT_ID) select t1.DRUG_ID,0,3, SUM(case when t.TALLY_DATE<v_beginDate then t1.PROFIT_COUNT else 0 end) PRE_QUANTITY, SUM(case when t.TALLY_DATE<v_beginDate then 0 else t1.PROFIT_COUNT end) QUANTITY,t1.MAX_PACKAGE_UNIT_ID from T_DRUGSTORE_INVENTORY_VOUCHER t join T_DRUGSTORE_INVENTORY_DETAIL t1 on t1.VOUCHER_ID = t.ID join T_TMP_REPORT_YFCHSLZZ c on t1.DRUG_ID=c.DRUG_ID and c.RECORD_FLAG=0 where t.IS_TALLY = '1' and t.INVENTORY_METHOD_ID != '-99999' and t.TALLY_DATE between v_firstDay and v_endDate+0.99999 and t.INVENTORY_DEPT_ID = v_deptId and t.hospital_id = v_hosId group by t1.DRUG_ID,t1.MAX_PACKAGE_UNIT_ID; commit; --统计药房发药数据(门诊发药) insert into T_TMP_REPORT_YFCHSLZZ(DRUG_ID,MAX_UNIT_ID,RECORD_FLAG,PRE_QUANTITY,QUANTITY,DRUG_UNIT_ID) select t1.DOCTOR_ORDER_ID,0,4, -SUM(case when t.CREATE_DATE<v_beginDate then t1.SEND_COUNT else 0 end) PRE_QUANTITY, SUM(case when t.CREATE_DATE<v_beginDate then 0 else t1.SEND_COUNT end) QUANTITY,e.DRUG_UNIT_ID from T_DRUGSTORE_DRUG_SEND t join T_DRUGSTORE_DRUG_SEND_DETAIL t1 on t1.SEND_ID = t.ID join T_TMP_REPORT_YFCHSLZZ c on t1.DOCTOR_ORDER_ID=c.DRUG_ID and c.RECORD_FLAG=0 join T_OPD_DOCTOR_ORDER_WRITE e on t1.OPDER_WRITE_ID=e.ID and e.HOSPITAL_ID=t.HOSPITAL_ID where t.CREATE_DATE between v_firstDay and v_endDate+0.99999 and t.DEPT_ID = v_deptId and t.hospital_id = v_hosId group by t1.DOCTOR_ORDER_ID,e.DRUG_UNIT_ID; commit; --统计药房发药数据(门诊退药)此处省略... --统计药房发药数据(住院发药)此处省略... --统计药房发药数据(住院退药)此处省略... --统计报损信息 insert into T_TMP_REPORT_YFCHSLZZ(DRUG_ID,MAX_UNIT_ID,RECORD_FLAG,PRE_QUANTITY,QUANTITY,DRUG_UNIT_ID) select t1.DRUG_ID,0,5, -SUM(case when t.tally_date<v_beginDate then t1.QUANTITY else 0 end) PRE_QUANTITY, SUM(case when t.tally_date<v_beginDate then 0 else t1.QUANTITY end) QUANTITY,t1.MAX_PACKAGE_UNIT_ID from t_drugstore_loss t join t_drugstore_loss_detail t1 on t1.voucher_id = t.ID join T_TMP_REPORT_YFCHSLZZ c on t1.DRUG_ID=c.DRUG_ID and c.RECORD_FLAG=0 where t.CREATE_DATE between v_firstDay and v_endDate+0.99999 and t.UNIT_ID = v_deptId and t.hospital_id = v_hosId group by t1.DRUG_ID,t1.MAX_PACKAGE_UNIT_ID; --更新数量为基本单位数量 merge into T_TMP_REPORT_YFCHSLZZ a using T_PHARMACY_DRUG_PACKAGE b on (a.DRUG_UNIT_ID=b.ID and a.RECORD_FLAG>0) when matched then update set a.RELATION=b.RELATION,a.PRE_QUANTITY=a.PRE_QUANTITY*b.RELATION,a.QUANTITY=a.QUANTITY*b.RELATION; commit; --查询汇总结果:记录类型0药品记录,1期初,2入库,3盘点盈亏,4出库,5报损 open v_result for with ect_sum as ( select DRUG_ID,sum(PRE_QUANTITY) PRE_QUANTITY, sum(case when RECORD_FLAG=2 then QUANTITY else 0 end) IN_QUANTITY, sum(case when RECORD_FLAG=4 then QUANTITY else 0 end) OUT_QUANTITY, sum(case when RECORD_FLAG=3 then QUANTITY else 0 end) INV_QUANTITY, sum(case when RECORD_FLAG=5 then QUANTITY else 0 end) LOSS_QUANTITY from T_TMP_REPORT_YFCHSLZZ where RECORD_FLAG>0 group by DRUG_ID ) select a.DRUG_ID ID,c.DRUG_CODE,c.DRUG_NAME,c.SPEC,b.DRUG_UNIT_ID, (select PACKAGE_UNIT from T_PHARMACY_DRUG_PACKAGE where ID=b.DRUG_UNIT_ID) UNIT_NAME, c.DRUG_TYPE_ID,d.name DRUG_TYPE_NAME, ROUND(a.PRE_QUANTITY/b.RELATION,4) PRE_QUANTITY, ROUND(a.IN_QUANTITY/b.RELATION,4) IN_QUANTITY, ROUND(a.OUT_QUANTITY/b.RELATION,4) OUT_QUANTITY, ROUND(a.INV_QUANTITY/b.RELATION,4) INV_QUANTITY, ROUND(a.LOSS_QUANTITY/b.RELATION,4) LOSS_QUANTITY, ROUND((a.PRE_QUANTITY+a.IN_QUANTITY-a.OUT_QUANTITY+a.INV_QUANTITY-a.LOSS_QUANTITY)/b.RELATION,4) FINAL_QUANTITY from ect_sum a join T_TMP_REPORT_YFCHSLZZ b on a.DRUG_ID=b.DRUG_ID and b.RECORD_FLAG=0 join T_PHARMACY_DRUG c on a.DRUG_ID=c.ID and c.HOSPITAL_ID=v_hosId left join T_SYS_DICTITEM d on d.id=c.DRUG_TYPE_ID and d.GROUP_ID='drugTypeId' order by DRUG_TYPE_NAME,DRUG_CODE; end P_RPT_YFCHSLZZ; /
第三步、java代码Mapper文件调用存储过程，返回实体代码此处不再展示，方法声明：void listInOutQuantityGL(Map<String,Object> param);
<resultMap id="InOutQuantityGLResultMap" type="cn.net.sunshine.hisManagement.drugStoreReport.bean.InOutQuantityGL">
<id column="ID" property="id"/>
<result column="DRUG_CODE" jdbcType="VARCHAR" property="drugCode"/>
<result column="DRUG_NAME" jdbcType="VARCHAR" property="drugName"/>
<result column="SPEC" jdbcType="VARCHAR" property="spec"/>
<result column="DRUG_UNIT_ID" jdbcType="DECIMAL" property="drugUnitId"/>
<result column="UNIT_NAME" jdbcType="VARCHAR" property="unitName"/>
<result column="DRUG_TYPE_ID" jdbcType="DECIMAL" property="drugTypeId"/>
<result column="DRUG_TYPE_NAME" jdbcType="VARCHAR" property="typeName"/>
<result column="PRE_QUANTITY" jdbcType="DECIMAL" property="preQuantity"/>
<result column="IN_QUANTITY" jdbcType="DECIMAL" property="inQuantity"/>
<result column="OUT_QUANTITY" jdbcType="DECIMAL" property="outQuantity"/>
<result column="INV_QUANTITY" jdbcType="DECIMAL" property="invQuantity"/>
<result column="LOSS_QUANTITY" jdbcType="DECIMAL" property="lossQuantity"/>
<result column="FINAL_QUANTITY" jdbcType="DECIMAL" property="finalQuantity"/>
</resultMap>
<!--收发存数量汇总查询-->
<select id="listInOutQuantityGL" parameterType="java.util.Map" statementType="CALLABLE">
call P_RPT_YFCHSLZZ(
#{v_typeIds,mode=IN,jdbcType=VARCHAR},
#{v_drugId,mode=IN,jdbcType=VARCHAR},
#{v_deptId,mode=IN,jdbcType=DECIMAL},
#{v_hosId,mode=IN,jdbcType=DECIMAL},
#{v_disableDrug,mode=IN,jdbcType=DECIMAL},
#{v_beginDate,mode=IN,jdbcType=DATE},
#{v_endDate,mode=IN,jdbcType=DATE},
#{v_result,jdbcType=CURSOR,mode=OUT,javaType=ResultSet, resultMap=InOutQuantityGLResultMap}
)
</select>
第四步、java中Service层调用代码，这样就可以获取存储过程返回的结果集。
@Override public List<InOutQuantityGL> listInOutQuantityGL(InOutQuantityGLQuery queryParam) { Map<String,Object> param =new HashMap<String, Object>(); param.put("v_drugId",queryParam.getDrugId()==null?"":queryParam.getDrugId().toString()); if (queryParam.getDrugType()==null || queryParam.getDrugType().length<=0){ param.put("v_typeIds",""); } else{ String typeIds = StringUtils.join(queryParam.getDrugType(),","); param.put("v_typeIds",typeIds); } param.put("v_deptId",queryParam.getDeptId()); param.put("v_hosId",queryParam.getHosId()); param.put("v_disableDrug",queryParam.getIncludeDisable()?1:0); param.put("v_beginDate",queryParam.getBeginDate()); param.put("v_endDate",queryParam.getEndDate()); drugStoreReportMapper.listInOutQuantityGL(param); List<InOutQuantityGL> list = (List<InOutQuantityGL>)param.get("v_result"); Integer rowNo = 1; for (InOutQuantityGL item:list){ item.setRowNo(rowNo); rowNo++; } return list; }