It was a Sunday morning. An early Sunday morning. I was waiting for my home wake up. So I looked again to my uncompleted task: how to paginate my recordset with Oracle DB (11c...).
I was inspiring to found a solution without using subselect.
I tried to avoid using subselect because I consider it not so readable (I found a lot of example with it). I've found my way using WITH
statement.
The WITH
statement permits to have a clean and simple SQL code.
This is the result.
WITH RECORDSET AS (
-- put here your select with the complete recordset.
SELECT FIELDA, FIELDB, FIELDC FROM TABLE
),
NUMBERED AS (
SELECT
ROW_NUMBER() OVER (ORDER BY FIELDA) RN,
RECORDSET.*
FROM RECORDSET)
SELECT
-- page number parameter
:page_number PAGE_NUMBER,
-- total recordset pages
CEIL((SELECT COUNT(*) FROM NUMBERED) / :page_size) TOTAL_PAGES,
-- page size parameter
:page_size PAGE_SIZE,
-- total rows
(SELECT COUNT(*) FROM NUMBERED) TOTAL_ROWS,
NUMBERED.*
FROM NUMBERED
WHERE
RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)
This code ask for two parameter: :page_size
of your recordset and :page_number
you want retrive.
The first fields contain pagination data: PAGE_NUMBER
, TOTAL_ROWS
, PAGE_SIZE
and TOTAL_ROWS
.
As well as I consider this a clean way to have a paginated recordset, I found it also very performing.
Top comments (1)
How do you think about new function of Oracle DB: OFFSET...FETCH...?