自己编写 Oracle 分页函数

CREATE OR REPLACE PACKAGE PACK_PAGINATION AS
  PAGESIZE CONSTANT NUMBER := 2;
  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) 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;

 ----代码优化 后

--以上代码的优化 ；
CREATE OR REPLACE PACKAGE  SPILE_PAGE AS 

  PAGESIZE NUMBER := 5;
  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);
  REC_EMP TYRECORD_EMP;
    TYPE TABLE_EMP_RECORD IS TABLE OF TYRECORD_EMP ;
    FUNCTION FUN_PAGINATION1(CURRENTPAGE NUMBER)   RETURN TABLE_EMP_RECORD
  PIPELINED ;
    END SPILE_PAGE;
    

CREATE OR REPLACE PACKAGE BODY SPILE_PAGE AS 
    FUNCTION  FUN_PAGINATION1(CURRENTPAGE NUMBER)
  RETURN TABLE_EMP_RECORD
  PIPELINED AS
  PAGESIZE NUMBER := 5;
  CURSOR CURSOR_EMP IS
    SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
      FROM (SELECT E.*, ROWNUM RN
              FROM EMP E
             WHERE ROWNUM <= (CURRENTPAGE - 1) * PAGESIZE + PAGESIZE)
     WHERE RN >= (CURRENTPAGE - 1) * PAGESIZE;
BEGIN
    --优化部分 ；
  OPEN CURSOR_EMP;
  LOOP
    FETCH CURSOR_EMP
      INTO REC_EMP;
    EXIT WHEN CURSOR_EMP%NOTFOUND;
    PIPE ROW(REC_EMP);
  END LOOP;
  CLOSE CURSOR_EMP;
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
END FUN_PAGINATION1;
END SPILE_PAGE ;
SELECT * FROM TABLE(SPILE_PAGE.FUN_PAGINATION1(3)) ;