oracle创建一个分页过程

select * 
from
(select e.*,rownum rn
 from emp e
 where rownum<=10
) t
where t.rn>=6;


create or replace package testpackage as
type   test_cursor        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.test_cursor--返回记录集
       
)      is
       v_sql       varchar2(1000);
       startNo number:=pageNow*pagesize-pagesize+1;
       endNo number:=pagesize*pageNow;
begin
        --dbms_output.put_line(startNo||'      '||endNo);
        v_sql:='select * from (select e.*,rownum rn from '||tableName||' e where rownum<='||endNo||') t where t.rn>='||startNo;
        open p_cursor for   v_sql;
        v_sql:='select count(*) from '||tableName;
        execute immediate v_sql into myrows;
        if mod(myrows,pagesize)=0 then
          myPageCount := myrows/pagesize;
        else
            myPageCount := myrows/pagesize+1;
        end if;
        --close p_cursor;
 end;
/

declare 
myrows  number(4);
mycount number(4);
pcoursor testpackage.test_cursor;
begin
  --my_pro('emp',10,6);
  fenye('emp',2,3,myrows,mycount,pcoursor);
  end;
/

create or replace  procedure my_pro(
       tableName  in    varchar2,endNo      in    number,startNo    in    number
)      is
pc       testpackage.test_cursor;
v_sql    varchar2(1000);
begin
   v_sql:='select * from (select e.*,rownum rn from '||tableName||' e where rownum<='||endNo||') t where t.rn>='||startNo;
   --v_sql:='select e.* from emp e';
   open pc for v_sql;
   
end;
/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!