Calling of the Oracle paging stored procedure is in ibatis

The following articles mainly describe the actual steps for calling the Oracle paging Stored Procedure correctly in ibatis, and the items in the actual operations are worth noting, the following describes the main content of the text, hoping to help you in this aspect.

Oracle paging:

Create or replace procedure P_QuerySplit (

Sqlscript varchar2, table name/SQL statement

PageSize integer, number of records per page

PageIndex integer, current page

TotalCount out number, total number of records

TotalPage out number, total number of pages

V_cur out sys_refcursor returns the cursor

 
 

  
  
) is  
  
  
/**  
  
  
* by chenjianxin 2008-5-3  
  
  
*  
  
  
*/  
  
  
v_PageSize number;  
  
  
v_PageIndex number;  
  
  
v_SQL_Count varchar2(4000);  
  
  
v_SQL varchar2(4000);  
  
  
v_StartIndex number;  
  
  
v_EndIndex number;  
  
  
begin  
  
  
v_PageSize:=pageSize;  
  
  
if v_PageSize=0 then  
  
  
v_PageSize:=1;  
  
  
end if;  
 
 

Ibatis requires statistics on the number of records when calling the Oracle paging storage process

 
 

  
  
v_SQL_Count := 'select count(*) from (' ? ? sqlscript ? ?') a ';  
  
  
execute immediate v_SQL_Count into totalCount;  
 
 

Total number of pages

 
 

  
  
totalPage:=CEIL(totalCount/v_PageSize); 
 
 

Verify the page number. If the page number is larger than the maximum number of pages, return to the last page.

 
 

  
  
v_PageIndex:=pageIndex;  
  
  
if v_PageIndex>totalPage then  
  
  
v_PageIndex:=totalPage;  
  
  
end if;  
 
 

Calculate the start Index and end Index

 
 

  
  
v_StartIndex:=(v_PageIndex-1)*v_PageSize 1;  
  
  
v_EndIndex:=v_PageIndex*v_PageSize;  
  
  
v_SQL:='SELECT /* FIRST_ROWS */* FROM (';  
  
  
v_SQLv_SQL:=v_SQL ? ?' SELECT A.*, ROWNUM RN ';  
  
  
v_SQLv_SQL:=v_SQL ? ?' FROM (' ? ?sqlscript ? ?') A ';  
  
  
v_SQLv_SQL:=v_SQL ? ?' WHERE ROWNUM <= ' ? ?v_EndIndex;  
  
  
v_SQLv_SQL:=v_SQL ? ?')WHERE RN >= ' ? ?v_StartIndex;  
  
  
open v_cur for v_SQL;  
  
  
end P_QuerySplit;   
 
 

The above content is an introduction to the calling of the Oracle paging Stored Procedure for ibatis. I hope you will get some benefits.