首先创建一个包,包中包含一个游标,这个游标用于分页存储过程返回记录集。
CREATE OR REPLACE PACKAGE P_CURSOR
AS
TYPE PAGE_CURSOR IS REF CURSOR;
END P_CURSOR;
创建分页存储过程:
CREATE OR REPLACE PROCEDURE PAGINATION(
V_TABLENAME VARCHAR2,
V_PAGENOW IN NUMBER,
V_PAGESIZE IN NUMBER,
V_ROWCOUNT OUT NUMBER,
V_PAGECOUNT OUT NUMBER,
RS_CURSOR OUT P_CURSOR.PAGE_CURSOR)
IS
V_SQL VARCHAR2(1000);
V_ROWFROM NUMBER;
V_ROWTO NUMBER;
BEGIN
V_ROWFROM := (V_PAGENOW - 1) * V_PAGESIZE + 1;
V_ROWTO := V_PAGENOW * V_PAGESIZE;
V_SQL := 'SELECT * FROM (SELECT T.*,ROWNUM RN FROM (SELECT ROWNUM,EMP.* FROM '|| V_TABLENAME ||') T WHERE ROWNUM <=' || V_ROWTO || ') WHERE RN >=' || V_ROWFROM;
OPEN RS_CURSOR FOR V_SQL;
--CLOSE RS_CURSOR;
V_SQL := 'SELECT COUNT(*) FROM ' || V_TABLENAME;
EXECUTE IMMEDIATE V_SQL INTO V_ROWCOUNT;
IF MOD(V_ROWCOUNT, V_PAGESIZE) = 0 THEN
V_PAGECOUNT := V_ROWCOUNT/V_PAGESIZE;
ELSE
V_PAGECOUNT := V_ROWCOUNT/V_PAGESIZE + 1;
END IF;
END;
存储过程中打开的游标并没有关闭,原因是该存储过程向外输出游标,如果关闭,会导致JAVA端获取不到游标。
当JAVA程序调用存储过程后,该游标自动关闭。
创建用于测试的JAVA程序:
package com.cool.Exercises;
import java.sql.*;
public class Pagination {
public static void main(String args[]){
Connection ct = null;
CallableStatement cs = null;
ResultSet rs = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:COOL", "scott", "tiger");
cs = ct.prepareCall("{call PAGINATION(?,?,?,?,?,?)}");
cs.setString(1, "EMP");
cs.setInt(2, 1);
cs.setInt(3, 10);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
int rowCount = cs.getInt(4);
int pageCount = cs.getInt(5);
rs = (ResultSet)cs.getObject(6);
System.out.println(" rowCount = " + rowCount);
System.out.println(" pageCount = " + pageCount);
while(rs.next()){
System.out.print(" " + rs.getInt(1));
System.out.print(" " + rs.getString(2));
System.out.print(" " + rs.getString(3));
System.out.print(" " + rs.getString(4));
System.out.print(" " + rs.getString(5));
System.out.print(" " + rs.getString(6));
System.out.print(" " + rs.getString(7));
System.out.println(" " + rs.getString(8));
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs != null){
rs.close();
}
if(cs != null){
cs.close();
}
if(ct != null){
ct.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}