How to create an efficient Oracle paging stored procedure

Create or Replace package p_page is

--Author:pharaohs

--Created:2006-4-30 14:14:14

--Purpose: Paging process

TYPE Type_cur is REF CURSOR; --Define a cursor variable to return the recordset

PROCEDURE Pagination (

Pindex in number--Paging index

Psql in VARCHAR2--SQL statements that produce a dataset

Psize in number--page size

Pcount out number--returns total paging

V_cur out Type_cur--Returns the current page data record

）；

Procedure Pagerecordscount (

Psqlcount in VARCHAR2--SQL statements that produce a dataset

Prcount out number--returns total records

）；

End P_page;

/

Create or Replace package body P_page is

PROCEDURE Pagination (

Pindex in number,

Psql in Varchar2,

Psize in number,

Pcount out number,

V_cur out Type_cur

）

As

V_sql VARCHAR2 (1000);

V_count number;

V_plow number;

V_phei number;

Begin

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Total------------------------------------------------------------Paging

V_sql: = ' SELECT count (*) from (' | | Psql | | '）';

Execute immediate v_sql into V_count;

Pcount: = Ceil (v_count/psize);

------------------------------------------------------------Display any page content

V_phei: = pindex * psize + psize;

V_plow: = v_phei-psize + 1;

--psql: = ' Select RowNum rn,t.* from Zzda t '; --Requires that rownum fields be included

V_sql: = ' select * FROM (' | | Psql | | ') where rn between ' | | V_plow | | ' AND ' | | V_phei;

Open v_cur for V_sql;

end pagination;

--**************************************************************************************

Procedure Pagerecordscount (

Psqlcount in Varchar2,

Prcount out number

）

As

V_sql VARCHAR2 (1000);

V_prcount number;

Begin

V_sql: = ' SELECT count (*) from (' | | Psqlcount | | '）';

Execute immediate v_sql into V_prcount;

Prcount: = V_prcount; --Returns the total number of records

End Pagerecordscount;

--**************************************************************************************

End P_page;

/