Oracle paging stored procedures)

Create or replace package pkg_tools
Is
Type resultdata is ref cursor;
Procedure sp_page (p_pagesize int, -- number of records per page
P_pageno int, -- current page number, starting from 1
P_sqlselect varchar2, -- query statement, including sorting part
P_sqlcount varchar2, -- query statement for obtaining the total number of records
P_outrecordcount out int, -- total number of returned records
P_outcursor out resultdata );


End pkg_tools;
/

Create or replace package body pkg_tools
Is
Procedure sp_page (p_pagesize int, -- number of records per page
P_pageno int, -- current page number, starting from 1
P_sqlselect varchar2, -- query statement, including sorting part
P_sqlcount varchar2, -- query statement for obtaining the total number of records
P_outrecordcount out int, -- total number of returned records
P_outcursor out resultdata)
As
V_ SQL varchar2 (3000 );
V_count int;
V_heirownum int;
V_lowrownum int;
Begin
---- Retrieve the total number of records
Execute immediate p_sqlcount into v_count;
P_outrecordcount: = v_count;
---- Query by PAGE
V_heirownum: = p_pageno * p_pagesize;
V_lowrownum: = v_heirownum-p_pagesize + 1;

V_ SQL: = 'select *
From (
Select a. *, rownum Rn
From ('| p_sqlselect |')
Where rownum <= '| to_char (v_heirownum) |'
) B
Where rn> = '| to_char (v_lowrownum );
-- Pay attention to the use of the rownum alias. For the first time, use rownum directly. For the second time, use the alias RN.

Open p_outcursor for v_ SQL;
 
End sp_page;
 
End pkg_tools;
/