oracle 实现数据分页 ！



oracle 分页函数：

CREATE OR REPLACE PACKAGE pack_pagination AS
TYPE TYRECORD_EMP IS RECORD(
    EMPNO    EMP.EMPNO%TYPE,
    ENAME    EMP.ENAME%TYPE,
    JOB      EMP.JOB%TYPE,
    MGR      EMP.MGR%TYPE,
    HIREDATE EMP.HIREDATE%TYPE,
    SAL      EMP.SAL%TYPE,
    COMM     EMP.COMM%TYPE,
    DEPTNO   EMP.DEPTNO%TYPE);
  TYPE RECORD_EMP IS TABLE OF TYRECORD_EMP;
FUNCTION FUN_PAGINATION(CURRENTPAGE NUMBER, PAGESIZE NUMBER)
    RETURN RECORD_EMP
    PIPELINED ;
END ;
/CREATE OR REPLACE PACKAGE BODY PACK_PAGINATION AS
  FUNCTION FUN_PAGINATION(CURRENTPAGE NUMBER) RETURN RECORD_EMP
    PIPELINED AS
    EMP_ROW  EMP%ROWTYPE;
    REC_EMP  TYRECORD_EMP;
    COUNTNUM NUMBER;
    PAGENUM  NUMBER;
    E_EXP1 EXCEPTION;
    EXP1_STRING VARCHAR2(1000) := '输入页数过大！';
    E_EXP2 EXCEPTION;
    PRAGMA EXCEPTION_INIT(E_EXP2, -06553);
    CURSOR CURSOR_EMP IS
      SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
        FROM (SELECT EMPNO,
                     ENAME,
                     JOB,
                     MGR,
                     HIREDATE,
                     SAL,
                     COMM,
                     DEPTNO,
                     ROWNUM RN
                FROM EMP
               WHERE ROWNUM <= (CURRENTPAGE - 1) * PAGESIZE + PAGESIZE)
       WHERE RN > (CURRENTPAGE - 1) * PAGESIZE
       ORDER BY DEPTNO;
  BEGIN
 
    SELECT COUNT(1) INTO COUNTNUM FROM EMP;
    PAGENUM := COUNTNUM / PAGESIZE;
    IF CURRENTPAGE > PAGENUM THEN
      RAISE E_EXP1;
    ELSE
      OPEN CURSOR_EMP;
      LOOP
        FETCH CURSOR_EMP
          INTO EMP_ROW;
        EXIT WHEN CURSOR_EMP%NOTFOUND;
        REC_EMP.EMPNO    := EMP_ROW.EMPNO;
        REC_EMP.ENAME    := EMP_ROW.ENAME;
        REC_EMP.JOB      := EMP_ROW.JOB;
        REC_EMP.MGR      := EMP_ROW.MGR;
        REC_EMP.HIREDATE := EMP_ROW.HIREDATE;
        REC_EMP.SAL      := EMP_ROW.SAL;
        REC_EMP.COMM     := EMP_ROW.COMM;
        REC_EMP.DEPTNO   := EMP_ROW.DEPTNO;
        PIPE ROW(REC_EMP);
      END LOOP;
    END IF;
    CLOSE CURSOR_EMP;
    RETURN;
  EXCEPTION
    WHEN E_EXP1 THEN
      DBMS_OUTPUT.PUT_LINE(EXP1_STRING || '每页显示' || PAGESIZE || '条, 共计' ||
                           PAGENUM || '页!');
    WHEN E_EXP2 THEN
      DBMS_OUTPUT.PUT_LINE('触发了ORA-06553 错误！' || SQLERRM);
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
  END FUN_PAGINATION;
END;