mybatis调用Oracle存储过程，并接收返回值（游标类型）

初次学习存储过程调用，写得不是很好，有不对的地方大家多多指教，希望能对您有所帮助。

1.普通返回值：

Oracle存储过程：

/*测试用存储过程*/create or replace procedure p_test(v_slid in varchar2,v_sm out varchar2,v_sm2 out varchar2) authid current_user is
begin
  declare
  v_slid2 bdc_rec.slid%type:=rpad(v_slid,20,' ');
  v_djlx bdc_rec.djlx%type;
  v_lcmc bdc_rec.lcmc%type;
  v_count number:=0;
  begin
    select count(slid) into v_count from bdc_rec where slid=v_slid2;
    if v_count<>0 then
    select djlx,lcmc into v_djlx,v_lcmc from bdc_rec where slid=v_slid2;      
    dbms_output.put_line('受理号：'||v_slid2||',登记类型编号：'||v_djlx||',登记类型名称：'||v_lcmc);
    v_sm:='受理号：'||v_slid2||',登记类型编号：'||v_djlx||',登记类型名称：'||v_lcmc;
    v_sm2:='受理号：'||v_slid2||',登记类型编号：'||v_djlx;
    else
      v_sm:='受理号数据为空.';
      dbms_output.put_line('受理号数据为空.');
    end if;
    end;
    exception when no_data_found then
        dbms_output.put_line('no_data_found');
        when too_many_rows then
          raise_application_error(-20000,'对v_slid2查询时，找到多条数据');
    end p_test;v_sm out varchar2,v_sm2 out varchar2) authid current_user is
begin
  declare
  v_slid2 bdc_rec.slid%type:=rpad(v_slid,20,' ');
  v_djlx bdc_rec.djlx%type;
  v_lcmc bdc_rec.lcmc%type;
  v_count number:=0;
  begin
    select count(slid) into v_count from bdc_rec where slid=v_slid2;
    if v_count<>0 then
    select djlx,lcmc into v_djlx,v_lcmc from bdc_rec where slid=v_slid2;      
    dbms_output.put_line('受理号：'||v_slid2||',登记类型编号：'||v_djlx||',登记类型名称：'||v_lcmc);
    v_sm:='受理号：'||v_slid2||',登记类型编号：'||v_djlx||',登记类型名称：'||v_lcmc;
    v_sm2:='受理号：'||v_slid2||',登记类型编号：'||v_djlx;
    else
      v_sm:='受理号数据为空.';
      dbms_output.put_line('受理号数据为空.');
    end if;
    end;
    exception when no_data_found then
        dbms_output.put_line('no_data_found');
        when too_many_rows then
          raise_application_error(-20000,'对v_slid2查询时，找到多条数据');
    end p_test;

mybatis的mapper文件(statementType="CALLABLE"代表调用存储过程)：

<select id="ptest" parameterType="HashMap" statementType="CALLABLE">
		{call P_TEST(#{slid,mode=IN,jdbcType=VARCHAR},#{v_sm,mode=OUT,jdbcType=VARCHAR},#{v_sm2,mode=OUT,jdbcType=VARCHAR})}
</select>		{call P_TEST(#{slid,mode=IN,jdbcType=VARCHAR},#{v_sm,mode=OUT,jdbcType=VARCHAR},#{v_sm2,mode=OUT,jdbcType=VARCHAR})}
</select>

备注：参数v_sm、v_sm2对应service层的map.get("v_sm")、map.get("v_sm2")中的key，可以不用对应存储过程中的v_sm、v_sm2。

service层代码：

public String ptest(String slid){
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("slid", slid);
		basicDataTransferDao.ptest(map);
		System.out.println(map.get("v_sm"));
		System.out.println(map.get("v_sm2"));
		return map.get("v_sm").toString();
	}

2.返回值为集合数据：

Oracle存储过程：

create or replace procedure p_testList (v_sjrq in varchar2,v_cursor out sys_refcursor) is
begin
  open v_cursor for select slid,djlx,lcmc from bdc_rec where to_char(sjrq,'yyyy-MM-dd')=v_sjrq;
end ;

mybatis的mapper文件：

<resultMap type ="java.util.HashMap" id= "cursorMap"> <!--配置返回游标中别名对应的resultMap-->  
     <result column ="slid" property="slid" />  
     <result column ="djlx" property="djlx"  />  
     <result column ="lcmc" property="lcmc" />  
</resultMap >

<select id="ptestSysCursor" parameterType="java.util.HashMap" statementType="CALLABLE">
	{call P_TESTLIST(#{sjrq,mode=IN,jdbcType=VARCHAR},#{result,mode=OUT, jdbcType=CURSOR,javaType=ResultSet,resultMap=cursorMap})}
</select>

service层代码：

public String ptestSysCursor(String sjrq){
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("sjrq", sjrq);
		basicDataTransferDao.ptestSysCursor(map);
		ArrayList<Map<String, Object>> cursorList = (ArrayList<Map<String, Object>>) map.get("result");
		if(cursorList!=null&&cursorList.size()!=0){
			for (Map<String, Object> cursor : cursorList) {
				System.out.println(cursor.get("slid")+"\t"+cursor.get("djlx")+"\t"+cursor.get("lcmc"));
			}
			return cursorList.size()+"";
		}
		return null;
	}

结果如下：