Oracle存储过程，ibatis兑现分页

Oracle存储过程，ibatis实现分页

1、oracle 分页存储过程脚本

create or replace procedure Pagination(Pindex in integer, --页号? 从1开始
???????????????????????????????????????? Psql?? in varchar2, --查询语句
???????????????????????????????????????? Psize? in integer, --每页分多少行
???????????????????????????????????????? Pcount out number, --共有多少页
???????????????????????????????????????? Result out sys_refcursor --返回数据集
???????????????????????????????????????? ) as

? v_sql?? VARCHAR2(3000);
? v_count number;
? v_Plow? number;
? v_Phei? number;
Begin
? -----------取分页总数 -----------
? v_sql := 'select count(*) from (' || Psql || ')';
? execute immediate v_sql
??? into v_count;
? Pcount := v_count; --ceil(v_count / Psize);
? ---------------显示任意页内容 ---------------
? v_Phei := Pindex * Psize;
? v_Plow := v_Phei - Psize + 1;
? v_sql? := 'select * from (select rownum rn,t.* from (' || Psql ||
??????????? ') t ) where rn between ' || v_Plow || ' and ' || v_Phei ;

? open Result for v_sql;

End Pagination;

2、Ibatis调用oracle存储过程sqlMap文件配置

?<!-- 存储过程参数Map -->
?<parameterMap id="parametermap" >
???? <parameter property="pindex" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
???? <parameter property="psql" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
???? <parameter property="psize" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
???? <parameter property="pcount" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
???? <parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" /> ????
?</parameterMap>
?<!-- 存储过调用 -->
?<procedure id="pagination" resultparameterMap="parametermap">
???? ?{call p_pagination(?,?,?,?,?)}
?</procedure>

3、service层调用sqlMap

?public List<ObjectQurey> pagination(Map param) throws AppException {
??? ??try
??{
???return baseDao.queryForList(ObjectQureySql.PageNation, param);
??}catch (Exception e)
??{
???throw new AppException(THIS_CLASS_NAME, "getAllObjectQureySqlInfo", MODULE_NAME, MessageUtil.BUSSIESS_ERROR, e);
??}
?}