Faster calling of Oracle paging stored procedures

We all know that ASP. NET to call the Oracle paging storage process should be combined with the ASPnetpager paging control to achieve automatic paging function. Before, we used the pagination function provided by the GridView, the speed is really not good. it is decided that the custom paging Oracle database has more than pieces of data, and AspnetPager is doing well.

Oracle paging stored procedure:

Create or replace package JT_P_page is

Type type_cur is ref cursor; defines the cursor variable used to return the record set

Procedure Pagination (Pindex in number, index of the number of pages to be displayed, starting from 0

Psql in varchar2, which generates query statements for paging data

Psize in number, number of records per page

Pcount out number, number of returned pages

Prowcount out number, number of returned records

V_cur out type_cur returns the cursor of the paging data

);

End JT_P_page;

Define the package subject

Create or replace package body JT_P_page is

Procedure Pagination (Pindex in number, index of the number of pages to be displayed, starting from 0

Psql in varchar2, which generates query statements for paging data

Psize in number, number of records per page

Pcount out number, number of returned pages

Prowcount out number, number of returned records

V_cur out type_cur returns the cursor of the paging data

 
 

  
  
) AS  
  
  
v_sql VARCHAR2(1000);  
  
  
v_Pbegin number;  
  
  
v_Pend number;  
  
  
begin  
  
  
v_sql := 'select count(*) from (' || Psql || ')';  
  
  
execute immediate v_sql into Prowcount;  
 
 

Calculate the total number of records

 
 

  
  
Pcount := ceil(Prowcount / Psize);  
 
 

Calculates the total number of pages and displays any page content.

 
 

  
  
v_Pend := Pindex * Psize + Psize;  
  
  
v_Pbegin := v_Pend - Psize + 1;   
  
  
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Pbegin || ' and ' || v_Pend;   
  
  
open v_cur for v_sql;  
  
  
end Pagination;   
  
  
end JT_P_page; 
 
 

This is found on the Internet, but it is not bad. It is mainly based on the pseudo column rownum as the where query condition for handsome selection. Through this Oracle paging, we have a preliminary understanding of rownum, in practice, with the Oracle paging Stored Procedure cut down, it is called.