[Oracle] 페이징 처리용 ROWNUM 예제 (WITH~AS 로 가상테이블 생성 예제도 함께)

WITH TEST AS(
    SELECT 1 SEQ, 'Y' FLAG FROM DUAL UNION ALL
    SELECT 2, 'Y' FROM DUAL UNION ALL
    SELECT 3, 'Y' FROM DUAL UNION ALL
    SELECT 4, 'N' FROM DUAL UNION ALL
    SELECT 5, 'N' FROM DUAL UNION ALL
    SELECT 6, 'N' FROM DUAL UNION ALL
    SELECT 7, 'N' FROM DUAL UNION ALL
    SELECT 8, 'N' FROM DUAL UNION ALL
    SELECT 9, 'Y' FROM DUAL UNION ALL
    SELECT 10, 'Y' FROM DUAL UNION ALL
    SELECT 11, 'Y' FROM DUAL UNION ALL
    SELECT 12, 'Y' FROM DUAL UNION ALL
    SELECT 13, 'Y' FROM DUAL UNION ALL
    SELECT 14, 'N' FROM DUAL UNION ALL
    SELECT 15, 'Y' FROM DUAL UNION ALL
    SELECT 16, 'N' FROM DUAL UNION ALL
    SELECT 17, 'Y' FROM DUAL UNION ALL
    SELECT 18, 'N' FROM DUAL UNION ALL
    SELECT 19, 'Y' FROM DUAL UNION ALL
    SELECT 20, 'Y' FROM DUAL
)
SELECT ot.*
FROM (SELECT   ROWNUM rowcnt
                ,TEST.*
            FROM TEST
           WHERE flag = 'N' AND ROWNUM <= 7
        ORDER BY seq) ot
WHERE rowcnt >= 2

-- <= 쪽 부등호를 >= 쪽과 같은 위상에서
SELECT ot.*
FROM (SELECT   ROWNUM rowcnt
                ,TEST.*
            FROM TEST
           WHERE flag = 'N'
        ORDER BY seq) ot
WHERE rowcnt >= 2 AND rowcnt <= 7

-- ROW_NUMBER() 사용
SELECT ot.*
FROM (SELECT   ROW_NUMBER () OVER (ORDER BY SEQ) rowcnt
                ,TEST.*
            FROM TEST
           WHERE flag = 'N'
        ORDER BY seq) ot
WHERE rowcnt >= 2 AND rowcnt <= 7

* rowcnt 는 임의로 정한 칼럼 이름임