Java code example for calling the PL/SQL paging Stored Procedure

JavaCallPL/SQL paging Stored ProcedureThe sample code is the content we will introduce in this article. We first give the code of the PL/SQL paging stored procedure, and then define the SQL statements and strings, finally, I introduced the Java calling code. Next let's take a look at this part.

PL/SQL paging stored procedure:

 
 

  
  
Create or replace procedure fenye
  
  
(TableName in varchar2, -- table name
  
  
Page_size in number, -- number of records displayed per page
  
  
PageNow in number, -- current page
  
  
Myrows out number, -- total number of records
  
  
MyPageCount out number, -- total number of pages
  
  
My_cursor out my_new_pack.test_cursor -- returned result set
  
  
) Is
 
 

Define SQL statements and strings:

 
 

  
  
v_sql varchar2(1000);  
  
  
v_begin number := (pageNow-1)*page_size+1;  
  
  
v_end number := pageNow*page_size;  
  
  
begin  
  
  
v_sql :=  'select * from (select t1.*,rownum rn from (select * from '||tableName  
  
  
||')t1 where rownum<='||v_end||' )where rn>='||v_begin;  
  
  
open my_cursor for v_sql;  
  
  
v_sql :='select count(*) from '|| tableName;  
  
  
execute immediate v_sql into myrows;  
  
  
if mod(myrows,page_size) =0 then   
  
  
myPageCount := myrows/page_size;  
  
  
else myPageCount := myrows/page_size+1;  
  
  
end if;  
  
  
end; 
 
 

JAVA call code:

 
 

  
  
Import java. SQL .*;
  
  
Public class test {
  
  
/**
  
  
* @ Param args
  
  
*/
  
  
Public static void main (String [] args ){
  
  
// TODO Auto-generated method stub
  
  
Connection conn = null;
  
  
CallableStatement cs = null;
  
  
ResultSet rs = null;
  
  
Try {
  
  
Class. forName ("oracle. jdbc. driver. OracleDriver ");
  
  
Conn = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: kelvin111G2", "system", "MANAGER ");
  
  
Cs = conn. prepareCall ("{call fenye (?,?,?,?,?,?)} ");
  
  
Cs. setString (1, "scott. emp ");
  
  
Cs. setInt (2, 5 );
  
  
Cs. setInt (3, 2 );
  
  
Cs. registerOutParameter (4, oracle. jdbc. OracleTypes. INTEGER );
  
  
Cs. registerOutParameter (5, oracle. jdbc. OracleTypes. INTEGER );
  
  
Cs. registerOutParameter (6, oracle. jdbc. OracleTypes. CURSOR );
  
  
Cs.exe cute ();
  
  
System. out. println ("Total number of records" + cs. getInt (4 ));
  
  
System. out. println ("total page number" + cs. getInt (5 ));
  
  
Rs = (ResultSet) cs. getObject (6 );
  
  
While (rs. next ()){
  
  
System. out. println (rs. getInt (1) + "=" + rs. getString (2) + "=" + rs. getString (3 ));
  
  
}
  
  
} Catch (Exception e ){
  
  
E. printStackTrace ();
  
  
} Finally {
  
  
Try {
  
  
Rs. close ();
  
  
Cs. close ();
  
  
Conn. close ();
  
  
} Catch (SQLException e ){
  
  
// TODO Auto-generated catch block
  
  
E. printStackTrace ();
  
  
}
  
  
}
  
  
}
  
  
}
 
 

The code example for calling the PL/SQL paging process in Java is described here. I hope this introduction will be helpful to you.