ibatis调用oracle存储过程分页

1、users.xml片段

<resultMap class="user" id="get-all-user">
		<result property="id" column="id" />
		<result property="userName" column="username" />
		<result property="password" column="password" />
	</resultMap>
……
<parameterMap id="swapParameters" class="java.util.Map">
        <parameter property="sqlscript" jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/>
        <parameter property="pageSize" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
        <parameter property="pageIndex" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
        <parameter property="totalCount" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
        <parameter property="totalPage" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
        <parameter property="v_cur" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT" resultMap="get-all-user" />
     </parameterMap>
     <procedure id="retrieveByLikeCmpRoleC" parameterMap="swapParameters">
            {call P_QuerySplit(?,?,?,?,?,?)}
     </procedure>

2、oracle存储过程

create or replace procedure P_QuerySplit(sqlscript  varchar2, --表名/SQL语句
                                         pageSize   integer, --每页记录数
                                         pageIndex  integer, --当前页
                                         totalCount out number, --总记录数
                                         totalPage  out number, --总页数
                                         v_cur      out sys_refcursor --返回游标
                                         ) is
  v_PageSize   number;
  v_PageIndex  number;
  v_SQL_Count  varchar2(4000);
  v_SQL        varchar2(4000);
  v_StartIndex number;
  v_EndIndex   number;
begin
  v_PageSize := pageSize;
  if v_PageSize = 0 then
    v_PageSize := 1;
  end if;

  --统计记录数量
  v_SQL_Count := 'select count(*) from (' || sqlscript || ') a ';
  execute immediate v_SQL_Count
    into totalCount;
  --计算总页数
  totalPage := CEIL(totalCount / v_PageSize);
  --验证页号 如果页号大余了最大页数，返回最后一页
  v_PageIndex := pageIndex;
  if v_PageIndex > totalPage then
    v_PageIndex := totalPage;
  end if;

  --计算开始的Index和结束的Index
  v_StartIndex := (v_PageIndex - 1) * v_PageSize + 1;
  v_EndIndex   := v_PageIndex * v_PageSize;

  v_SQL := 'SELECT /*+ FIRST_ROWS */* FROM (';
  v_SQL := v_SQL || ' SELECT A.*, ROWNUM RN ';
  v_SQL := v_SQL || ' FROM (' || sqlscript || ') A ';
  v_SQL := v_SQL || ' WHERE ROWNUM <= ' || v_EndIndex;
  v_SQL := v_SQL || ')WHERE RN >= ' || v_StartIndex;

  open v_cur for v_SQL;
end P_QuerySplit;

3、dao中调用

Map b = new HashMap();
		b.put("sqlscript", " users ");
		b.put("pageSize", Integer.parseInt("2"));
		b.put("pageIndex", Integer.parseInt("3"));
		getSqlMapClientTemplate().queryForList("retrieveByLikeCmpRoleC", b);
System.out.println("总记录数："+b.get("totalCount"));
		System.out.println("总页数："+b.get("totalPage"));
		List<Users> listusers =(List<Users>) b.get("v_cur");
		for(Users p:listusers){
			System.out.println(p.getId()+"---"+p.getUserName()+"---"+p.getPassword());
		}

备注：由于oracle的存储过程有2种写法，一种是FUNCTION函数，一种是procedure。他们的返回值的写法不一样，如：

CREATE OR REPLACE PACKAGE REFS_PCK AS
  TYPE REF_CURSOR_T IS REF CURSOR;
  FUNCTION GET_REFS RETURN REF_CURSOR_T;
  procedure getFlowTrack(cur_Scan out REF_CURSOR_T,
                         cur_Send out REF_CURSOR_T);
END REFS_PCK;
/
CREATE OR REPLACE PACKAGE BODY REFS_PCK IS
  FUNCTION GET_REFS RETURN REF_CURSOR_T IS
    L_CURSOR REF_CURSOR_T;
  BEGIN
    OPEN L_CURSOR FOR
      SELECT * FROM USERS;
    RETURN L_CURSOR;
  END GET_REFS;
  procedure getFlowTrack(cur_Scan out REF_CURSOR_T,
                         cur_Send out REF_CURSOR_T) as
  begin
    --1
    open cur_Scan for
      select * from USERS;
    --2
    open cur_Send for
      select * from USERS;
  
  end getFlowTrack;

END REFS_PCK;

那么在xml中的调用，针对函数的是：

<procedure id="getRefs" parameterMap="output">{ ? = call refs_pck.get_refs }</procedure>

针对procedure的是

<procedure id="getRefsaa" parameterMap="outputaa">{ call refs_pck.getFlowTrack(?,?) }</procedure>

即一个有“？”，一个没有。
第二个注意的地方是：oracle存储过程中的参数类型number对应的java中的类型还是java.lang.Integer