针对任何表写的纯Oracle分页

CREATE OR REPLACE PACKAGE
       APP_OPERATOR
       IS
         TYPE TABLE_REFCUR IS REF CURSOR;
         PROCEDURE PAGINATION(TABLE_NAME VARCHAR2,PAGE_SIZE NUMBER,CURRENT_PAGE NUMBER,DATA_LIST OUT TABLE_REFCUR,IMFOR OUT VARCHAR2,PUBLICCOLUMN VARCHAR2);   
         --TABLE_NAME VARCHAR2   表名
         --,PAGE_SIZE NUMBER,    每页条数
        -- CURRENT_PAGE NUMBER,    当前页
         --DATA_LIST OUT TABLE_REFCUR,   返回数据集
         --IMFOR OUT VARCHAR2,      系统信息
         --PUBLICCOLUMN VARCHAR2       主键
       END;
/

CREATE OR REPLACE PACKAGE BODY
       APP_OPERATOR
       IS
         PROCEDURE PAGINATION(TABLE_NAME VARCHAR2,PAGE_SIZE NUMBER,CURRENT_PAGE NUMBER,DATA_LIST OUT TABLE_REFCUR,IMFOR OUT VARCHAR2,PUBLICCOLUMN VARCHAR2)
         IS
         COU NUMBER(2);--内部存储表的记录数
         MAX_PAGE NUMBER(10);--存储表所分页的最大页数
         MAX_SIZE NUMBER(10);--每页所能分的最大条数
         DEFUALT_PAGE_SIZE NUMBER(2):=5;--默认每页条数
         DEFUALT_CURRENT_PAGE NUMBER(2):=1;--默认读取页数
         DINAMICSQL VARCHAR2(200);--动态SQl存储
         BEGIN
              --验证表的存在
              DINAMICSQL:='SELECT COUNT(*) FROM TAB WHERE TNAME=:1';
              EXECUTE IMMEDIATE DINAMICSQL INTO COU USING TABLE_NAME ;
              IF COU!=1 THEN
              RAISE_APPLICATION_ERROR(-20001,'表不存在!!!');
              END IF;
             
              --获得每页条数
              DINAMICSQL:='SELECT COUNT(*) FROM '||TABLE_NAME;
              EXECUTE IMMEDIATE DINAMICSQL INTO MAX_SIZE;
              IF PAGE_SIZE>=1 AND PAGE_SIZE<=MAX_SIZE THEN
              DEFUALT_PAGE_SIZE:=PAGE_SIZE;
              ELSE
              IMFOR:='INPUT PAGE_SIZE ILLEGE!!!  DEFUALT PAGE_SIZE=5';
              END IF;
             
              --获得总页数
              DINAMICSQL:='SELECT COUNT(*)/'||DEFUALT_PAGE_SIZE||' FROM '||TABLE_NAME;
              EXECUTE IMMEDIATE DINAMICSQL INTO MAX_PAGE;
              IF MAX_PAGE>TRUNC(MAX_PAGE) THEN
              MAX_PAGE:=TRUNC(MAX_PAGE)+1;
              END IF;
             
              IF CURRENT_PAGE>=1 AND CURRENT_PAGE<=MAX_PAGE THEN
               DEFUALT_CURRENT_PAGE:=CURRENT_PAGE;
              ELSE
              IMFOR:=IMFOR||'------'||'INPUT CURRENT_PAGE ILLEGE!!!  DEFUALT CURRENT_PAGE=1';
              END IF;
              DINAMICSQL:='SELECT B.* FROM (SELECT * FROM (SELECT ROWNUM NUM,'||TABLE_NAME||'.* FROM '||TABLE_NAME||') WHERE NUM > ('||DEFUALT_CURRENT_PAGE||'-1)*'||DEFUALT_PAGE_SIZE||' AND NUM<='||DEFUALT_CURRENT_PAGE||'*'||DEFUALT_PAGE_SIZE ||')A,'||TABLE_NAME ||' B WHERE A.'||PUBLICCOLUMN||'=B.'||PUBLICCOLUMN;
              OPEN DATA_LIST FOR DINAMICSQL;
         END;
       END;
/


DECLARE
TAABLE_NAME VARCHAR2(20):='EMP';
DINAMICSQL VARCHAR2(200);
CUR APP_OPERATOR.TABLE_REFCUR;
IMFOR VARCHAR2(40);
PUBLICCOLUMN VARCHAR2(20):='EMPNO';
ROWDATA EMP%ROWTYPE;
BEGIN
     APP_OPERATOR.PAGINATION(TAABLE_NAME,3,4,CUR,IMFOR,PUBLICCOLUMN);
     DBMS_OUTPUT.PUT_LINE(IMFOR);
     LOOP
     FETCH CUR INTO ROWDATA;
     EXIT WHEN CUR%NOTFOUND;
     DBMS_OUTPUT.put_line(ROWDATA.ENAME);
     END LOOP;
END;
/