存储过程写的oracle 分页查询

先创建一个游标类型
create or replace package p_page is
  type refCursorType is REF CURSOR;  --游标类型定义，用于返回数据
end p_page;

存储过程如下
CREATE OR REPLACE PROCEDURE proc_pageview(p_pagesize INT,
  p_pageno INT ,--当前页码 从1开始
	p_sqlselect VARCHAR2,--查询语句部分,含排序部分
  --p_outrecordcount OUT INT,--f返回总记录数
	p_outcursor OUT p_page.refCursorType,
	v_sql_count VARCHAR2 --简化的取记录总数的sql语句
) AS
  v_sql VARCHAR2(3000);
  v_count INT;
	v_endrownum INT;
	v_startrownum INT;
BEGIN
	---取记录数
  ---使用动态sql获取记录总数
	EXECUTE IMMEDIATE v_sql_count INTO v_count;
 --p_outrecordcount :=v_count;
	  
	  v_endrownum := p_pageno*p_pagesize;
		v_startrownum :=v_endrownum-p_pagesize+1;
		
	----the sql for page slide
	v_sql :='select * 
	                from ( select a.*, rownum rn
									       from ('|| p_sqlselect ||') a
												 where rownum <= '||to_char(v_endrownum) || '
												 )b
												 where rn >= ' || to_char(v_startrownum);
								 EXECUTE IMMEDIATE v_sql;
										OPEN p_outcursor FOR v_sql;
		END  proc_pageview;

测试结果
 DECLARE
   l_cursor p_page.refCursorType;
   l_ename emp.ename%TYPE;
   l_empno emp.empno%TYPE;
   l_deptno emp.deptno%TYPE;
   BEGIN
      proc_pageview(5,2,'select  ename,empno from emp order by empno ',p_outcursor=>l_cursor,v_sql_count => 'select count(*) from emp');
            LOOP
                     FETCH l_cursor INTO l_ename,l_empno,l_deptno ;
                     EXIT WHEN l_cursor%NOTFOUND;
                     dbms_output.put_line(l_ename|| ' | '|| l_empno|| ' | ' || l_deptno);
                     END LOOP;
                     CLOSE l_cursor;
                      END;
                       /

BLAKE | 7698 | 6
CLARK | 7782 | 7
SCOTT | 7788 | 8
KING | 7839 | 9
TURNER | 7844 | 10