網頁

▼

2017/9/13

Oracle 分頁查詢

在Oracle分頁(pagination)查詢的SQL如下

SELECT * FROM (
  SELECT rownum rn, t.*, 
  FROM (
    SELECT * FROM table_name 
    ORDER BY column_name -- 原本的SQL
  ) t 
  WHERE rn < ((頁數 * 每頁筆數) + 1) -- 每頁的結束筆數
)
WHERE rn >= (((頁數 - 1) * 每頁筆數) + 1) -- 每頁的開始筆數

頁數為所在頁數，例如第2頁。
每頁筆數為每一頁要顯示的筆數，例如一頁顯示20筆。

頁數的值通常是從前端UI用Ajax送來，每頁筆數則可能設定在資料庫，或在前端讓使用者直接設定。

所以如果每一頁顯示20筆，查詢第2頁的資料的SQL如下。

SELECT * FROM (
  SELECT rownum rn, t.*, 
  FROM (
    SELECT * FROM table_name 
    ORDER BY column_name -- 原本的SQL
  ) t 
  WHERE rn < ((2 * 20) + 1) -- 每頁的結束筆數 rn < 41
)
WHERE rn >= (((2 - 1) * 20) + 1) -- 每頁的開始筆數 rn >= 21

所以上面是查詢第21筆到第40筆資料。

也可改寫如下，但在效能上有點差異。

SELECT * FROM (
  SELECT rownum rn, t.*, 
  FROM (
    SELECT * FROM table_name 
    ORDER BY column_name -- 原本的SQL
  ) t 
)
WHERE rn BETWEEN (((頁數 - 1) * 每頁筆數) + 1) AND (頁數 * 每頁筆數);

在Oracle 12c(12.1.0.1)版本之後，新增了Native SQL Support for Query Row Limits and Row Offsets特色，可以用OFFSET n ROWS FETCH NEXT m ROWS ONLY語法來達成分頁查詢。

下面範例只會查出n + 1筆 到 n + 1 + m 筆資料

SELECT * FROM table_name 
ORDER BY column_name 
OFFSET n ROWS FETCH NEXT m ROWS ONLY;

查詢分頁的話，則為

SELECT * FROM table_name 
ORDER BY column_name 
OFFSET ((頁數 * 每頁筆數) + 1) ROWS FETCH NEXT 每頁筆數 ROWS ONLY;

在Oracle Stored Procedure使用paging範例。

-- 預存程序 body
/*
*查詢員工申請記錄
*@param i_apply_no 申請記錄編號
*@param i_start_row 起始筆數
*@param i_per_page_count 每頁筆數
*@param o_total_count 查詢總筆數
*@param o_cur 查詢結果cursor(result set) 
*/
PROCEDURE SP_GET_APPLY_RECORD_BY_NO (
    i_apply_no IN VARCHAR2, 
    i_start_row IN NUMBER, 
    i_per_page_count IN NUMBER, 
    o_total_count OUT NUMBER, 
    o_cur OUT SYS_REFCURSOR) 
AS 
BEGIN

    SELECT COUNT(*) INTO o_total_count
    FROM APPLY_RECORD r 
    INNER JOIN EMPLOYEE e ON e.EMPLOYEE_ID = r.EMPLOYEE_ID
    WHERE r.APPLY_NO = i_apply_no;
    
    OPEN o_cur FOR
    SELECT * 
    FROM APPLY_RECORD r
    INNER JOIN EMPLOYEE e ON e.EMPLOYEE_ID = r.EMPLOYEE_ID
    WHERE r.APPLY_NO = i_apply_no
    ORDER BY r.APPLY_NO
    OFFSET i_start_row ROWS FETCH NEXT i_per_page_count ROWS ONLY;

END SP_GET_APPLY_RECORD_BY_NO;


沒有留言:

張貼留言