c# oracle 分頁

//////////////////////////////////////////oracle部分預存程序

create or replace package PACK_PAGE is

 

 TYPE type_cur IS REF CURSOR;     --定義遊標變數用於返回記錄集
  PROCEDURE PROC_PAGE
  (
  Pindex in number,                --分頁索引
  Psql in varchar2,                --產生dataset的sql語句
  Psize in number,                 --頁面大小
  Pcount out number,               --返回分頁總數
  v_cur out type_cur               --返回當前頁資料記錄
  );
  procedure PROC_PAGECOUNT
  (
  Psqlcount in varchar2,           --產生dataset的sql語句
  Prcount   out number             --返回記錄總數
  );

end PACK_PAGE;

///////////////////////////////////////////////////////

create or replace package body PACK_PAGE is

 

 PROCEDURE PROC_PAGE
  (
  Pindex in number,
  Psql in varchar2,
  Psize in number,
  Pcount out number,
  v_cur out type_cur
  )
  AS
  v_sql VARCHAR2(5000);
  v_tempSql varchar2(5000);
  v_count number;
  v_Plow number;
  v_Phei number;
  Begin
  ------------------------------------------------------------取分頁總數
  v_sql := 'select count(*) from (' || Psql || ')';
  execute immediate v_sql into v_count;
  Pcount := ceil(v_count/Psize);
  ------------------------------------------------------------顯示任意頁內容
  v_Phei := (Pindex-1) * Psize + Psize;
  v_Plow := v_Phei - Psize + 1;
  v_tempSql := 'select rownum rn,t.* from ('|| Psql ||')t Where rownum <= ' || v_Phei ;            --要求必須包含rownum欄位
  v_sql := 'select * from (' || v_tempSql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
  open v_cur for v_sql;
  End PROC_PAGE;
 --**************************************************************************************
  procedure PROC_PAGECOUNT
  (
  Psqlcount in varchar2,
  Prcount   out number
  )
  as
  v_sql varchar2(5000);
  v_prcount number;
  begin
  v_sql := 'select count(*) from (' || Psqlcount || ')';
  execute immediate v_sql into v_prcount;
  Prcount := v_prcount;                  --返回記錄總數
  end PROC_PAGECOUNT;
 --**************************************************************************************
end PACK_PAGE;

 

////////////////////// c# 調用

   /// <summary>
        /// 分頁條件查詢
        /// </summary>
        /// <param name="sql">SQL語句</param>
        /// <param name="pageIndex">當前頁</param>
        /// <param name="pageSize">每頁包含的記錄數</param>
        /// <param name="tabName">填充資料集時的表名稱</param>
        /// <returns></returns>
        public DataSet QueryByPage(string sql, int pageIndex, int pageSize, string tabName)
        {
            using (OracleHelper orahp = new OracleHelper())
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(new DataTable(tabName));

 

                orahp.Open();
                orahp.ExecuteSP_Page(ds.Tables[tabName], "pack_page.proc_page", sql, pageIndex, pageSize, true);
                orahp.Close();

                return ds;
            }
        }

 

 

　　 public int ExecuteSP_Page(DataTable outDataTable, string procName, string sql, int pageIndex, int pageSize, bool setCase)
        {
            if (null == connection)
            {
                throw new ObjectDisposedException(GetType().FullName);
            }

 

            int nReturn = 0;

            using (OracleCommand command = new OracleCommand(procName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = this._timeout;

                command.Parameters.Add("psql", OracleType.VarChar).Value = sql;
                command.Parameters.Add("pindex", OracleType.Number).Value = pageIndex;
                command.Parameters.Add("psize", OracleType.Number).Value = pageSize;
                command.Parameters.Add("pcount", OracleType.Number).Direction = ParameterDirection.Output;
                command.Parameters.Add("v_cur", OracleType.Cursor).Direction = ParameterDirection.Output;

                if (null != transaction)
                {
                    command.Transaction = transaction;
                }

                using (OracleDataAdapter oracAdapter = new OracleDataAdapter(command))
                {
                    if (setCase) outDataTable.CaseSensitive = true;
                    nReturn = oracAdapter.Fill(outDataTable);
                }

                command.Parameters.Clear();

                return nReturn;
            }
        }