PL/SQL paging query

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

create or replace procedure fenye(tabelname in varchar2,currentpage in number,pageSize in number,inWhere in varchar2,myCursor out sys_refcursor)
as
v_sql varchar2(300);
v_where varchar2(200);
begin
   if inWhere is not null then
   v_where:=inWhere;
   else
      v_where:='1=1';
      end if;
   v_sql:='select * from (select t.* ,rownum num from '|| tabelname||' t where rownum<='||currentpage*pageSize||') a

              where a.num > '||(currentpage-1)*pageSize; 
  open myCursor for v_sql;
end fenye;

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Patrick at December 14, 2013 - 12:08 PM