select t1.*,rownum rn from (select * from emp) t1; select t1.*,rownum rn from (select * from emp) t1 where rownum<=10; --在分页的时候,可以把下面的sql语句当做一个模板使用 select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6; --开发一个包 --1、创建一个包 create or replace package testpackage as type cursor_test is ref cursor; end testpackage; --开始编写分页的过程 create or replace procedure fenye(tableName in varchar2, pageSize in number, --每页显示记录数 pageNow in number, myRows out number,--总记录数 myPageCount out number,--总页数 p_cursor out testpackage.cursor_test) is --定义sql语句 字符串 v_sql varchar2(1000); --定义2个整数 v_begin number:=(pageNow-1)*pageSize+1; v_end number:=pageNow*pageSize; begin v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin||''; --把游标和sql关联 open p_cursor for v_sql; --计算myRows和myPageCount --组织一个sql v_sql:='select count(*) from '||tableName||''; --执行sql,并把返回的值,赋给myRows execute immediate v_sql into myRows; --计算myPageCount if mod(myRows,pageSize)=0 then myPageCount:=myRows/pageSize; else myPageCount:=myRows/pageSize+1; end if; --关闭游标 --close p_cursor; end;