Oracle分页查询存储过程及C#调用示例

CREATE OR REPLACE Package Body package_page 
Is 
       --存储过程 
      Procedure proc_page( 
             p_tablename varchar2,                --表名emp e 
             p_tablecolumn varchar2,             --查询列e.id,e.ename,e.job 
             p_order varchar2,                         --排序e.ename desc 
             p_pagesize Number,                   --每页大小 
             p_curpage Number,                     --当前页 
             p_where varchar2,                       --查询条件e.ename like '%S%' 
             p_rowcount out Number,             --总条数,输出参数 
             p_pagecount out number,            --总页数 
             p_cursor out cursor_page          --结果集 
      ) 
      is 
            v_count_sql varchar2(2000); 
            v_select_sql varchar2(2000); 
      begin 
            --查询总条数 
            v_count_sql:='select count(*) from '||p_tablename; 
            --连接查询条件(''也属于is null) 
            if p_where is not null  then 
               v_count_sql:=v_count_sql||' where '||p_where; 
            end if; 
            --执行查询,查询总条数 
            execute immediate v_count_sql into p_rowcount; 

            --dbms_output.put_line('查询总条数SQL=>'||v_count_sql); 
            --dbms_output.put_line('查询总条数Count='||p_rowcount); 

             --得到总页数 
             if mod(p_rowcount,p_pagesize)=0 then 
                p_pagecount:=p_rowcount/p_pagesize; 
             else 
                p_pagecount:=p_rowcount/p_pagesize+1; 
             end if; 

            --如果查询记录大于0则查询结果集 
            if p_rowcount>0 and p_curpage>=1 and p_curpage<=p_pagecount then 

               --查询所有(只有一页) 
               if p_rowcount<=p_pagesize then 
                  v_select_sql:='select '||p_tablecolumn||' from '||p_tablename; 
                  if p_where is not null then 
                     v_select_sql:=v_select_sql||' where '||p_where; 
                  end if; 
                  if p_order is not null then 
                      v_select_sql:=v_select_sql||' order by '||p_order; 
                  end if; 
               elsif p_curpage=1 then  --查询第一页 
                  v_select_sql:='select '||p_tablecolumn||' from '||p_tablename; 
                  if p_where is not null then 
                     v_select_sql:=v_select_sql||' where '||p_where||' and 

rownum<='||p_pagesize; 
                  else 
                     v_select_sql:=v_select_sql||' where rownum<='||p_pagesize; 
                  end if; 
                  if p_order is not null then 
                      v_select_sql:=v_select_sql||' order by '||p_order;