ORACLE存储过程编程应用实例-门诊药房发药

最近两个整理了门诊药房发药的业务逻辑，准备通过存储过程实现数据处理。耗费两天时间验证终于完成，对存储过程的了解又深入了一些，总结如下：

1、游标的遍历使用了FOR IN语句进行循环，比FETCH与方便了好多，不用声明一堆变量去接收；而且FOR循环使用的游标是自动打开、自动关闭的无需手动关闭。

2、通过跟踪调试发现，游标虽然是在过程开始就声明了，语句执行到FOR循环时才真正打开游标（执行游标定义的SELECT语句）。

3、存储过程中触发一个自定义的异常的语法：RAISE_APPLICATION_ERROR(-20001, '这是一个自定义的异常信息');

只是还有以下两个小疑问目前没有找到答案，那位朋友有明确答案期待在评论区留言告知：

  1）、在LOOP中直接RETURN不知道游标是否会自动关闭？所以这里不确定我使用了GOTO语句跳出到标签返回的。

  2）、如何实现在存储过程中让一个游标打开一次后，可以进行两次循环？也就是一次数据查询进行两次结果使用，只能使用临时表吗。

CREATE OR REPLACE PROCEDURE P_OPD_SENDDRUG (
--门诊药房发药过程 liuyc 20210928
receiveId IN INT, --收款单主表id
operateId IN INT, --操作员id
deptId IN INT, --执行发药药房id
mess OUT VARCHAR2, --OK为执行成功
sendId OUT INT, --生成发药记录主表id
priceHint OUT VARCHAR2, --低于进价发药记录的提示信息
needPrint OUT INT --返回1需要打印,0不需要打印
)
IS
--本组变量保存收款信息
rc_cardNum VARCHAR2(60); --收款单就诊号
rc_patientId NUMBER(12,0); --患者id
rc_patientName VARCHAR2(50); --患者姓名
rc_hosId NUMBER(12,0); --医院id
rc_invoiceCode VARCHAR2(60); --发票号
rc_isSend VARCHAR2(4); --发药状态
rc_status VARCHAR2(4); --收款单状态
--其他变量
v_sendQuantity NUMBER(12,2); --发药数量(基本单位表示的)
v_sendWriteId NUMBER(12,0); --已执行医嘱的id
v_isFirst BOOLEAN; --是否首行发药记录,只有首行写发药主表
v_i NUMBER(12,0); --临时计数用
--待发药医嘱记录游标(此处没有考虑库存批次有效性的验证)
/*列信息：医嘱id,库存id,药品编号,药品名称,药品规格,
发药单位,发药单位与基本单位换算比,发药数量,售价,医嘱发药状态,
用法id,用法名称,药品剂量,用药频次id,用药频次名称,
库存数量(基本单位表示的),药库进价(已换算为发药单位),
库存批号
*/
CURSOR cur_send IS
  select a.ORDER_WRITE_ID,c.STOCK_ID,b.DOCTOR_ORDER_CODE,b.DOCTOR_ORDER_NAME,b.SPECIFICATION,
         e.PACKAGE_UNIT,e.RELATION,c.QUANTITY,c.PRICE,b.ORDER_STATUS_ID,
         b.USAGE_ID,f.USAGE_NAME,b.DOSE,b.FREQUENCY_ID,g.FREQUENCY_NAME,
         d.QUANTITY AS STOCK_QUANTITY,ROUND(d.PURCHASE_PRICE/d.RELATION_MAX,4) as PUR_PRICE,
         d.BATCH_NUMBER,
         (CASE WHEN d.QUANTITY<ROUND(e.RELATION*c.QUANTITY,2) THEN 1 ELSE 0 END) AS LESS_FLAG  --库存不足标志
  from T_OPD_RECEIVABLES_DETAIL a
  inner join T_OPD_DOCTOR_ORDER_WRITE b on a.ORDER_WRITE_ID=b.ID and b.DOCTOR_ORDER_TYPE_ID='drug' and b.EXECUTIVE_SECTION_ID=deptId
  inner join T_OPD_QUOTN c on c.ORDER_WRITE_ID=b.ID
  inner join T_DRUGSTORE_DRUG_STOCK d on c.STOCK_ID=d.id
  inner join T_PHARMACY_DRUG_PACKAGE e on b.DRUG_UNIT_ID=e.id
  left join T_SYS_BASIC_USAGE f on b.USAGE_ID=f.ID
  left join T_SYS_BASIC_FREQUENCY g on b.FREQUENCY_ID=g.ID
  where a.RECEIVABLES_ID=receiveId
  ORDER BY b.id,c.id;
