'Oracle &amp; MS-SQL &amp; Sybase' 카테고리의 글 목록

'Oracle & MS-SQL & Sybase'에 해당되는 글 1건

2012.09.18 oracle 페이징 처리 next 조회

2012. 9. 18. 15:31

oracle 페이징 처리 next 조회

2012. 9. 18. 15:31 in Oracle & MS-SQL & Sybase

예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

예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) )

1. 일반적인 오라클 페이징 처리

SELECT * FROM (
SELECT A.*, 
ROWNUM AS RNUM,
FLOOR((ROWNUM-1)/{디스플레이수}+1) AS PAGE,
COUNT(*) OVER() AS TOTCNT FROM (
{검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}
) A
) WHERE PAGE = {페이지번호};

OR

SELECT * FROM (
SELECT A.*, 
ROWNUM AS RNUM,
COUNT(*) OVER() AS TOTCNT FROM (
{검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}
) A
) WHERE RNUM > {범위부터} AND RNUM <= {범위까지};

2. 오라클 대용량 페이징 처리

SELECT * FROM (
SELECT ROWNUM AS RNUM, A.* FROM (
{검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}
) A WHERE ROWNUM <= {범위까지}
) WHERE RNUM > {범위부터};

OR

SELECT * FROM (
SELECT /*+ INDEX_ASC or INDEX_DESC(A {정렬조건 인덱스명}) */
ROWNUM AS RNUM, A.* FROM (
{검색쿼리 - 정렬이 필요한 경우 정렬조건을 포함하지 않고 ORACLE 힌트사용}
) A WHERE ROWNUM <= {범위까지}
) WHERE RNUM > {범위부터};