Pl/sql 分页

declare  
pageSize number;   
currentPage number;   
row_count number;--表的总行数   
firstRow number;--分页查询第一条   
lastRow number;-- 分页查询最后一条   
pageTotalNum number;--总页数   
process number;--过程   
cur_1 number;--游标   
table_name varchar2(100);--输入的表名   
column_num number;--列数   
tmp_sql varchar2(500);  --动态sql语句   
get_count_sql varchar2(500);   
get_column_num_sql varchar2(200);   
n number;--计数   
TYPE T_TAB is table of varchar2(50) index by binary_integer;--数组类型   
my_array T_TAB;--数组   
begin  
    table_name:=UPPER('ShiPINFO');   
   pageSize:=to_number('50');   
   currentPage:=to_number('2');   
    dbms_output.put_line('输入表名:'||table_name||' ,每页条数：'||pageSize||'条'||' ,当前页:第'||currentPage||'页');   
       
    --表的总行数   
    get_count_sql:='select count(*) from '||table_name;   
    EXECUTE IMMEDIATE get_count_sql INTO row_count;   
   dbms_output.put_line(table_name||'表的总条数：'||row_count||'条');   
       
    --分页共多少页   
    pageTotalNum:=ceil(row_count/pageSize);   
    dbms_output.put_line('总共'||pageTotalNum||'页');   
    firstRow:=1+pageSize*(currentPage-1);   
    lastRow:=pageSize*currentPage;   
   if lastRow>row_count then  
        lastRow:=row_count;   
    end if;   
       
    dbms_output.put('当记录');   
    dbms_output.put_line('从 第'||firstRow||'条 到 第'||lastRow||'条');      
    
       
--获取参数表的列数   
    --select count(*) from (select * from user_tab_columns where TABLE_NAME='&name');   
    --动态执行sql   
    get_column_num_sql:='select count(*) from '||'(select * from user_tab_columns where TABLE_NAME='''||table_name||''')';   
    EXECUTE IMMEDIATE get_column_num_sql INTO column_num;  --获取列数   
    dbms_output.put_line(table_name||'表，共有'||column_num||'行');   
  
--实现分页输出       
    --临时执行的sql   
    tmp_sql:='select tt.* from (select rownum as r_num, t.* from  (select * from '||table_name||' ) t) tt where tt.r_num>='||firstRow||' and  r_num<='||lastRow;   
    --动态游标   
        cur_1:=dbms_sql.open_cursor;   
    dbms_sql.parse(cur_1,tmp_sql,dbms_sql.native);   
     --定义游标的列   
    for n in 1..column_num+1 loop   
        my_array(n):='';   
   end loop;   
       
    for n in 1..column_num+1 loop   
        dbms_sql.define_column(cur_1,n,my_array(n),50);   
    end loop;   
       
    process:=dbms_sql.execute(cur_1);   
       
    loop   
        if dbms_sql.fetch_rows(cur_1)>0 then  
            for n in 1..column_num+1 loop   
                dbms_sql.column_value(cur_1,n,my_array(n));   
                dbms_output.put('......'||my_array(n));   
            end loop;   
            dbms_output.put_line('');       
        else  
            exit;   
        end if;   
    end loop;   
end;