Java調用Oracle的分頁存儲過程

1. 使用PL/SQL寫分頁存儲過程
   

   create or replace procedure pro(
    v_in_table_name in varchar2,--表名
    v_in_page_size in number,--每頁大小
    v_in_page_now in number,--當前頁
    v_out_page_count out number,--頁數
    v_out_row_count out number,--記錄數
    v_out_result_set out pkg.page_cursor--結果集
   ) is
    v_sql_stmt varchar2(2000);
    v_start_index number :=v_in_page_size*(v_in_page_now-1)+1;--起始位置
    v_end_index number :=v_in_page_size*v_in_page_now;--結束位置
   begin
    v_sql_stmt:='select * from (select t.*, rownum rn from (select * from '||v_in_table_name||') t where rownum<='||v_end_index||') where rn>='||v_start_index;
    open v_out_result_set for v_sql_stmt;--打開游標
    v_sql_stmt:='select count(*) from '||v_in_table_name;
    execute immediate v_sql_stmt into v_out_row_count;--查詢出記錄數
    --計算頁數
    if mod(v_out_row_count,v_in_page_size)=0 then
      v_out_page_count:=v_out_row_count/v_in_page_size;
    else
      v_out_page_count:=v_out_row_count/v_in_page_size+1;
    end if;
   end;

   2. 使用Java程序調用該存儲過程
   

   package com.wujilin.procedure;

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

public class ProcedureTest { public static void main(String[] args) { String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; String username = "scott"; String password = "tiger"; String driver = "oracle.jdbc.driver.OracleDriver";

    try {
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, username, password);
        String sql = "{call pro(?,?,?,?,?,?)}";
        CallableStatement cstmt = conn.prepareCall(sql);
        // 設置表名
        cstmt.setString(1, "emp");
        // 設置每一頁的記錄數
        cstmt.setInt(2, 4);
        // 設置當前頁
        cstmt.setInt(3, 2);
        // 注冊總共的頁數
        cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
        // 注冊總共的記錄數
        cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
        // 注冊結果集
        cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
        // 直接執行：execute()方法即可。
        cstmt.execute();
        // 將游標強制轉換成ResultSet類型。
        ResultSet rs = (ResultSet) cstmt.getObject(6);
        int pageCount = cstmt.getInt(4);
        System.out.println("頁數：" + pageCount);
        int rowCount = cstmt.getInt(5);
        System.out.println("記錄數：" + rowCount);
        ResultSetMetaData rsmd = rs.getMetaData();
        while (rs.next()) {
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                System.out.print(rsmd.getColumnLabel(i + 1) + ":" + rs.getObject(i + 1) + ", ");
            }
            System.out.println();
        }
    } catch (Exception e) {

    }
}

}</pre>3. 運行結果
頁數：4
記錄數：14
EMPNO:7654, ENAME:MARTIN, JOB:SALESMAN, MGR:7698, HIREDATE:1981-09-28, SAL:1250, COMM:1400, DEPTNO:30, RN:5, 
EMPNO:7698, ENAME:BLAKE, JOB:MANAGER, MGR:7839, HIREDATE:1981-05-01, SAL:2850, COMM:null, DEPTNO:30, RN:6, 
EMPNO:7782, ENAME:CLARK, JOB:MANAGER, MGR:7839, HIREDATE:1981-06-09, SAL:26950, COMM:null, DEPTNO:10, RN:7, 
EMPNO:7788, ENAME:SCOTT, JOB:ANALYST, MGR:7566, HIREDATE:1987-04-19, SAL:3000, COMM:null, DEPTNO:20, RN:8,