BEGIN
mess:='';
--取得收款单主表信息
  BEGIN
    SELECT CARD_NUM,PATIENT_ID,PATIENT_NAME,HOSPITAL_ID,INVOICE_CODE,IS_SEND_DRUG,STATUS 
    INTO rc_cardNum,rc_patientId,rc_patientName,rc_hosId,rc_invoiceCode,rc_isSend,rc_status
    FROM T_OPD_RECEIVABLES WHERE id=receiveId FOR UPDATE NOWAIT;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    mess:='待发药收款单'||rc_invoiceCode||'记录不存在!';
    RETURN;
  END;
  --只有收款状态1收款、2退款后生成的新发票,可以进行发药操作
  IF NVL(rc_status,'null') NOT IN ('1','2') THEN
    mess:='收款单'||rc_invoiceCode||'状态值:'||NVL(rc_status,'null')||',不满足发药操作条件.';
    RETURN;
  END IF;
  --只有发药状态为0未发药、2已打印处方未发药 可以进行发药操作
  IF NVL(rc_isSend,'null') NOT IN ('0','2') THEN
    mess:='收款单'||rc_invoiceCode||'发药状态值:'||NVL(rc_isSend,'null')||',不满足发药操作条件.';
    RETURN;
  END IF;
  --如果处方未打印过,需要打印处方标志赋值1
  needPrint:=(CASE WHEN rc_isSend=2 THEN 0 ELSE 1 END);
--先验证发药记录中是否存在库存不足的批次信息,尽量避免数据修改中途撤销的情况
mess:='';
v_i:=0;
FOR item IN (
  select ('品名/规格：'||b.DOCTOR_ORDER_NAME||' '||b.SPECIFICATION||'，批号：'||d.BATCH_NUMBER
         ||'，出库量：'||c.QUANTITY||e.PACKAGE_UNIT||'，库存量：'||ROUND(d.QUANTITY/e.RELATION,2)) AS stockInfo
  from T_OPD_RECEIVABLES_DETAIL a
  inner join T_OPD_DOCTOR_ORDER_WRITE b on a.ORDER_WRITE_ID=b.ID and b.DOCTOR_ORDER_TYPE_ID='drug' and b.EXECUTIVE_SECTION_ID=deptId
  inner join T_OPD_QUOTN c on c.ORDER_WRITE_ID=b.ID
  inner join T_DRUGSTORE_DRUG_STOCK d on c.STOCK_ID=d.id
  inner join T_PHARMACY_DRUG_PACKAGE e on b.DRUG_UNIT_ID=e.id
  WHERE d.QUANTITY<ROUND(e.RELATION*c.QUANTITY,2) and a.RECEIVABLES_ID=receiveId
  ORDER BY b.id,c.id
) LOOP
  v_i:=v_i+1;
  mess:=mess||item.stockInfo||chr(13);
END LOOP;
IF v_i>0 THEN
  mess:='药品库存不足'||chr(13)||mess;
  RETURN;
END IF;
--开始业务数据处理,遍历药品医嘱待出库记录进行发药库存处理
v_sendWriteId:=0; --每个医嘱发药时更新医嘱状态为已执行,一条医嘱可能会发多个批次
V_isFirst:=TRUE;
FOR item IN cur_send LOOP
--如果有处理的发药明细,写入发药主表记录
  IF V_isFirst THEN
    V_isFirst:=FALSE;
    sendId:=T_DRUGSTORE_DRUG_SEND_ID.nextval;
    INSERT INTO T_DRUGSTORE_DRUG_SEND(ID,CARD_NUM,INVOICE_CODE,PATIENT_ID,PATIENT_NAME,DEPT_ID,HOSPITAL_ID,CREATE_DATE,CREATE_OPER,PRINT_COUNT)
      VALUES(sendId,rc_cardNum,rc_invoiceCode,rc_patientId,rc_patientName,deptId,rc_hosId,sysdate,operateId,0);
  END IF;
  --更新医嘱发药状态
  IF (v_sendWriteId!=item.ORDER_WRITE_ID) THEN
    v_sendWriteId:=item.ORDER_WRITE_ID;
    --如果医嘱状态已执行则返回并提示
    IF item.ORDER_STATUS_ID='4' THEN
      mess:='已执行过的医嘱 品名/规格：'||item.DOCTOR_ORDER_NAME||' '||item.SPECIFICATION;
      GOTO ExitLabel;
    END IF;
    UPDATE T_OPD_DOCTOR_ORDER_WRITE SET ORDER_STATUS_ID='4',UPDATE_OPER=operateId,UPDATE_DATE=SYSDATE 
      WHERE ORDER_STATUS_ID!='4' AND ID=v_sendWriteId;
    IF SQL%ROWCOUNT<1 THEN
      mess:=item.DOCTOR_ORDER_NAME||' 医嘱发药状态更新了0行,医嘱id:'||v_sendWriteId;
      GOTO ExitLabel;
    END IF;
  END IF;
  --更新正式库存
  v_sendQuantity:=ROUND(item.RELATION*item.QUANTITY,2);
  UPDATE T_DRUGSTORE_DRUG_STOCK SET QUANTITY=QUANTITY-v_sendQuantity,UPDATE_OPER=operateId,UPDATE_DATE=SYSDATE
    WHERE QUANTITY>=v_sendQuantity AND ID=item.STOCK_ID;
  IF SQL%ROWCOUNT<1 THEN
    mess:=item.DOCTOR_ORDER_NAME||' 扣减库存更新了0行,库存id:'||item.STOCK_ID;
    GOTO ExitLabel;
  END IF;
  --写入门诊发药明细表
  INSERT INTO T_DRUGSTORE_DRUG_SEND_DETAIL(ID,SEND_ID,OPDER_WRITE_ID,DOCTOR_ORDER_ID,DRUG_CODE,
    DRUG_NAME,SPECIFICATION,ORDER_DRUG_COUNT,RETURN_COUNT,SEND_COUNT,
    PRICE,USAGE_ID,USAGE_NAME,DOSE,FREQUENCY_ID,
    FREQUENCY_NAME,PURCHASE_PRICE)
    VALUES(T_DRUGSTORE_DRUG_SEND_ID.nextval,sendId,item.ORDER_WRITE_ID,item.STOCK_ID,item.DOCTOR_ORDER_CODE,
      item.DOCTOR_ORDER_NAME,item.SPECIFICATION,item.QUANTITY,0,item.QUANTITY,
      item.PRICE,item.USAGE_ID,item.USAGE_NAME,item.DOSE,item.FREQUENCY_ID,
      item.FREQUENCY_NAME,item.PUR_PRICE);
  --对比医嘱药品售格和进价对比,如果低于进价添加到提示信息中
  IF item.PUR_PRICE>item.PRICE THEN
    priceHint:='门诊发药-库存主键:'||item.STOCK_ID
      ||'编号:'||item.DOCTOR_ORDER_CODE||'品名:'||item.DOCTOR_ORDER_NAME||chr(13);    
  END IF;
