MyBatis调用oracle存储过程-蒲公英云

MyBatis调用oracle存储过程

前言

存储过程书写要规范（IN 、OUT 参数一定要写上，例如：v_siteNo IN VARCHAR2），不然JAVA调用有问题，拿不到存储过程返回值

存储过程ONE

create or replace procedure P_GetToken(v_siteNo IN VARCHAR2,
                                     tv    OUT varchar2) IS

MyBatis代码

<select id="getSerialCodeBySiteCode" parameterType="Map" statementType="CALLABLE" >
         {call P_GetToken(
             #{v_siteNo,jdbcType=VARCHAR,mode=IN},#{tv,mode=OUT,jdbcType=VARCHAR}
         )}
</select>

后台代码

ServiceImp实现类获取存储过程返回值

@Override
public String getSerialCodeBySiteCode(String siteCode){

Map<String,String> map = new HashMap();
map.put("v_siteNo",siteCode);
customerMainDao.getSerialCodeBySiteCode(map);
return map.get("tv");

}

存储过程TWO

CREATE OR REPLACE PROCEDURE P_USERINFOLIST(BEGINDATE IN VARCHAR2, --格式yyyy-mm-dd hh24:mi:ss
                                               ENDDATE   IN VARCHAR2,
                                               SITENAME  IN VARCHAR2,
                                               EMPNO     IN VARCHAR2,
                                               scanType  in varchar2,
                                               RF        OUT SYS_REFCURSOR) IS

MyBatis代码

<resultMap type="CourierInfo" id="CourierInfoMap">
    <result column="RECEIVEINFO" property="consigneeName"/>
    <result column="BILLCODE" property="billCode"/>
    <result column="SCANTIME" property="expCabinetTime"/>
    <result column="CABINETTYPE" property="expCabinetType"/>
</resultMap>
<!-- 派件相关数据查询（查询类型：已签收、问题件、门店代收、派件入柜）-->
<select id="listCourierInfoList" statementType="CALLABLE">    
           {call P_USERINFOLIST(
              #{BeginDate,jdbcType=VARCHAR,mode=IN},
              #{EndDate,jdbcType=VARCHAR,mode=IN},
              #{SiteName,jdbcType=VARCHAR,mode=IN},
              #{EmpNo,jdbcType=VARCHAR,mode=IN},
              #{scanType,jdbcType=VARCHAR,mode=IN},
              #{rf,jdbcType=CURSOR,mode=OUT,resultMap=CourierInfoMap,javaType=java.sql.ResultSet}
          )}
</select>

后台代码

ServiceImp实现类获取存储过程返回值

@Override
public List listCourierInfoList(Map map) {
```
this.courierInfoDao.listCourierInfoList(map);
return (List) map.get("rf");
```
}