自己编写 Oracle 分页函数
1 CREATE OR REPLACE PACKAGE PACK_PAGINATION AS 2 PAGESIZE CONSTANT NUMBER := 2; 3 TYPE TYRECORD_EMP IS RECORD( 4 EMPNO EMP.EMPNO%TYPE, 5 ENAME EMP.ENAME%TYPE, 6 JOB EMP.JOB%TYPE, 7 MGR EMP.MGR%TYPE, 8 HIREDATE EMP.HIREDATE%TYPE, 9 SAL EMP.SAL%TYPE, 10 COMM EMP.COMM%TYPE, 11 DEPTNO EMP.DEPTNO%TYPE); 12 TYPE RECORD_EMP IS TABLE OF TYRECORD_EMP; 13 14 FUNCTION FUN_PAGINATION(CURRENTPAGE NUMBER) RETURN RECORD_EMP 15 PIPELINED; 16 END; 17 18 CREATE OR REPLACE PACKAGE BODY PACK_PAGINATION AS 19 FUNCTION FUN_PAGINATION(CURRENTPAGE NUMBER) RETURN RECORD_EMP 20 PIPELINED AS 21 EMP_ROW EMP%ROWTYPE; 22 REC_EMP TYRECORD_EMP; 23 COUNTNUM NUMBER; 24 PAGENUM NUMBER; 25 E_EXP1 EXCEPTION; 26 EXP1_STRING VARCHAR2(1000) := '输入页数过大！'; 27 E_EXP2 EXCEPTION; 28 PRAGMA EXCEPTION_INIT(E_EXP2, -06553); 29 CURSOR CURSOR_EMP IS 30 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO 31 FROM (SELECT EMPNO, 32 ENAME, 33 JOB, 34 MGR, 35 HIREDATE, 36 SAL, 37 COMM, 38 DEPTNO, 39 ROWNUM RN 40 FROM EMP 41 WHERE ROWNUM <= (CURRENTPAGE - 1) * PAGESIZE + PAGESIZE) 42 WHERE RN > (CURRENTPAGE - 1) * PAGESIZE 43 ORDER BY DEPTNO; 44 BEGIN 45 46 SELECT COUNT(1) INTO COUNTNUM FROM EMP; 47 PAGENUM := COUNTNUM / PAGESIZE; 48 IF CURRENTPAGE > PAGENUM THEN 49 RAISE E_EXP1; 50 ELSE 51 OPEN CURSOR_EMP; 52 LOOP 53 FETCH CURSOR_EMP 54 INTO EMP_ROW; 55 EXIT WHEN CURSOR_EMP%NOTFOUND; 56 REC_EMP.EMPNO := EMP_ROW.EMPNO; 57 REC_EMP.ENAME := EMP_ROW.ENAME; 58 REC_EMP.JOB := EMP_ROW.JOB; 59 REC_EMP.MGR := EMP_ROW.MGR; 60 REC_EMP.HIREDATE := EMP_ROW.HIREDATE; 61 REC_EMP.SAL := EMP_ROW.SAL; 62 REC_EMP.COMM := EMP_ROW.COMM; 63 REC_EMP.DEPTNO := EMP_ROW.DEPTNO; 64 PIPE ROW(REC_EMP); 65 END LOOP; 66 END IF; 67 CLOSE CURSOR_EMP; 68 RETURN; 69 EXCEPTION 70 WHEN E_EXP1 THEN 71 DBMS_OUTPUT.PUT_LINE(EXP1_STRING || '每页显示' || PAGESIZE || '条, 共计' || 72 PAGENUM || '页!'); 73 WHEN E_EXP2 THEN 74 DBMS_OUTPUT.PUT_LINE('触发了ORA-06553 错误！' || SQLERRM); 75 WHEN OTHERS THEN 76 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK); 77 END FUN_PAGINATION; 78 END;
----代码优化 后
1 --以上代码的优化 ； 2 CREATE OR REPLACE PACKAGE SPILE_PAGE AS 3 4 PAGESIZE NUMBER := 5; 5 TYPE TYRECORD_EMP IS RECORD( 6 EMPNO EMP.EMPNO%TYPE, 7 ENAME EMP.ENAME%TYPE, 8 JOB EMP.JOB%TYPE, 9 MGR EMP.MGR%TYPE, 10 HIREDATE EMP.HIREDATE%TYPE, 11 SAL EMP.SAL%TYPE, 12 COMM EMP.COMM%TYPE, 13 DEPTNO EMP.DEPTNO%TYPE); 14 REC_EMP TYRECORD_EMP; 15 TYPE TABLE_EMP_RECORD IS TABLE OF TYRECORD_EMP ; 16 FUNCTION FUN_PAGINATION1(CURRENTPAGE NUMBER) RETURN TABLE_EMP_RECORD 17 PIPELINED ; 18 END SPILE_PAGE; 19 20 21 CREATE OR REPLACE PACKAGE BODY SPILE_PAGE AS 22 FUNCTION FUN_PAGINATION1(CURRENTPAGE NUMBER) 23 RETURN TABLE_EMP_RECORD 24 PIPELINED AS 25 PAGESIZE NUMBER := 5; 26 CURSOR CURSOR_EMP IS 27 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO 28 FROM (SELECT E.*, ROWNUM RN 29 FROM EMP E 30 WHERE ROWNUM <= (CURRENTPAGE - 1) * PAGESIZE + PAGESIZE) 31 WHERE RN >= (CURRENTPAGE - 1) * PAGESIZE; 32 BEGIN 33 --优化部分 ； 34 OPEN CURSOR_EMP; 35 LOOP 36 FETCH CURSOR_EMP 37 INTO REC_EMP; 38 EXIT WHEN CURSOR_EMP%NOTFOUND; 39 PIPE ROW(REC_EMP); 40 END LOOP; 41 CLOSE CURSOR_EMP; 42 RETURN; 43 EXCEPTION 44 WHEN OTHERS THEN 45 DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM); 46 END FUN_PAGINATION1; 47 END SPILE_PAGE ; 48 SELECT * FROM TABLE(SPILE_PAGE.FUN_PAGINATION1(3)) ;
