Oracle分頁查詢

一.引用文章

SELECT /*+ FIRST_ROWS */ * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A --不排序
WHERE ROWNUM <= 40
)
WHERE RN >= 21;

SELECT /*+ FIRST_ROWS */ *
FROM (SELECT a.*, row_number() over(ORDER BY 1) rn--假排序，速度同上
FROM t1 a)
WHERE rn BETWEEN 21 AND 40;

SELECT *
FROM (SELECT a.*, row_number() over(ORDER BY c1) rn --真實排序，無法比較速度
FROM t1 a)
WHERE rn BETWEEN 21 AND 40;

Oracle的分頁查詢語句基本上可以按照本文給出的格式來進行套用。
分頁查詢格式：

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最內層的查詢SELECT * FROM TABLE_NAME表示不進行翻頁的原始查詢語句。ROWNUM <= 40和RN >= 21控制分頁查詢的每頁的范圍。

上面給出的這個分頁查詢語句，在大多數情況擁有較高的效率。分頁的目的就是控制輸出結果集大小，將結果盡快的返回。在上面的分頁查詢語句中，這種考慮主要體現在WHERE ROWNUM <= 40這句上。

選擇第21到40條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過ROWNUM <= 40來控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 40語句，在查詢的最外層控制分頁的最小值和最大值。這是，查詢語句如下：

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE RN BETWEEN 21 AND 40

對比這兩種寫法，絕大多數的情況下，第一個查詢的效率比第二個高得多。

這是由于CBO優化模式下，Oracle可以將外層的查詢條件推到內層查詢中，以提高內層查詢的執行效率。對于第一個查詢語句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內層查詢中，這樣Oracle查詢的結果一旦超過了ROWNUM限制條件，就終止查詢將結果返回了。

而第二個查詢語句，由于查詢條件BETWEEN 21 AND 40是存在于查詢的第三層，而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義，因為最內層查詢不知道RN代表什么）。因此，對于第二個查詢語句，Oracle最內層返回給中間層的是所有滿足條件的數據，而中間層返回給最外層的也是所有數據。數據的過濾在最外層完成，顯然這個效率要比第一個查詢低得多。

上面分析的查詢不僅僅是針對單表的簡單查詢，對于最內層查詢是復雜的多表聯合查詢或最內層查詢包含排序的情況一樣有效。

這里就不對包含排序的查詢進行說明了，下一篇文章會通過例子來詳細說明。下面簡單討論一下多表聯合的情況。對于最常見的等值表連接查詢，CBO一般可能會采用兩種連接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不會考慮）。在這里，由于使用了分頁，因此指定了一個返回的最大記錄數，NESTED LOOP在返回記錄數超過最大值時可以馬上停止并將結果返回給中間層，而HASH JOIN必須處理完所有結果集（MERGE JOIN也是）。那么在大部分的情況下，對于分頁查詢選擇NESTED LOOP作為查詢的連接方法具有較高的效率（分頁查詢的時候絕大部分的情況是查詢前幾頁的數據，越靠后面的頁數訪問幾率越小）。

因此，如果不介意在系統中使用HINT的話，可以將分頁的查詢語句改寫為：

SELECT /*+ FIRST_ROWS */ * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

二.我的存儲過程

create or replace package  CJ_PageData is

TYPE type_cur IS REF CURSOR;     --建立游標

PROCEDURE CJ_GetDataByPage
(
tblName in  varchar2,       

strGetFields in varchar2,  

fldName in varchar2,        

PageSize in  number,        

PageIndex in  number,           

doCount  in number,   

OrderType in number,  

strWhere in varchar2,  

DataCount out number,              

DataSet out type_cur          

);
end CJ_PageData;

CREATE OR REPLACE PACKAGE BODY CJ_PageData
AS

PROCEDURE CJ_GetDataByPage
(
tblName in  varchar2,      --表名 

strGetFields in varchar2,  --返回字段

fldName in varchar2,       --排序字段名

PageSize in  number,       --分頁大小  

PageIndex in  number,      --頁碼    

doCount  in number,        --是否統計(0否,1是)

OrderType in number,       --排序類型(0降,1升)

strWhere in varchar2,      --篩選條件

DataCount out number,      --返回記錄總數   

DataSet out type_cur       --返回游標(指針)
)

as

strSQL   varchar2(5000) ;      -- 臨時記錄Sql語句

strOrder varchar2(400) ;       -- 臨時記錄排序語句

begin

if doCount <> 0 then
    begin
      if  strWhere <>  ' ' then
  
          strSQL := 'select count(*) as total from ' || tblName || ' where '||strWhere;
             
      else
          strSQL := 'select count(*) as total from ' || tblName;
      end if;
      
      execute immediate  strSQL into DataCount;--取出記錄總數放入返回變量DataCount      
    end;

else

    begin
         
         strSQL := 'select ' ||strGetFields||' from (select T2.*,rownum rn from (select T1.* from ' || tblName||' T1 ';
         
        --得到排序類型語句
        if OrderType <> 0 then

           begin           
                 strOrder := ' order by ' || fldName ||' asc';              
           end;

        else

           begin
                  strOrder := ' order by ' || fldName ||' desc';           
           end;

        end if;
        
        begin
       --組裝SQl語句

                 if strWhere <> ' ' then
                
                    strSQL := strSQL || ' where ' || strWhere || ') T2 where rownum <=' || TO_CHAR(PageIndex*PageSize) || ')'
                    || ' where rn >' || TO_CHAR((PageIndex-1)*PageSize) || strOrder;
                    
                else
                
                    strSQL := strSQL || ' ) T2 where rownum <=' || TO_CHAR(PageIndex*PageSize) || ')'
                    || ' where rn >' || TO_CHAR((PageIndex-1)*PageSize) || strOrder;
                    
                end if;
           
       end;
    end;
