Oracle分页存储过程

--创建程序包
create or replace 
PACKAGE pkg_query AS TYPE cur_query IS REF CURSOR;
END pkg_query;

create or replace 
PROCEDURE PROC_DATAPAGINATION 
(
  TableNames IN NVARCHAR2,   --表名,支持多表联查
  Fields IN VARCHAR2 default '*',  --字段名
  Wheres IN NVARCHAR2,  --where条件,不需要加where
  OrderBy IN VARCHAR2,  --排序条件，不需要加order by
  CurrentPage IN NUMBER DEFAULT 1,  --当前页,从1开始,不是0
  PageSize IN NUMBER DEFAULT 10,  --每页显示多少条数据
  GetCount IN  NUMBER DEFAULT 0, --获取的记录总数，0则获取记录总数，不为0则不获取
  TotalCount out NUMBER, --总数
  PageCursor out pkg_query.cur_query--返回的结果集
) is
selectSQL varchar2(8000):='';
tempTable varchar2(100);
tempFields varchar2(2000);
tempWhere nvarchar2(4000);
tempOrderBy varchar2(1000);
getCountSQL varchar2(8000);
tableExists number(3);
startRow number(10);
endRow number(10);
BEGIN
  --没有提供排序字段,默认主键排序
    if OrderBy is null or OrderBy='' then
      tempTable:=TRIM(TableNames);
        --多表联查如果没有提供排序字段,自动找第一个表的主键进行排序
        if INSTR(tempTable,' on ')>0 then
            tempTable:=substr(tempTable,0,INSTR(tempTable,' '));
        elsif INSTR(tempTable,',')>0 then
                tempTable:=substr(tempTable,0,INSTR(tempTable,','));
                --如果有别名如Article a,User u
                if(instr(tempTable,' ')>0) then
                    tempTable:=substr(tempTable,0,INSTR(tempTable,' '));
        end if;
        else
      if INSTR(tempTable,' ')>0 then
        tempTable:=substr(tempTable,0,INSTR(tempTable,' '));     
      end if;
    end if;
        --查询表是否存在
        select count(*) into tableExists from user_tables where table_name = upper(tempTable);
        if tableExists=0 then
      raise_application_error('-20002', '查询表'||tempTable||'不存在');
    end    if;

        --查询排序主键
        select col.COLUMN_NAME into tempOrderBy from user_constraints con,user_cons_columns col where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name=upper(tempTable);
        --如果没有主键,如视图
        if tempOrderBy is null or tempOrderBy = '' then
      raise_application_error('-20002', tempTable||'必须在OrderBy中提供排序字段');
    end if;
  else 
      tempOrderBy:=OrderBy;
    end if;

    --过滤条件
    if Wheres is null or Wheres='' then
        tempWhere:='';
    else
        tempWhere:=' WHERE '||Wheres;
  end if;
  --查询字段
  if Fields is null or Fields='' then
    tempFields:='*';
  else 
    tempFields:=Fields;
  end if;
  
  /*设置分页参数*/
  startRow:=(CurrentPage - 1)*PageSize + 1;
  endRow:=CurrentPage*PageSize;

  selectSQL:='select * from (select t1.* ,rownum rn from(select '|| tempFields ||' from '|| TableNames|| tempWhere ||' ORDER BY '||tempOrderBy ||') t1 where rownum<='|| endRow ||') where rn>='|| startRow;
  --把游标和sql关联
  DBMS_OUTPUT.put_line(selectSQL);
  open PageCursor for selectSQL;
  
    /*
    如果GetCount=0,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,
    把总页数传回给存储过程,避免再次计算总页数,当数据量很大时,select count(*)速度也要几秒钟)
    */
    if GetCount=0 then
        getCountSQL:='SELECT COUNT(*) FROM '||TableNames||tempWhere;
    execute immediate getCountSQL into TotalCount;--返回总记录数
    else
        TotalCount:=GetCount;
  end if;
END PROC_DATAPAGINATION;

Oracle SQL Developer测试

declare curl pkg_query.cur_query;
 totalCount number;
begin
proc_datapagination('VGS_NATION','*','NationID>2','',3,5,0,totalCount,curl);
end;

C#调用

OracleHelper helper = new OracleHelper(connStr);
helper.CreateStoredProcedureCommand("proc_DataPagination");
helper.AddParameter("TableNames", "VGS_Nation");
helper.AddParameter("Fields", "*");
helper.AddParameter("Wheres", "NationID>2");
helper.AddParameter("OrderBy", "");
helper.AddParameter("CurrentPage", 1);
helper.AddParameter("PageSize", 5);
helper.AddParameter("GetCount", 0);
helper.AddParameter("TotalCount", OracleDbType.Int32, 0, null, ParameterDirection.Output);//输出参数
helper.AddParameter("PageCursor", OracleDbType.RefCursor, 0, null, ParameterDirection.Output);
IList<Nation> list = helper.ExecuteReader<Nation>();
int recordSum = Convert.ToInt32(helper.GetValue("TotalCount").ToString());
foreach (Nation item in list)
{
    textBox1.Text +=item.NationId+ item.NationName + "\r\n";
}