java调用oracle数据库的分页存储过程

港控/mmm° 2022-06-09 04:11 189阅读 0赞

package procedure;

import java.sql.\*;

/\*\*  
 \* 测试 java调用oracle数据库的分页存储过程  
 \* @author sweetanan  
 \*  
 \*/  
public class TestProcedure \{

/\*\*  
  \* @param args  
  \* @throws ClassNotFoundException  
  \*/  
 public static void main(String\[\] args) throws Exception \{  
  /\*  
   --1、创建含有游标的包  
   create or replace package pac1 as  
   type fy\_cursor is ref cursor;  
   end pac1;  
   --2、开始分页存储过程  
   create or replace procedure fengye(tableName in varchar2, condition in varchar2, startPage in number, pageNum in number, totPage out number, totRecord out number, res out pac1.fy\_cursor) is  
   v\_startNum number := (startPage - 1) \* pageNum;  
   v\_endNum number := startPage \* pageNum + 1;  
   v\_sqlStr varchar2(1000);  
   begin  
   v\_sqlStr := 'select count(\*) from '||tableName;  
   execute immediate v\_sqlStr into totRecord;  
   if mod(totRecord,pageNum) = 0 then  
     totPage := totRecord / pageNum;  
   else  
     totPage := totRecord / pageNum + 1;  
   end if;  
   v\_sqlStr := 'select \* from (select t.\*, rownum rn from (select \* from '||tableName||' where 1 = 1 and '||condition||')t where rownum < '||v\_endNum||') t2 where t2.rn > '||v\_startNum;  
   open res for v\_sqlStr;  
   --close res;  
   end;  
   /  
   \*/  
  Class.forName("oracle.jdbc.driver.OracleDriver");  
  String url = "jdbc:oracle:thin:@127.0.0.1:1521:myora";  
  String userName = "scott";  
  String password = "m123";  
  Connection con = DriverManager.getConnection(url, userName, password);  
  CallableStatement cs = con.prepareCall("\{call fengye(?, ?, ?, ?, ?, ?, ?)\}");  
    
  cs.setString(1, "emp");  
  cs.setString(2, "2 = 2");  
  cs.setInt(3, 3);  
  cs.setInt(4, 5);

cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);  
  cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);  
  cs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);  
    
  cs.execute();  
    
  int totPage = cs.getInt(5);  
  int totRecord = cs.getInt(6);  
  ResultSet rs = (ResultSet)cs.getObject(7);  
    
  System.out.println("totPage:" + totPage + " totRecord:" + totRecord);  
  while(rs.next())  
  \{  
   System.out.println("编号：" + rs.getInt(1) + "姓名：" + rs.getString(2));  
  \}  
 \}