How to call the Oracle paging stored procedure in ibatis

Do you feel a headache when ibatis calls the Oracle paging stored procedure? If this is the case, the following articles will provide you with corresponding solutions. The following articles mainly introduce the operation solution for ibatis to call the Oracle paging stored procedure, the following is a detailed description of the relevant content.

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;  
 
 

Count records

 
 

  
  
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 describes the calling of the Oracle paging stored procedure by ibatis, hoping to help you in this regard.