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

阳光穿透心脏的1/2处 2021-06-22 15:37 882阅读 0赞

一、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

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

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());
    	}