Oracle 存储过程实现分页

--1、首先建立一个包，用户创建一个游标类型

CREATE OR REPLACE PACKAGE PKG_QUERY AS
  TYPE CUR_QUERY IS REF CURSOR;
END PKG_QUERY;

--2、创建存储过程
CREATE OR REPLACE PROCEDURE PRC_QUERY(
P_TABLENAME　　　　IN　VARCHAR2, 　 --表名
P_STRWHERE　　　　 IN　VARCHAR2, 　 --查询条件
P_ORDERCOLUMN　　　IN　VARCHAR2, 　 --排序的列
P_ORDERSTYLE　　　 IN　VARCHAR2, 　 --排序方式
P_CURPAGE　　　　　IN OUT NUMBER, 　 --当前页
P_PAGESIZE　　　　 IN OUT NUMBER, 　 --每页显示记录条数
P_TOTALRECORDS　　 OUT NUMBER, 　　 --总记录数
P_TOTALPAGES　　　 OUT NUMBER, 　　 --总页数
V_CUR　　　　　　　OUT PKG_QUERY.CUR_QUERY) 　 --返回的结果集
 AS
V_SQL VARCHAR2(1000) := ''; 　　　 --sql语句
V_STARTRECORD NUMBER(4); 　　　　 --开始显示的记录条数
V_ENDRECORD NUMBER(4); 　　　　　 --结束显示的记录条数

BEGIN
  --记录中总记录条数
  V_SQL := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || P_TABLENAME ||
           ' WHERE 1=1';

  IF P_STRWHERE IS NOT NULL OR P_STRWHERE <> '' THEN
    V_SQL := V_SQL || P_STRWHERE;
  END IF;

  EXECUTE IMMEDIATE V_SQL
    INTO P_TOTALRECORDS;
  -- 验证页面记录大小 
  IF P_PAGESIZE < 0 THEN
    P_PAGESIZE := 0;
  END IF;
  --根据页大小计算总页数
  IF MOD(P_TOTALRECORDS, P_PAGESIZE) = 0 THEN
    P_TOTALPAGES := P_TOTALRECORDS / P_PAGESIZE;
  
  ELSE
    P_TOTALPAGES := P_TOTALRECORDS / P_PAGESIZE + 1;
  END IF;
  --验证页号
  IF P_CURPAGE < 1 THEN
    P_CURPAGE := 1;
  END IF;
  IF P_CURPAGE > P_TOTALPAGES THEN
    P_CURPAGE := P_TOTALPAGES;
  END IF;
  --实现分页查询
  V_STARTRECORD := (P_CURPAGE – 1) * P_PAGESIZE + 1;
  V_ENDRECORD   := P_CURPAGE * P_PAGESIZE;
  V_SQL         := 'SELECT * FROM (SELECT A.*, rownum r FROM ' ||
                   '(SELECT * FROM ' || P_TABLENAME;
  IF P_STRWHERE IS NOT NULL OR P_STRWHERE <> '' THEN
    V_SQL := V_SQL || ' WHERE 1=1 ' || P_STRWHERE;
  END IF;

  IF P_ORDERCOLUMN IS NOT NULL OR P_ORDERCOLUMN <> '' THEN
    --在Oracle存储过程中实现分页
    V_SQL := V_SQL || ' ORDER BY ' || P_ORDERCOLUMN || ' ' || P_ORDERSTYLE;
  END IF;
  V_SQL := V_SQL || ') A WHERE rownum <= ' || V_ENDRECORD ||
           ') B WHERE r >= ' || V_STARTRECORD;
  DBMS_OUTPUT.PUT_LINE(V_SQL);
  OPEN V_CUR FOR V_SQL;
END PRC_QUERY;

 //JAVA代码里取出结果集
 String sql= “{ call prc_query(?,?,?,?,?,?,?,?,?) }”;
 CallableStatement call = con.prepareCall(sql);
 // ……中间数据设置及注册省略
 call.registerOutParameter(9, OracleTypes.CURSOR);
 // 取出结果集
 (ResultSet) call.getObject(9);