END LOOP;
--最后判断本收款单所有药品医嘱是否已不存在非4的记录(即均已执行发药)，如果是则修改过收款单状态为1已发药。
select COUNT(*) into v_i from DUAL
 where exists(
    select *
    from T_OPD_RECEIVABLES_DETAIL a
    inner join T_OPD_DOCTOR_ORDER_WRITE b on a.ORDER_WRITE_ID=b.ID and b.DOCTOR_ORDER_TYPE_ID='drug' and b.ORDER_STATUS_ID!=4
    WHERE a.RECEIVABLES_ID=receiveId);
IF v_i=0 THEN
  UPDATE T_OPD_RECEIVABLES SET IS_SEND_DRUG=1,UPDATE_OPER=operateId,UPDATE_DATE=SYSDATE WHERE ID=receiveId;
END IF;
mess:='OK';
RETURN;
--游标循环体内退出的情况跳转到这里(循环体内直接RETURN会导致游标未关闭)
<<ExitLabel>>
  RETURN;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    mess:='查询记录数据为空!';
  WHEN OTHERS THEN
    mess := SUBSTR(SQLERRM,1,2000);
END P_OPD_SENDDRUG;
/

 存储过程调用语句

declare
mess VARCHAR2(800);
sendId INT;
priceHint VARCHAR2(800);
needPrint INT;
BEGIN
  P_OPD_SENDDRUG(6923463,3315003,3347533,mess,sendId,priceHint,needPrint);
  DBMS_OUTPUT.put_line(mess);
  DBMS_OUTPUT.put_line(sendId);
end;
rollback;

 JAVA中mybatis中mapper.xml配置

//声明
void opdSendDrug(Map<String,Object> params);
//实现   
    <select id="opdSendDrug" statementType="CALLABLE" resultType="java.util.Map">
        {call P_OPD_SENDDRUG(
                #{receiveId,mode=IN,jdbcType=BIGINT},
                #{operateId,mode=IN,jdbcType=BIGINT},
                #{deptId,mode=IN,jdbcType=BIGINT},
                #{mess,mode=OUT,jdbcType=VARCHAR},
                #{sendId,mode=OUT,jdbcType=BIGINT},
                #{priceHint,mode=OUT,jdbcType=VARCHAR},
                #{needPrint,mode=OUT,jdbcType=INT}
            )}
    </select>

服务层传参调用，这里使用了自有的一个类MapFetcher进行数据转换

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Long doOpdSendDrug(Long receiveId, Long operateId, Long deptId, Map<String, String> message) {
        //调用门诊发药数据库存储过程
        Map<String,Object> params=new HashMap<>();
        params.put("receiveId", receiveId);
        params.put("operateId", operateId);
        params.put("deptId",deptId);
        mapper.opdSendDrug(params);
        //获取存储过程的返回值,返回值非OK的情况代表没有正常结束,抛出异常回滚事务
        MapFetcher mapFch = new MapFetcher(params);
        String retMess=mapFch.get(String.class,"mess","");
        if (!"OK".equals(retMess)){
            throw new RuntimeException("门诊发药失败!" +retMess);
        }
        Long sendDrugId=mapFch.get(Long.class,"sendId");
        message.put("priceHint",mapFch.get(String.class,"priceHint"));
        message.put("needPrint",mapFch.get(String.class,"needPrint"));
        return sendDrugId;
    }