前言
儲存過程書寫要規範(IN 、OUT 參數一定要写上,例如:v_siteNo IN VARCHAR2),不然JAVA调用有问题,拿不到存储过程返回值
儲存過程1
create or replace procedure P_GetToken(v_siteNo IN VARCHAR2,
tv OUT varchar2) IS
Mybatis xml
<select id="getSerialCodeBySiteCode" parameterType="Map" statementType="CALLABLE" >
{call P_GetToken(
#{v_siteNo,jdbcType=VARCHAR,mode=IN},#{tv,mode=OUT,jdbcType=VARCHAR}
)}
</select>
Java 後端代碼
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");
}
儲存過程2
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 xml
<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>
java後端代碼
ServiceImp實現類獲取儲存過程返回值
@Override
public List<CourierInfo> listCourierInfoList(Map<String, Object> map) {
this.courierInfoDao.listCourierInfoList(map);
return (List) map.get("rf");
}