MyBatis调用oracle存储过程

逃离我推掉我的手 2022-01-12 09:51 397阅读 0赞

### 前言 ###

>  *  存储过程书写要规范（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<CourierInfo> listCourierInfoList(Map<String, Object> map) { 
    	this.courierInfoDao.listCourierInfoList(map);
    	return (List) map.get("rf");
    }