oracle高性能分页,ORACLE高效分页存储过程代码

ORACLE高效分页存储过程代码

create or replace procedure P_Pagination

(

Pindex in number,       --页号  从1开始

Psql in varchar2,       --查询语句

Psize in number,        --每页分多少行

Pcount out number,      --共有多少页

v_cur out Sys_Refcursor --返回数据集

)

AS

v_sql VARCHAR2(1000);

v_count number;

v_Plow number;

v_Phei number;

Begin

-----------取分页总数 -----------

v_sql := 'select count(*) from (' || Psql || ')';

execute immediate v_sql into v_count;

Pcount := 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 v_cur for v_sql;

End P_Pagination;