最重要的也是最实用的（oracle运用存储过程嵌套游标来实现分页）

-- 创建一个包含有点变量的存储过程；开始分页查询的技术 

CREATE OR REPLACE PROCEDURE PRO_PAGE_EMP 
( 
   TOP IN INT, 
   BUTTOM IN INT 
) 
IS 
--声明一个游标变量 
CURSOR CUR_PAGE_EMP IS 
        SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 
        FROM (SELECT ROWNUM AS NUM ,EMPLOYEE.* FROM EMPLOYEE) WHERE NUM >=TOP AND NUM <=BUTTOM; 

--定义一个自定义变量 用于存放从有游标中取到的值； 
   TYPE PAGE_TYPE IS RECORD 
   ( 
        EMPNO SCOTT.EMPLOYEE.EMPNO%TYPE, 
        ENAME SCOTT.EMPLOYEE.ENAME%TYPE, 
        JOB SCOTT.EMPLOYEE.JOB%TYPE, 
        MGR SCOTT.EMPLOYEE.MGR%TYPE, 
        HIREDATE SCOTT.EMPLOYEE.HIREDATE%TYPE, 
        SAL SCOTT.EMPLOYEE.SAL%TYPE, 
        COMM SCOTT.EMPLOYEE.COMM%TYPE, 
        DEPTNO SCOTT.EMPLOYEE.DEPTNO%TYPE 
   ); 
   PAGE_DEMO_TYPE PAGE_TYPE; 
BEGIN 
      --打开游标 
      OPEN CUR_PAGE_EMP; 
      LOOP 
          FETCH CUR_PAGE_EMP INTO PAGE_DEMO_TYPE; 
          EXIT WHEN CUR_PAGE_EMP%NOTFOUND; 
      END LOOP;     
      CLOSE CUR_PAGE_EMP; 
END;