Oracle Paging with OVER()
오라클 페이징 예제
SELECT * FROM
(
SELECT T1.*,
ROWNUM AS RN,
FLOOR((ROWNUM-1)/[페이지당 출력 레코드 수]+1) AS PAGE,
COUNT(*) OVER() AS TTCNT FROM
(
SELECT .....
) T1
)
WHERE PAGE = [페이지번호];
EMP 테이블에 페이징과 계층구조 커리를 결합한 예
SELECT * FROM
(
SELECT T1.*,
ROWNUM AS RN,
FLOOR((ROWNUM-1)/3+1) AS PAGE, /* 한 페이지에 3행씩 보여주는 경우 */
COUNT(*) OVER() AS TTCNT FROM /* 테이블에 저장된 총 라인(행) 수 */
(
select empno, LPAD('┗━', 4*(LEVEL-1), ' ') || ename 이름, LEVEL from emp
START WITH mgr is null /* ename='KING' */
CONNECT BY PRIOR empno=mgr
) T1
)
WHERE PAGE = 1; /* 1 페이지를 보여주려고 하는 경우 */
/*특수공백문자(ㄱ 의 1번 특수문자)
알파벳 L 자와 비슷한 특수문자 (ㅂ 의 6번 특수문자)
*/
위의 문장과 연동하여 사용할 수 있는 페이지 네비게이션의 예는 여기를 참고하세요
단일행 대상 작업과 복수행 대상 작업을 함께 수행할 경우 오류가 발생하는 예
SQL> select ename, sal, rownum || '/' || count(*) from emp;
select ename, sal, rownum || '/' || count(*) from emp
*
1행에 오류:
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
위의 문장은 한번에 한행을 대상으로 컬럼의 값을 선택하여 가져오는 기능을 하는데
한행을 선택할 때 복수행을 대상으로 검사하여 값을 산출하는 count(*) 함수가 사용되고 있으므로
위의 문장은 오류를 발생한다.
즉, 단일행을 대상으로 하는 작업에는 복수행을 대상으로 한 작업을 함께 할 수 없다는 것이다.
위와 같은 단점을 해결하기 위해서 오라클에서는 OVER() 함수를 제공한다
OVER()함수를 복수행 함수와 함께 사용하면 전체행을 대상으로 검사한다
아래와 같은 방법으로 단일행 대상 작업을 하면서 복수행 대상 작업을 동시에 할 수 있다.
SQL> select ename, sal, rownum || '/' || count(*) over() from emp;
MIN, MAX, AVG, SUM, STDDEV 등의 함수도 OVER()와 함께 사용할 수 있다.
CEIL(), FLOOR() 함수의 용법
SQL : SELECT CEIL(123.456) FROM DUAL;
결과 : 124
SQL : SELECT FLOOR(123.456) FROM DUAL;
결과 : 123
EMP 테이블을 대상으로 한 페이지당 3개의 레코드를 보여주려고 할 때 2 페이지를 가져오는 예
SQL> select rn, empno, ename from
2 (
3 select
4 T1.*,
5 rownum rn,
6 FLOOR((rownum-1)/3+1) as page,
7 COUNT(*)OVER() as TTCNT from
8 (
9 select * from emp order by empno
10 ) T1
11 )
12 where page=2;
RN EMPNO ENAME
---------- ---------- --------------------
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
SQL>
특정 페이지에 해당하는 레코드와 함께 현재 페이지번호, 총 페이지번호를 출력하는 예
SQL> select rn, empno, ename, page, ttpage from
2 (
3 select
4 T1.*,
5 rownum rn,
6 FLOOR((rownum-1)/3+1) as page,
7 FLOOR((TTCNT-1)/3+1) as TTPAGE from
8 (
9 select e.*,COUNT(*)OVER() as TTCNT from emp e order by empno
10 ) T1
11 )
12 where page=2;
RN EMPNO ENAME PAGE TTPAGE
---------- ---------- -------------------- ---------- ----------
4 7566 JONES 2 4
5 7654 MARTIN 2 4
6 7698 BLAKE 2 4
SQL>
행번호를 이용하여 해당 페이지를 확인하는 계산식 설명
13 행을 가진 목록이 있을 때 3행씩 나누어서 한페이지에 보여주고 한다.
각 행의 행번호를 구할 수 있다면 그 행번호를 이용하여 몇 페이지에 속하는지 확인하는 계산을 알아본다
1~3 : Page 1
4~6 : Page 2
7~9 : Page 3
10~12 : Page 4
13 : Page 5
ROWNUM 을 이용하여 특정 행이 어떤 페이지에 속하는 계산하는 예
예를 들어 10~12행이 Page 4에 속한다는 사실을 계산하는 예
다음과 같이 행번호를 3으로 나눈 결과를 사용하면 정확한 페이지를 구할 수 없다
9/3 -> 3, 나머지 0
10/3 -> 3, 나머지 1
11/3 -> 3, 나머지 2
12/3 -> 4, 나머지 0
위의 결과에서 알 수 있듯이 해당 행번호 - 1 을 계산한 결과를 3으로 나누고 나눈 결과에 1을 더해주면 4 를 얻을 수 있다
그러므로 다음과 같은 계산식을 생각해 볼 수 있다
( ( 행번호-1 ) / 페이지당 행수 ) + 1
계산식 테스트
한 페이지에 3행씩 보여줄 때 11행과 12행은 각각 몇 페이지에 속하는가?
((11-1)/3) + 1
= (10/3) + 1
= (3) + 1
= 4
((12-1)/3) + 1
= (11/3) + 1
= (3) + 1
= 4