end if;
open DataSet for strSQL;
end;

end CJ_PageData;

三,.Net中調用存儲過程

1.方法

/// <summary>
  /// 獲得記錄總數和所有記錄{連接字符串,存儲過程,表,返回字段,排序字段,分頁大小,頁碼,統計(1是,0不是),排序(1升序,0降序),條件}
  /// </summary>
  /// <param name="strConn"></param>
  /// <param name="strSPName">存儲過程名字</param>
  /// <param name="strTableName">表名</param>
  /// <param name="strGetFields">返回字段字符串</param>
  /// <param name="strFieldName">排序字段</param>
  /// <param name="iPageIndex">頁碼</param>
  /// <param name="iPageSize">分頁大小</param>
  /// <param name="iDoCount">是否統計記錄總數(1統計,0不統計)</param>
  /// <param name="iOrderType">排序類型(0升序,1降序)</param>
  /// <param name="strWhere">篩選條件(不帶where)</param>
  protected void BuildData(string strConn,string strSPName,string strTableName,string strGetFields,string strFieldName,int iPageSize,int  iPageIndex,int iDoCount,int iOrderType,string strWhere)
  {
   System.Data.OracleClient.OracleConnection conn = new OracleConnection();
   System.Data.OracleClient.OracleCommand cmd = new OracleCommand();
   System.Data.OracleClient.OracleDataReader dr;

   conn.ConnectionString = strConn;
   cmd.Connection = conn;
   cmd.CommandType = CommandType.StoredProcedure;

   conn.Open();

   cmd.CommandText = strSPName;

   cmd.Parameters.Add("tblName",OracleType.VarChar);//表名
   cmd.Parameters.Add("strGetFields",OracleType.VarChar);//返回字段名
   cmd.Parameters.Add("fldName",OracleType.VarChar);//排序字段名
   cmd.Parameters.Add("PageSize",OracleType.Number);//分頁大小
   cmd.Parameters.Add("PageIndex",OracleType.Number);//頁碼
   cmd.Parameters.Add("doCount",OracleType.Number);//是否統計總記錄數
   cmd.Parameters.Add("OrderType",OracleType.Number);//排序類型
   cmd.Parameters.Add("strWhere",OracleType.VarChar);//篩選條件
   cmd.Parameters.Add("DataCount",OracleType.Number);//返回得記錄總數
   cmd.Parameters.Add("DataSet",OracleType.Cursor);//返回得記錄集

   cmd.Parameters["tblName"].Value = strTableName;
   cmd.Parameters["strGetFields"].Value = strGetFields;
   cmd.Parameters["fldName"].Value = strFieldName;
   cmd.Parameters["PageSize"].Value = iPageSize;
   
   cmd.Parameters["PageIndex"].Value = ( iOrderType == 0 ) ? (pager.PageCount+1-iPageIndex) : iPageIndex;//因為Oracle中不支持子查詢排序,所以在這里如果需要倒序,則將頁碼按反序取數據,"以假亂真".嘿嘿!
   cmd.Parameters["doCount"].Value = iDoCount;
   cmd.Parameters["OrderType"].Value = iOrderType;
   cmd.Parameters["strWhere"].Value = strWhere;
   cmd.Parameters["DataCount"].Direction = ParameterDirection.Output;
   cmd.Parameters["DataSet"].Direction = ParameterDirection.Output;

   cmd.Parameters["tblName"].Size = 100;
   cmd.Parameters["strGetFields"].Size = 1000;
   cmd.Parameters["fldName"].Size = 100;
   cmd.Parameters["strWhere"].Size = 1000;
  
   //try
   {
    switch (iDoCount)
    {
     case 0:
     {
      dr = cmd.ExecuteReader();
      dgData.DataSource = dr;
      dgData.DataBind();//綁定數據源
      dr.Close();
      break;
     }
     case 1:
     { 
      cmd.ExecuteOracleScalar();
      pager.RecordCount = int.Parse(cmd.Parameters["DataCount"].Value.ToString());
      break;
     }
    }
    
   }
   //catch
   {
   }
   conn.Close();
 
  }

2.調用

A.頁面加載時候取得總記錄數,以綁定分頁控件

private void Page_Load(object src,EventArgs e)
  {
   if(!Page.IsPostBack)
   {
    string conn = "password=cqcj;user id=cqcj;data source=cqc1";
    this.BuildData(conn,"CJ_PageData.CJ_GetDataByPage","CJ_Log","","",0,0,1,0,"UserID = 'xxr'");
   
    BindData();//這里給分頁控件.如DataGrid綁定數據源
   }
  }

B.BindData()

protected void BindData()
  {
   string conn = "password=cqcj;user id=cqcj;data source=cqc1";

   this.BuildData(conn,"CJ_PageData.CJ_GetDataByPage","CJ_Log","*","LoginTime",pager.PageSize,pager.CurrentPageIndex,0,1,"UserID = 'xxr'");

   pager.CustomInfoText="記錄總數：<font color=\"blue\"><b>"+pager.RecordCount.ToString()+"</b></font>";
   pager.CustomInfoText+=" 總頁數：<font color=\"blue\"><b>"+pager.PageCount.ToString()+"</b></font>";
   pager.CustomInfoText+=" 當前頁：<font color=\"red\"><b>"+pager.CurrentPageIndex.ToString()+"</b></font>";
  }

四.總結

    Oracle中無法直接返回記錄集,需利用游標返回.Oracle中不支持子查詢排序.臨時表耗費資源不推薦使用.