General paging stored procedures for large data volumes in Oracle

Type refcursortype is ref cursor; -- cursor type definition, used to return a dataset

/*************************************** **************************
* Function Description: Common paging stored procedures for large data volumes
* Created by: Xia chuntao xchuntao@163.com QQ: 23106676
* Creation Time:
**************************************** *************************/
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_outrecordcount out int, -- total number of returned records
P_outcursor out refcursortype)
As
V_ SQL varchar2 (3000 );
V_count int;
V_heirownum int;
V_lowrownum int;
Begin
---- Retrieve the total number of records
V_ SQL: = 'select count (*) from ('| p_sqlselect | ')';
Execute immediate v_ SQL 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;

EndSp_page;

/*************************************** **************************
* Function Description: Common paging stored procedures for large data volumes (heavy load 1, recommended)
* Creator: Xia chuntao
* Creation Time:
**************************************** *************************/
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 refcursortype)
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;

EndSp_page;

/*************************************** **************************
* Function Description: Paging common stored procedures for large data volumes (heavy load 2)
* Creator: Xia chuntao
* Creation Time:
**************************************** *************************/
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_outcursor out refcursortype)
As
V_ SQL varchar2 (3000 );
-- V_count int;
V_heirownum int;
V_lowrownum int;
Begin
/*
---- Retrieve the total number of records
V_ SQL: = 'select count (*) from ('| p_sqlselect | ')';
Execute immediate v_ SQL 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;

EndSp_page;