CREATE OR REPLACE PROCEDURE dP_SelPageListOUTList(
inPageNum IN INT,
inPageSize IN INT,
inSQLQuery IN VARCHAR2,
outTotalCount OUT INT,
outResultSet OUT CURSOR
)
AS
vStartIndex INT; --起始索引
vEndIndex INT; --结束索引
vDynamicSQL VARCHAR2(1000); --最终分页sql
BEGIN
-- 获取总记录数(只在第一页查询时获取)
DBMS_OUTPUT.ENABLE();
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || inSQLQuery || ')' INTO outTotalCount;
DBMS_OUTPUT.PUT_LINE(outTotalCount);
IF inPageNum <= 0
THEN
SET inPageNum=0;
END IF;
-- 页面大小超过总记录数
IF inPageSize>=outTotalCount
THEN
SET inPageNum= 0;
SET inPageSize =outTotalCount;
END IF;
-- 页码超过总记录数
IF (inPageNum + inPageSize) > outTotalCount
THEN
SET inPageNum = 0;
END IF;
-- 计算起始索引和结束索引
--如果利用OFFSET 偏移量分页
vStartIndex := (inPageNum - 1)* inPageSize ;
--如果用ROWNUM 分页
-- vStartIndex :=(inPageNum - 1)* inPageSize+1;
vEndIndex := vStartIndex + inPageSize - 1;
DBMS_OUTPUT.PUT_LINE(vStartIndex);
DBMS_OUTPUT.PUT_LINE(vEndIndex);
--1、 使用ROWNUM进行分页,构建动态 SQL 查询语句
/* vDynamicSQL := 'SELECT * FROM (
SELECT ROWNUM AS RowNumIndex, t.* FROM (' || inSQLQuery || ') t
) WHERE RowNumIndex BETWEEN :startIdx AND :endIdx';*/
--2、使用OFFSET 和 FETCH NEXT 关键字 进行分页查询。 通过合理地设置偏移量和每页显示的记录数,我们可以高效地获取查询结果集的指定页数据。
vDynamicSQL := 'SELECT * FROM (
' || inSQLQuery || ') t
OFFSET :startIdx ROWS
FETCH NEXT :endIdx ROWS ONLY';
DBMS_OUTPUT.PUT_LINE(vDynamicSQL);
-- 执行动态 SQL 查询
--如果利用OFFSET 偏移量分页
OPEN outResultSet FOR vDynamicSQL USING vStartIndex, inPageSize;
--如果用ROWNUM 分页
--OPEN outResultSet FOR vDynamicSQL USING vStartIndex, vEndIndex;
END;
ROWNUM分页测试说明
多表关联sql测试
17384条数据,每页15条,第16页,基本在0.004秒左右。
84521条数据,每页15条,第40页,基本统一到了0.017秒左右。
单表测试8673527,
每页15条,第10000页,耗时:0.008秒
每页15条,第578236页(最后一页);耗时 0.4秒左右。
经测试:从20万页开始(0.1秒)-57万页,每增加10-15万页会增加0.1秒