分页存储过程

CREATE OR REPLACE PACKAGE utils
IS
TYPE ref_cur IS REF CURSOR;
PROCEDURE sp_page (
p_pagesize IN OUT INTEGER,
p_curPageNo IN OUT INTEGER,
p_sqlSelect IN VARCHAR2,
p_totalPages OUT INTEGER,
p_totalRecords OUT INTEGER,
p_outCursor OUT ref_cur
);
END mgis_utils;

CREATE OR REPLACE PACKAGE BODY utils
IS
PROCEDURE sp_page(
p_pagesize IN OUT INTEGER,
p_curPageNo IN OUT INTEGER,
p_sqlSelect IN VARCHAR2,
p_totalPages OUT INTEGER,
p_totalRecords OUT INTEGER,
p_outCursor OUT ref_cur
)
IS
v_countSql VARCHAR2(1000);
v_startRownum INTEGER;
v_endRownum INTEGER;
v_sql VARCHAR2(4000);
BEGIN
v_countSql := 'SELECT to_char(count(*)) FROM (' || p_sqlSelect || ')';
EXECUTE IMMEDIATE v_countSql INTO p_totalRecords;

-- 验证
IF p_pageSize < 0 THEN
p_pageSize := 10;
END IF;

IF mod(p_totalRecords, p_pageSize) = 0 THEN
p_totalPages := p_totalRecords / p_pageSize;
ELSE
p_totalPages := (p_totalRecords / p_pageSize) + 1;
END IF;

IF p_curPageNo < 1 THEN
p_curPageNo := 1;
END IF;

IF p_curPageNo > p_totalPages THEN
p_curPageNo := p_totalPages;
END IF;

-- 执行
v_startRownum := (p_curPageNo - 1) * p_pageSize + 1;
v_endRownum := p_pageSize * p_curPageNo;
v_sql := 'SELECT * FROM (SELECT ROWNUM rn, a.* FROM (' || p_sqlSelect || ') a WHERE ROWNUM <= ' || v_startRownum || ') b WHERE rn >= ' || v_endRownum;

OPEN p_outCursor FOR v_sql;
END sp_page;
END mgis_utils;