Lehekülgede pagineerimine ORACLE-s ühe SELECT lause kasutamisega ainult

Eriti REST rakenduste puhul tuleb sageli pagineerimine teha andmebaasi poolel.

Läbi tuleb teha selleks kaks etappi
1. Saada teade kirjete koguarv
2. Võtta vajalik vahemik välja

ORACLE WITH konstruktsiooni tehes saab selle tehe ühe SELECT lausega ainult, mis juhtudel, kui SELECT lause ise võtab kaua aega annab märgatava ajalise kokkuhoiu

Näide, kus annad ette lehekülje suuruse ja mitmendat lehekülge tahad

SET SERVEROUTPUT ON
DECLARE i_page_size INTEGER;  --lehekülje suurus
i_page_no INTEGER; --mitme lehekülg kuvada
BEGIN
i_page_size:=5;
i_page_no:=2;
FOR ss IN (
 WITH Tu AS
        (  SELECT ROWNUM AS jrk, dde.ID, dde.SUBJECT_ID, dde.OCCUP_NAME
                , CEIL((ROWNUM)/i_page_size) AS PAGE_NUMBER --arvutab lehekülje numbri
            FROM
            (  SELECT ID, SUBJECT_ID, OCCUP_NAME FROM SUB_EMPLOYER ORDER BY OCCUP_NAME DESC ) dde
     )       
     SELECT Tu.jrk, Tu.ID, Tu.SUBJECT_ID, Tu.OCCUP_NAME,Tu.PAGE_NUMBER
       ,(SELECT MAX(Tu.jrk) FROM Tu ) AS kokku_kirjeid
       FROM Tu
         WHERE Tu.PAGE_NUMBER = i_page_no  --pagineerimine
         ORDER BY Tu.jrk)
LOOP
    DBMS_OUTPUT.PUT_LINE ( SS.JRK || ' ' || ss.OCCUP_NAME || ' ' || ss.SUBJECT_ID  || ' ' || ss.kokku_kirjeid);
END LOOP;
END;

WITH konstruktsiooni osa, saab kätte kirje järjekorra numbri ja CEIL funktsiooniga arvutab lehekülje numbri

ROWNUM AS jrk, CEIL((ROWNUM)/i_page_size) AS PAGE_NUMBER
      
 hilisemas välises päringus saab eraldi tulbast kätte ka kirjete koguarvu
(SELECT MAX(Tu.jrk) FROM Tu ) AS kokku_kirjeid



Mõnikord vaja saada lisaks eraldi teada ka kirjete ja lehekülgede koguarv siis kasutab CURSOR-i kuhu lisab ette esimeseks lehekülgede ja ridade koguarvuga rea ning FETCH-ib selle välja enne tagastamist. Esimese rea väljatõmbamisel saad lehekülgede ja kirjete koguarvu teada
o_totalPages:=nvl(ceil(o_totalRows/i_page_size),0); --arvutame lehekülgede arvu
o_totalRows:=nvl(o_totalRows,0); --nvl et saaks ridade puudumisel number 0-i välja



SET SERVEROUTPUT ON
DECLARE i_page_size INTEGER;  --lehekülje suurus
i_page_no INTEGER; --mitmes lehekülg kuvada
o_cursor sys_refcursor;  --siia paneme tulemused
o_totalPages INTEGER; --mitu lehekülge kokku
o_totalRows INTEGER; --mitu rida kokku

v_int integer; --abimuutuja
v_int2 integer; --abimuutuja
v_lehekulg integer; --abimuutuja
v_subject_id varchar2(8000); --abimuutuja
v_occup_name varchar2(8000); --abimuutuja
BEGIN
i_page_size:=5;
i_page_no:=2;

open o_cursor for
 WITH Tu AS
        ( 
        SELECT dde2.jrk, dde2.ID, dde2.SUBJECT_ID, dde2.OCCUP_NAME, dde2.PAGE_NUMBER
        FROM (
        SELECT ROWNUM AS jrk, dde.ID, dde.SUBJECT_ID, dde.OCCUP_NAME
                , CEIL((ROWNUM)/i_page_size) AS PAGE_NUMBER --arvutab lehekülje numbri
            FROM
            (  SELECT ID, SUBJECT_ID, OCCUP_NAME FROM SUB_EMPLOYER ORDER BY OCCUP_NAME DESC ) dde ) dde2
     )       
     SELECT Tu.jrk, Tu.ID, Tu.SUBJECT_ID, Tu.OCCUP_NAME,Tu.PAGE_NUMBER
       ,(SELECT MAX(Tu.jrk) FROM Tu ) AS kokku_kirjeid
       FROM Tu
         WHERE Tu.PAGE_NUMBER = i_page_no  --pagineerimine
       
    UNION --tekitame juurde esimese rea, sellekaudu saab fetch ridade koguarvu teada väljastuseks
        SELECT 0 as jrk, Tu.ID, Tu.SUBJECT_ID, Tu.OCCUP_NAME,Tu.PAGE_NUMBER
       ,(SELECT MAX(Tu.jrk) FROM Tu ) AS kokku_kirjeid FROM Tu WHERE Tu.jrk =1
         ORDER BY 1;  --et 0 as jrk rida esimeseks tuleks, mille FETCH-iga välja võtame
        
    --Esimese rea kursorist võib ära raisata, küsib sealt lehekülgede koguarvu ja kirjete koguarvu
     FETCH o_cursor INTO v_int, v_int2, v_subject_id, v_occup_name, v_lehekulg,o_totalRows;
             o_totalPages:=nvl(ceil(o_totalRows/i_page_size),0); --arvutame lehekülgede arvu
             o_totalRows:=nvl(o_totalRows,0); --nvl et saaks ridade puudumisel number 0-i välja
     IF o_totalRows=0 THEN   --anname tühja kursori välja, kui kirjeid üldse pole, tuleb viga ORA-01002: fetch out of sequence
        CLOSE o_cursor;
        OPEN o_cursor FOR
         SELECT 0 AS jrk , 0 AS ID, '' AS SUBJECT_ID, '' AS OCCUP_NAME, 0 AS PAGE_NUMBER, 0 AS kokku_kirjeid FROM dual;
     END IF;
     DBMS_OUTPUT.PUT_LINE ( 'Kokku kirjeid: ' || o_totalRows  || '   Kokku lehekülgi: ' || o_totalPages);
    
    
     LOOP
          FETCH o_cursor INTO  v_int, v_int2, v_subject_id, v_occup_name, v_lehekulg,o_totalRows;
             EXIT WHEN o_cursor%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE ('Jrk: ' ||  v_int || ' ' || v_occup_name || ' ' || v_subject_id);
     END LOOP;
    
END;

Konstruktsioon 
.. UNION .. WHERE Tu.jrk =1 .. ORDER BY 1  teeb selle, et "tühi rida" mille võib ära visata tekib CURSOR-isse esimeseks, kust loeb välja kirjete ja lehekülgede koguarvu.

Kui kirjeid pole IF o_totalRows=0 THEN siis tekitab tühja üherealise CURSOR-i REST kliendi jaoks juhuks kui REST kliendile ei meeldi, et andmebaasist mitte kui midagi ei tagastata.