1.創建表
SQL> create table t1( id number);

表已創建。

SQL> commit;

提交完成。

2.插入數據
insert into t1 values(1)
...
insert into t1 values(10)

3.創建程序包
create or replace package pkg_query as
    type cur_query is ref cursor;
end pkg_query;

4.創建存儲過程
create OR REPLACE PROCEDURE prc_query(
    p_tableName in varchar2,--表名
    p_strwhere in varchar2,--查詢條件
    p_orderColumn in varchar2,--排序的列
    p_orderStyle in varchar2,--排序方式
    p_curPage in out Number,--當前頁
    p_pageSize in out Number,--每頁顯示記錄條數
    p_totalRecords out Number,--總記錄數
    p_totalPages out Number,--總頁數
    v_cur out pkg_query.cur_query)--返回的結果集
IS
    v_sql VARchar2(1000) := '';--sql語句
    v_startRecord Number(4);--開始顯示的記錄條數
    v_endRecord Number(4);--結束顯示的記錄條數
BEGIN
    --記錄中總記錄條數
    v_sql := ' select TO_NUMBER(count(*)) FROM  '  || p_tableName ||  ' where 1=1 ';
    IF p_strwhere IS NOT NULL or p_strwhere <> '' THEN
        v_sql := v_sql || p_strwhere;
    END IF;
    execute IMMEDIATE v_sql INTO p_totalRecords;
  
    --驗證頁面記錄大小
    IF p_pageSize < 0 THEN
    p_pageSize := 0;
    END IF;
  
    --根據頁大小計算總頁數
    IF MOD(p_totalRecords,p_pageSize) = 0 THEN
        p_totalPages := p_totalRecords / p_pageSize;
    ELSE
        p_totalPages := p_totalRecords / p_pageSize + 1;
    END IF;
  
    --驗證頁號
    IF p_curPage < 1 THEN
        p_curPage := 1;
    END IF;

    IF p_curPage > p_totalPages THEN
        p_curPage := p_totalPages;
    END IF;
  
    --實現分頁查詢
    v_startRecord := (p_curPage - 1) * p_pageSize+1;
    v_endRecord := p_curPage * p_pageSize;      
    v_sql := ' select * FROM (select A.*, rownum r FROM  ' || ' (select * FROM ' || p_tableName;

    IF p_strwhere IS NOT NULL or p_strwhere <> '' THEN
        v_sql := v_sql || ' where 1=1 ' || p_strwhere;
    END IF;
    IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN
        v_sql := v_sql || ' ORDER BY  ' || p_orderColumn || ' ' || p_orderStyle;
    END IF;

    v_sql := v_sql || ' ) A where rownum <= '  || v_endRecord || ' ) B where r >=  ' || v_startRecord;
        OPEN v_cur FOR v_sql;
END prc_query;
/

5.創建Java-JDBC
package page_return_list;

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

import oracle.jdbc.OracleTypes;

public class Search {

 /**
  * author david
  * @param args
  */
 public static void main(String[] args) {

  String driver = "oracle.jdbc.driver.OracleDriver";
  String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:myorcl";
  Statement stmt = null;
  ResultSet rs = null;
  Connection conn = null;

  String p_tableName = "t1";
  String p_strwhere = "";
  String p_orderColumn = "id";
  String p_orderStyle = "asc";
  Integer p_curPage = 10;  //負數時能正確返回首頁數據, 但超過總頁數時, 卻不能返回未頁數據, 有待修正pl/sql語句
  Integer p_pageSize = 3;
  Integer p_totalRecords = 0;
  Integer p_totalPages = 0;

  try {
   Class.forName(driver);
   conn = DriverManager.getConnection(strUrl, "scott", "tiger");
   CallableStatement proc = null;

   String sql = "{ call prc_query(?,?,?,?,?,?,?,?,?) }";
   proc = conn.prepareCall(sql);

   proc.setString(1, p_tableName);
   proc.setString(2, p_strwhere);
   proc.setString(3, p_orderColumn);
   proc.setString(4, p_orderStyle);
   proc.setInt(5, p_curPage);
   proc.setInt(6, p_pageSize);

   proc.registerOutParameter(5, java.sql.Types.INTEGER);
   proc.registerOutParameter(6, java.sql.Types.INTEGER);
   proc.registerOutParameter(7, java.sql.Types.INTEGER);
   proc.registerOutParameter(8, java.sql.Types.INTEGER);
   proc.registerOutParameter(9, OracleTypes.CURSOR);

   proc.execute();

   p_curPage = proc.getInt(5);
   p_pageSize = proc.getInt(6);
   p_totalRecords = proc.getInt(7);
   p_totalPages = proc.getInt(8);
   System.out.println("p_totalRecords: " + p_totalRecords + " , "
     + "p_totalPages: " + p_totalPages + ", p_curPage: "
     + p_curPage + ", p_pageSize: " + p_pageSize);

   //獲得游標
   proc.registerOutParameter(9, OracleTypes.CURSOR);

   rs = (ResultSet) proc.getObject(9);
   while (rs.next()) {
    System.out.println(rs.getInt(1));
   }
  } catch (SQLException ex2) {
   ex2.printStackTrace();
  } catch (Exception ex2) {
   ex2.printStackTrace();
  } finally {
   try {
    if (rs != null) {
     rs.close();
     if (stmt != null) {
      stmt.close();
     }
     if (conn != null) {
      conn.close();
     }
    }
   } catch (SQLException ex1) {
   }
  }
 }
}