예1>
SELECT CORP_CD
, CTR_NO
, DED_DGRE
, DEMD_DT
, DEMD_TN
, DEMD_CUS_NO
, CASE
WHEN (TCNT - (:l_PagNo - 1) * :l_InqCnt) > :l_InqCnt THEN 'Y'
ELSE 'N'
END NEXT_TR_EXS_YN
FROM (SELECT B.*
, ROWNUM AS SRC_RSLT_CNT
FROM (SELECT CORP_CD
, CTR_NO
, DED_DGRE
, DEMD_DT
, DEMD_TN
, DEMD_CUS_NO
, MAX(ROWNUM) OVER(
ORDER BY ROWNUM DESC) AS TCNT
FROM DMM1101
WHERE CORP_CD = 'A01'
AND DEMD_DT BETWEEN '20131101' AND '20131231'
AND DEMD_STC = 'C9540'
ORDER BY CTR_NO, DED_DGRE, DEMD_DT, DEMD_TN, DEMD_CUS_NO ) B
WHERE ROWNUM <= :l_PagNo * :l_InqCnt )
WHERE SRC_RSLT_CNT >= (:l_PagNo - 1) * :l_InqCnt + 1
, CTR_NO
, DED_DGRE
, DEMD_DT
, DEMD_TN
, DEMD_CUS_NO
, CASE
WHEN (TCNT - (:l_PagNo - 1) * :l_InqCnt) > :l_InqCnt THEN 'Y'
ELSE 'N'
END NEXT_TR_EXS_YN
FROM (SELECT B.*
, ROWNUM AS SRC_RSLT_CNT
FROM (SELECT CORP_CD
, CTR_NO
, DED_DGRE
, DEMD_DT
, DEMD_TN
, DEMD_CUS_NO
, MAX(ROWNUM) OVER(
ORDER BY ROWNUM DESC) AS TCNT
FROM DMM1101
WHERE CORP_CD = 'A01'
AND DEMD_DT BETWEEN '20131101' AND '20131231'
AND DEMD_STC = 'C9540'
ORDER BY CTR_NO, DED_DGRE, DEMD_DT, DEMD_TN, DEMD_CUS_NO ) B
WHERE ROWNUM <= :l_PagNo * :l_InqCnt )
WHERE SRC_RSLT_CNT >= (:l_PagNo - 1) * :l_InqCnt + 1
예2>
SELECT COC_DT || CTR_NO || COC_SEQ AS NEXT_INQ_RQS_LTL
, A.*
FROM AWM1101 A
WHERE 1=1
AND CORP_CD = 'A01'
AND DED_DGRE = '0001'
AND CTR_NO LIKE '%LC%'
AND ( COC_DT >= :COC_DT_NEXT
AND NOT ( COC_DT = :COC_DT_NEXT AND CTR_NO < :CTR_NO_NEXT)
AND NOT ( COC_DT = :COC_DT_NEXT AND CTR_NO = :CTR_NO_NEXT AND COC_SEQ < :COC_SEQ) )
ORDER BY COC_DT , CTR_NO, COC_SEQ
예3>
SELECT *
FROM (
SELECT ROWNUM AS RNUM
, A.*
FROM (
SELECT
CORP_CD
, CTR_NO
, DED_DGRE
, DEMD_DT
, DEMD_TN
, FLOOR((ROWNUM-1)/:PAGESIZE+1) AS PAGE
, COUNT(*) OVER() AS TOTCNT
FROM EXD1060
WHERE 1=1
AND CORP_CD = 'A01'
AND CTR_NO = '211LR110477'
AND DED_DGRE = '0001'
ORDER BY CORP_CD , CTR_NO, DED_DGRE, DEMD_DT
) A WHERE ROWNUM <= (((:PAGENO * 1) * :PAGESIZE))
)
WHERE RNUM > (((:PAGENO - 1) * :PAGESIZE) )
FROM (
SELECT ROWNUM AS RNUM
, A.*
FROM (
SELECT
CORP_CD
, CTR_NO
, DED_DGRE
, DEMD_DT
, DEMD_TN
, FLOOR((ROWNUM-1)/:PAGESIZE+1) AS PAGE
, COUNT(*) OVER() AS TOTCNT
FROM EXD1060
WHERE 1=1
AND CORP_CD = 'A01'
AND CTR_NO = '211LR110477'
AND DED_DGRE = '0001'
ORDER BY CORP_CD , CTR_NO, DED_DGRE, DEMD_DT
) A WHERE ROWNUM <= (((:PAGENO * 1) * :PAGESIZE))
)
WHERE RNUM > (((:PAGENO - 1) * :PAGESIZE) )
1. 일반적인 오라클 페이징 처리
01.
SELECT
*
FROM
(
02.
SELECT
A.*,
03.
ROWNUM
AS
RNUM,
04.
FLOOR((ROWNUM-1)/{디스플레이수}+1)
AS
PAGE,
05.
COUNT
(*) OVER()
AS
TOTCNT
FROM
(
06.
{검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}
07.
) A
08.
)
WHERE
PAGE = {페이지번호};
09.
10.
OR
11.
12.
SELECT
*
FROM
(
13.
SELECT
A.*,
14.
ROWNUM
AS
RNUM,
15.
COUNT
(*) OVER()
AS
TOTCNT
FROM
(
16.
{검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}
17.
) A
18.
)
WHERE
RNUM > {범위부터}
AND
RNUM <= {범위까지};
2. 오라클 대용량 페이징 처리
01.
SELECT
*
FROM
(
02.
SELECT
ROWNUM
AS
RNUM, A.*
FROM
(
03.
{검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}
04.
) A
WHERE
ROWNUM <= {범위까지}
05.
)
WHERE
RNUM > {범위부터};
06.
07.
OR
08.
09.
SELECT
*
FROM
(
10.
SELECT
/*+ INDEX_ASC
or
INDEX_DESC(A {정렬조건 인덱스명}) */
11.
ROWNUM
AS
RNUM, A.*
FROM
(
12.
{검색쿼리 - 정렬이 필요한 경우 정렬조건을 포함하지 않고 ORACLE 힌트사용}
13.
) A
WHERE
ROWNUM <= {범위까지}
14.
)
WHERE
RNUM > {범위부터};