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;