oracle表分页,针对任何表写的纯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; /