Oracle預存程序實現分頁

--book表
CREATE TABLE book(
book_id VARCHAR2(20),
book_name VARCHAR2(100),
book_publish VARCHAR2(100)
);

--in表示輸入參數，預設為in
--out 表示輸出參數
CREATE OR REPLACE PROCEDURE sp_pro7(spBookId IN VARCHAR2, spBookName in VARCHAR2, spBookPub VARCHAR2)
 IS
 BEGIN
   INSERT INTO book VALUES(spBookId, spBookName,spBookPub);
 END;

--返回單個值
CREATE OR REPLACE PROCEDURE sp_pro8(spBookId IN VARCHAR2, spBookName OUT VARCHAR2)
 IS
 BEGIN
   SELECT t.book_name INTO spBookName FROM book t WHERE t.book_id = spBookId;
 END;

--返回結果集
--1 建立一個遊標
CREATE OR REPLACE PACKAGE testPackage AS
 TYPE test_cursor IS REF CURSOR;
END testPackage;
--2 建立過程
CREATE OR REPLACE PROCEDURE sp_pro9(spBookId VARCHAR2, sp_cursor OUT testPackage.test_cursor) IS
 BEGIN
   OPEN sp_cursor FOR SELECT t.book_id,t.book_name,t.book_publish FROM book t;
 END;
 
--分頁
SELECT *
  FROM (SELECT ROWNUM RN, T.* FROM BOOK T WHERE ROWNUM < 5)
 WHERE RN > 2;

CREATE OR REPLACE PROCEDURE fenye(
       tablename VARCHAR2,
       pageSize NUMBER,  -- 一頁顯示的記錄數
       pageNow NUMBER, --當前頁數
       myRows OUT NUMBER, --總記錄數
       myPageCount OUT NUMBER,
       sp_cursor OUT testPackage.test_cursor) IS
v_sql VARCHAR2(1000);
v_begin NUMBER:=(pageNow - 1) * pageSize + 1;
v_end NUMBER:=(pageNow) * pageSize;
BEGIN
    v_sql:='SELECT * FROM (SELECT ROWNUM RN, T.* FROM '|| tablename || ' T WHERE ROWNUM < '|| v_end || ') WHERE RN > '|| v_begin;
    OPEN sp_cursor FOR v_sql;
    --計算myrows mypagecount
    v_sql := 'SELECT COUNT(*) FROM '|| tablename;
    --快速執行sql,並將結果賦值給myrows
    EXECUTE IMMEDIATE v_sql INTO myrows;
    IF MOD(myrows, pageSize) = 0 THEN
      myPageCount:=myrows / pageSize;
    ELSE
      myPageCount:=myrows / pageSize + 1;
    END IF;
    --關閉遊標
    CLOSE sp_cursor;
END;

 

 

 

package com.leeket;

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

/**
 * @author Administrator
 * @description 分頁
 */
public class TestProcedure4 {

 public static void main(String[] args) {

  CallableStatement call = null;
  Connection conn = null;
  ResultSet rs = null;
  try {

   Class.forName("Oracle.jdbc.driver.OracleDriver");
   conn = DriverManager
     .getConnection("jdbc:oracle:thin:pas_perf/pas_perf@localhost:1521:orcl");
   call = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");
   call.setString(1, "BOOK");
   call.setInt(2, 10);
   call.setInt(3, 1);
   call.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
   call.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
   call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
   call.execute();
   //取出傳回值 要注意問好的順序
   int num = call.getInt(4);
   int myPageCount = call.getInt(5);
   rs = (ResultSet)call.getObject(6);
   if (rs != null)
    while (rs.next()) {
     System.out.println(num + "\t" + myPageCount + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4));
    }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (rs != null)
     rs.close();
    if (call != null)
     call.close();
    if (conn != null)
     conn.close();
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
 }
}