set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[QueryPagedTable] 
@PageSize    int,
@PageNo        int,
@Table        sysname,
@Filter        nvarchar(1000),
@Order        nvarchar(500)
as      
    begin

        IF (LEN(@Order) > 0)
            SET @Order = ' ORDER BY ' + @Order
        ELSE
            SET @Order = ' ORDER BY ID ' 
        declare @newSql Nvarchar(max)  
        set @newSql=''
        declare @beginPage as  nvarchar(100) --开始数字
        set    @beginPage=(@PageNo*@PageSize)-(@pageSize-1)
        declare  @endPage nvarchar(100)
        set @endPage=@beginPage+@PageSize-1
        set @newSql='select * from (
        SELECT Row_Number() over('+ @order +') as rowNum,* from '+@Table+' as aa where  '+@Filter+') as yy
        where rowNum BETWEEN '+ @beginPage+' and '+@endPage+ @Order
        print @newSql
        exec (@newSql)
    end

--转换
create or replace procedure QueryPagedTable(
p_pagesize int,
p_pageno int,
p_table varchar2,
p_filter varchar2,
p_order in out varchar2 
)
is
v_sql varchar2(4000) := '';
v_begPage varchar2(100) := '';
v_endPage varchar2(100) := '';
type cur_type is ref cursor;
cur cur_type;
v_cols varchar2(4000):='';
v_col varchar2(30):='';
v_sqlcol varchar2(4000):='';
begin
    if (length(p_order)>0) then
        p_order := ' ORDER BY '|| p_order;
    else
        p_order := ' ORDER BY ID';
    end if;
    v_begPage := p_pageno*p_pagesize-(p_pagesize-1);
    v_endPage := to_number(v_begPage)+p_pagesize-1;
    v_sqlcol := 'select column_name from user_tab_columns where table_name=upper('''||p_table||''')';
    open cur for v_sqlcol;
    loop
        fetch cur into v_col;
        exit when cur%notfound;
        v_cols := v_cols||','||v_col;
    end loop;
    close cur;
    v_sql := 'select * from (
        SELECT Row_Number() over('|| p_order ||') rn'||v_cols||' from '||p_table||' aa where  '||p_filter||') yy
        where rn BETWEEN '|| v_begPage ||' and '|| v_endPage || p_order;
    dbms_output.put_line(v_sql);
    execute immediate v_sql;
end;
/

------解决方案--------------------
如果你的oracle版本是oracle9i以后，直接用sys_refcursor就可以了

------解决方案--------------------
create or replace procedure QueryPagedTable(
p_pagesize int,
p_pageno int,
p_table varchar2,
p_filter varchar2,
p_order in out varchar2  
)
is
v_sql varchar2(4000) := '';
v_begPage varchar2(100) := '';
v_endPage varchar2(100) := '';
type cur_type is ref cursor;
cur cur_type;
v_cols varchar2(4000):='';
v_col varchar2(30):='';
v_sqlcol varchar2(4000):='';
begin
   if (length(p_order)>0) then
       p_order := ' ORDER BY '|| p_order;
   else
       p_order := ' ORDER BY ID';
   end if;
   v_begPage := p_pageno*p_pagesize-(p_pagesize-1);
   v_endPage := to_number(v_begPage)+p_pagesize-1;
   v_sqlcol := 'select column_name from user_tab_columns where table_name=upper('''||p_table||''')';
   open cur for v_sqlcol;
   loop
       fetch cur into v_col;
       exit when cur%notfound;
       v_cols := v_cols||','||v_col;
   end loop;
   close cur;
   v_sql := 'select * from (
       SELECT Row_Number() over('|| p_order ||') rn'||v_cols||' from '||p_table||' aa where  '||p_filter||') yy
       where rn BETWEEN '|| v_begPage ||' and '|| v_endPage || p_order;
   dbms_output.put_line(v_sql);
   execute immediate v_sql;
end;
/

declare  
   v_pagesize int := 10;
   v_pageno int := 2;
   v_table varchar2(50) := 'emp';
   v_filter varchar2(1000) := 'empno=7369';