熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle如何返回指定行數之間的查詢結果

2013-11-13 16:09:45  來源: Oracle 

  如何返回指定行數之間的查詢結果以實現web記錄分頁在Oracle中有許多的方法這裡僅僅列出了希望能對大家有所幫助大家可以根據不同需要選擇下面的script 
  
  )select where rownum < 50 minus select ... where rownum < 30 
    這個方法因為用到了minus操作符,所以速度會受影響。 
  2) 
  SELECT results.* FROM 
  ( SELECT t2.*, rownum rownumber FROM 
  ( SELECT t.* FROM mv_table t WHERE ORDER BY col1) t2) results 
  WHERE results.rownumber BETWEEN 30 and 50 ORDER BY col1 
  這個方法是從一個論壇上看到的,沒有親自測試過 
  3) 
  定義cursor x, 2.fetch x a,b,c; loop ...... end loop; 
  其中用兩個循環變量和一個FLAG變量,分別表示,當前的記錄數,屬於第幾頁的, 及第一頁面。tw.WINGwit.cOM 
  ps; 
  j:=to_number(kafyf); 
  i:=1; 
  open cx; 
  loop fetch cx into col1,col2,col3,col4,col5,col6; 
  if cx%NOTFOUND then exit; end if; 
    if i>=j then 
  htp.tableRowOpen; 
  htp.tableData(col1); 
  htp.tableData(col2); 
  htp.tableData(col4); 
  htp.tableData(col5); 
  htp.tableData(col6); 
  htp.tableData(col3); 
  htp.tableRowClose; 
  i:=i+1; 
  if i=j+10 then l:=1; exit; end if; 
  else i:=i+1; 
  end if; 
  end loop; 
  close x; 
  該方法是名叫‘淼’的網友寫的script,他用到了Oracle web2kit中的OWA_UTIL package。 
  
  4)How can one page forward and backwards through a table? 
  Externalize ROWNUM by implementing queries like this: 
  
  SELECT ... 
  FROM (SELECT ROWNUM rnum, ... FROM ...) 
  WHERE rnum BETWEEN :low AND :high AND rownum <(:high :low + 1); 
    where :low and :high are dynamically generated values depending on which result page the user 
  is viewing. Typically, they are used to show "Next 15 matches", "Previous 15 matches" links at the 
  bottom of each page. 

From:http://tw.wingwit.com/Article/program/Oracle/201311/17917.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.