Oracle 分页存储过程的实现
首先创建一个包，包中包含一个游标，这个游标用于分页存储过程返回记录集。
?
CREATE OR REPLACE PACKAGE P_CURSORAS 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();}}}} 1 楼 ainishigai 2012-03-11 学习了啊