PL/SQL 分页查询

--分页查询
create or replace package testpackage as
type testcursor is ref cursor;
end testpackage;
/

--select * from (select rownum rn,a1.* from (select * from emp)a1 where rownum<=10) where rn>=6;
create or replace procedure zk_pro7(
zk_table in varchar2,
zk_pagesize in number,
zk_pagenow in number,
zk_rownum out number,
zk_rowcount out number,
zk_cursor out testpackage.testcursor)
is
v_sql varchar2(1000);
v_begin number:=(zk_pagenow-1)*zk_pagesize;
v_end number:=zk_pagenow*zk_pagesize;
begin
v_sql:='select * from (select rownum rn,a1.* from (select * from '||zk_table||')a1 where rownum<='||v_end||') where rn>='||v_begin;
open zk_cursor for v_sql;
v_sql:='select connt(*) from '||zk_table;
execute immediate v_sql into zk_rownum;
if mod(zk_rownum,zk_pagesize)=0 then
zk_rowcount := zk_rownum/zk_pagesize;
else 
zk_rowcount:=zk_rownum/zk_pagesize+1;
end if;
end;
/

package com.gentle;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test1 {
    
    private static Connection connection = null;
    private static CallableStatement statement = null;
    private static ResultSet resultSet = null;
    
    public static void main(String[] args) {
        conn();
    }
    
    static void conn() {
        try {
            Class.forName("oralce.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORACLE","scott","tiger");
            statement = connection.prepareCall("{call zk_pro7(?,?,?,?,?,?)}");
            statement.setString(1, "emp");
            statement.setInt(2, 3);
            statement.setInt(3, 1);
            statement.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);
            statement.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);
            statement.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
            statement.execute();
            int rownum = statement.getInt(4);
            int rowcount = statement.getInt(5);
            System.out.println("rownum:" + rownum);
            System.out.println("rowcount:" + rowcount);
            resultSet = (ResultSet) statement.getObject(6);
            
            while(resultSet.next()) {
                resultSet.getString("ename");
                resultSet.getInt("sal");
                resultSet.getDate("hiredate");
                //....
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeDB();
        }
        
    }
    
    static void closeDB() {
        if(connection!=null) {
            try {
                connection.close();
                connection = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement!=null) {
            try {
                statement.close();
                statement = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(resultSet!=null) {
            try {
                resultSet.close();
                resultSet = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}