oracle 分页存储过程

--数据库创建存储过程包

create or replace package DotNet is
  type type_cur is ref  cursor; --定义游标变量用于返回记录集
  procedure DotNetPagination(Pindex in number, --分页索引
                                 Psql   in varchar2, --产生dataset的sql语句
                                 Psize  in number, --页面大小
                                 v_cur  out type_cur --返回当前页数据记录
                                 );
  procedure DotNetPageRecordsCount(Psqlcount in varchar2, --产生dataset的sql语句
                                       Prcount   out number --返回记录总数
                                       );
end DotNet;

 

create or replace package body DotNet is
procedure DotNetPagination(Pindex in number,
                             Psql   in varchar2,
                             Psize  in number,
                             v_cur  out type_cur) AS
    v_sql    VARCHAR2(4000);
    v_count  number;
    v_Plow   number;
    v_Phei   number;
    v_Appsql varchar2(1000);
  Begin
    v_Phei   := Pindex * Psize + Psize;
    v_Plow   := v_Phei - Psize + 1;

    v_Appsql := '';
    if (Pindex < 1000) then
      v_Appsql := ' where rownum <= ' || v_Phei;
    end if;  
    --v_sql select rownum as rn,t1.* from " + tableName + " t1 ;要求必须包括rownum字段
    v_sql := 'select * from (' || Psql || v_Appsql || ') where rn between ' || v_Plow || ' and ' || v_Phei;

    open v_cur for v_sql;
  End DotNetPagination;
  procedure DotNetPageRecordsCount(Psqlcount in varchar2,
                                   Prcount   out number) as
    v_sql     varchar2(4000);
    v_prcount number;
  begin
    v_sql := 'select count(*) from (' || Psqlcount || ')';
    execute immediate v_sql
      into v_prcount;
    Prcount := v_prcount;
  end DotNetPageRecordsCount;
end DotNet;

 

--C#用Oracle.DataAccess调用

DataTable dt = new DataTable();
   dt.TableName = tableName;
   IDataAccess da = IVDBConnect.Instance.GetDataAccess();
   IDbConnection conn = da.GetConnection;
   OracleCommand command = new OracleCommand();
   command.Connection = (OracleConnection)conn;
   //command.CommandType = CommandType.StoredProcedure;
   command.CommandText = "begin DotNet.DotNetPagination(:Pindex,:Psql,:Psize,:v_cur);end;";
   OracleParameter p1 = new OracleParameter("Pindex", OracleDbType.Decimal);
   p1.Direction = ParameterDirection.Input;
   p1.Value = Pindex;
   command.Parameters.Add(p1);
   OracleParameter p2 = new OracleParameter("Psql", OracleDbType.Varchar2);
   p2.Direction = ParameterDirection.Input;
   p2.Value = @" select rownum as rn,t1.* from " + tableName + " t1 ";
   command.Parameters.Add(p2);
   OracleParameter p3 = new OracleParameter("Psize", OracleDbType.Decimal);
   p3.Direction = ParameterDirection.Input;
   p3.Value = Psize;
   command.Parameters.Add(p3);
   OracleParameter p4 = new OracleParameter("v_cur", OracleDbType.RefCursor);
   p4.Direction = ParameterDirection.ReturnValue;
   command.Parameters.Add(p4);
   try
   {
    command.Connection.Open();
    //IDbTransaction trans = conn.BeginTransaction();
    //command.Transaction = trans;
    command.ExecuteNonQuery();
    //command.Transaction.Commit();
    OracleDataAdapter dap = new OracleDataAdapter(command);
    dap.Fill(dt);

   }
   catch (Exception ex)
   {
    AppLog.Log("分页查询失败！" + ex.Message + "\n\r" + ex.StackTrace, AppLog.LogType.Error);
    throw ex;
   }
   finally
   {
    command.Connection.Close();
   }