oracle分页

--创建包  获取数据结果集
CREATE OR REPLACE PACKAGE getDatas_PACKAGE AS
  TYPE getDatas_cursor IS REF CURSOR;
END getDatas_PACKAGE;

--创建分页存储过程
  CREATE OR REPLACE PROCEDURE FENYE(TABLENAME    IN VARCHAR2,
                                    PAGSIZE      IN NUMBER,
                                    PAGENOW      IN NUMBER,
                                    MYROWS       OUT NUMBER,
                                    PAGECOUNT    OUT NUMBER,
                                    FENYE_CURSOR OUT getDatas_PACKAGE.getDatas_cursor) IS

  V_SQL   VARCHAR2(1000);
  V_BEGIN NUMBER := (PAGENOW - 1) * PAGSIZE + 1;
  V_END   NUMBER := PAGENOW * PAGSIZE;

BEGIN
  V_SQL := 'SELECT *
  FROM (SELECT T1.*, ROWNUM RN
          FROM (SELECT * FROM ' || TABLENAME ||
           ' order by sal asc) T1
         WHERE ROWNUM <= ' || V_END || ')
 WHERE RN >= ' || V_BEGIN;

  OPEN FENYE_CURSOR FOR V_SQL;

  V_SQL := 'SELECT COUNT(*) FROM ' || TABLENAME;

  -- execute immediate v_sql into MYROWS;
  EXECUTE IMMEDIATE V_SQL
    INTO MYROWS;

  IF MOD(MYROWS, PAGSIZE) = 0 THEN
    PAGECOUNT := MYROWS / PAGSIZE;
  
  ELSE
    PAGECOUNT := MYROWS / PAGSIZE + 1;
  END IF;
 -- END LOOP;
 --CLOSE FENYE_CURSOR;

END;