最近在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;
}
标签：报表,--,YFCHSLZZ,游标,DRUG,oracle,ID,UNIT,QUANTITY From： https://www.cnblogs.com/sdlz/p/17240442.html