oracle分页存储过程

create or replace procedure QueryPagedTable(
p_pagesize in int,
p_pageno in int,
p_table in varchar2,
p_filter in varchar2,
p_order in varchar2,
pageResultSet out SYS_REFCURSOR
)
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):='';
order2 varchar2(100) :='';
begin
    if (length(p_order)>0) then
        --p_order := ' ORDER BY '|| p_order;
        order2 := ' ORDER BY '|| p_order;
    else
        --p_order := ' ORDER BY ID';
        order2 := ' 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('|| order2 ||') rn'||v_cols||' from '||p_table||' aa where  '||p_filter||') yy
        where rn BETWEEN '|| v_begPage ||' and '|| v_endPage || order2;
    open pageResultSet for v_sql;
    --dbms_output.put_line(v_sql);
    --execute immediate v_sql;
end;