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 学习了啊
哥们儿你太好了 下次能发表多表插入的动态语句吗 谢谢
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html