在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;
沒有留言:
張貼留言