Oracle 中用存储过程嵌套游标来实现分页

Oracle 的过程要返回结果集是比较复杂的；要结合游标嵌套使用 (据说还能用临时表 但临时现在没掌握…………嘿嘿…… 待续。)

下面就是hi自己总结的例子 也是面试的时候经常要考的内容：要谨记 还要多加回顾

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

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;