教你如何在Oracle中實現搜索分頁查詢

在構建自定義搜索引擎時，開發人員常遇到的一個問題是實現某種類型的分頁功能；也就是說，允許用戶提交一個返回很多行數據的查詢，但是只顯示前20條。在用戶點擊一個鏈接時，下20條或者前20條數據會從數據庫應用程序中取出。

數據庫訪問存在的一個問題是來自 Web 站點的請求是無狀態的。在等待用戶請求下一組數據時，讓數據庫維護一個游標的效率是非常低的。對 Html 編寫代碼讓游標位於客戶端是可以做到的，但是游標是一個有限的資源，所以每次在頁面裝載完成時最好關閉游標。

Oracle 游標不支持通過行集(rowset)向後移動;但是用戶總是會在浏覽器中後退，或者不按順序請求一個行集。顯然，返回有限行數據就成了數據庫服務器的責任。

偽列(pseudocolumn)ROWNUM 中包含有當前的行號。很多人在第一次試圖返回表中中間某段記錄子集時，都會發現下面這種方法行不通:

select * from all_objects where rownum between 30 and 49;

這種寫法之所以行不通，是因為 ROWNUM 列只有在記錄被取出或過濾時才會應用到記錄上。第一行在被取出時會被拋出，因為它的 ROWNUM 是1。然後，下一行被取出;它也會被拋出，因為它是新的“1”，以此類推，直到所有的行都被使用。這個查詢不會返回任何記錄。解決方法是在看到30到50之間的記錄時必須先取出1到30行的記錄:

select * from all_objects where rownum <= 49;

然後，你可以將它作為一個子查詢並過濾掉開始點之前的所有記錄(注意我必須為“rownum”提供一個別名才能編譯):

select * from (select rownumr,all_objects.* from all_objects where rownum <=
49) t
where t.r >= 30;

為了保證它的效率，不妨對這個限制使用綁定變量。這將使用所有此類請求在字面上完全相同，從而消除了在每次請求不同的范圍求時重新解析查詢:

select * from (select rownumr,all_objects.* from all_objects where rownum <=
:min) t
where t.r >= :max;

如果你使用的語言能夠通過存儲過程返回行集，那麼數據庫將自動在內部處理綁定變量。然後，應用程序代碼就會只接收它請求的記錄，而不必決定接收哪些記錄。

create or replace procedure search(p_mininteger,p_maxinteger,p_rowset out
sys_refcursor)
is
begin
select cursor(*) into p_rowset
from (select rownumr,all_objects.* from all_objects where rownum <=
p_max) t
where t.r >= p_min;
end search;
/
show errors

注意 ROWNUM 是在排序之後計算的，所以使用 ORDER BY 子句將得到新的記錄順序。然而，基於規則的優化器用 ROWNUM 來“短路”查詢，並在 ROWNUM 子句被滿足時返回記錄給下一部分查詢。