一、存储过程
--存储过程语句
CREATE OR REPLACE
PROCEDURE PAGE_UTIL(--存储过程分页工具
location IN VARCHAR2,--查询语句
page IN int,--第几页
perPageCount IN int,--每页几条记录
totalPage out SYS_REFCURSOR,--查询出的总页数
pageResultSet out SYS_REFCURSOR--当前页查询出来的结果集
)
AS
pageSql varchar(2000);--查询某页结果的SQL语句
BEGIN
open totalPage for 'select count(1) total from TableName WHERE location = '''||location||'''';--查询出总数
pageSql := 'select * from (select rownum rn,t.* from (SELECT * FROM TableName WHERE location = '''||location||''') t where rownum<='||(page*perPageCount)||') where rn > '||(page-1)||'*'||perPageCount;
open pageResultSet for pageSql;
END PAGE_UTIL;
二、Mybatis配置
<!--在存储过程中使用到游标类型,所有jdbcType为:CURSOR,javaType设置为:ResultSet,resultMap为自己定义的Map-->
<select id="selectLocationPageA" statementType="CALLABLE">
{call PAGE_UTIL(
#{location,mode = IN},
#{page,mode = IN},
#{perPageCount,mode = IN},
#{totalPage,mode = OUT,jdbcType = CURSOR,javaType = ResultSet,resultMap = totalMap},
#{pageResultSet,mode = OUT,jdbcType = CURSOR,javaType = ResultSet,resultMap = queryMap}
)}
</select>
三、详解
(1)存储过程中字符串拼接传入变量,形式为: ”’||要传的值||”’,如上面的location。
(2)对于查询需要返回的结果集,使用游标返回