mybatis调用oracle存储过程 返回结果集

一、ORACLE

1、创建包

CREATE OR REPLACE PACKAGE pkg_test
IS
   TYPE v_cur IS REF CURSOR;

   PROCEDURE p_test (
      param1       IN       VARCHAR2,
      param2       IN       VARCHAR2, 
      p_cur        OUT      v_cur
   );
END pkg_test;

2、创建包体 ，存储过程

CREATE OR REPLACE PACKAGE BODY PKG_TEST
IS
   PROCEDURE P_TEST (  PARAM1        IN     VARCHAR2, 
                       PARAM2        IN     VARCHAR2, 
                       P_CUR         OUT    V_CUR )
   IS
      V_PARAM1      VARCHAR2 (8) := NULL;
   BEGIN
      IF  PARAM1 IS NULL
      THEN
         V_PARAM1   := '00000000';
      ELSE
         V_PARAM1  := PARAM1;
      END IF;
      OPEN P_CUR FOR
      SELECT * FROM TABLE -- 略 ...
         ;
   END P_TEST ;
END PKG_TEST;

二、JAVA 结果集用Map接收

1、mapper.xml  

        2个入参，1个输出，入参不传直接写''则为null

	<resultMap id="xxMap" type="java.util.HashMap">
	</resultMap>
	
<select id="findData" statementType="CALLABLE" parameterType="java.util.Map">
	<![CDATA[
	call PKG_TEST.P_TEST('',
	  #{endDate,jdbcType=VARCHAR,mode=IN},
      #{p_cur,jdbcType=CURSOR,mode=OUT,resultMap=xxMap}
	)
	]]>
</select>

2、dao

	@DS(value = "datasource") // 配置多数据源
	String findData(Map<String, Object> map);

3、service

	@Override
	public void findData() {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("startDate", "20200301");
		map.put("endDate", "20200309");
		map.put("p_cur", OracleTypes.CURSOR);
		this.xxDao.findData(map);
		System.out.println(map.get("p_cur"));
	}

三、JAVA 结果集映射类

1、mapper.xml  

	 <resultMap id="resMap" type="com.*.*.entity.User">
		<result column="id" property="id"></result>
		<result column="name" property="name"></result>
	</resultMap>

	<select id="findData" statementType="CALLABLE" parameterType="java.util.Map" >
        <![CDATA[
			call call PKG_TEST.P_TEST(
				#{startDate,jdbcType=VARCHAR,mode=IN},
				#{endDate,jdbcType=VARCHAR,mode=IN},
				'','',#{p_cur,jdbcType=CURSOR,mode=OUT,resultMap=resMap}
			)
		]]>
    </select>

2、dao

	@DS(value = "ds")
	void findData(Map<String, Object> map);

3、service

	@Override
	public void findData() {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("startDate", "20200320");
		map.put("endDate", "20200330");
		map.put("p_cur", new ArrayList<User>());
		this.xxDao.findData(map);
		List<User> list = (List<User>)map.get("p_cur");
		System.out.println(list.size());
	}