自己编写 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)) ;

巴特西

自己编写 Oracle 分页函数

最新文章

热门文章