[oracle] 버전별 페이징 쿼리 (mybatis 예시)

1. 12g 이전 버전

  SELECT
    ROW_NUM,
    LOG_NO,
    SVC_NM,
    DB_NM,
    TBL_NM,
    PROCDR_NM,
    CALL_URL,
    CALL_MTHD_SP_VAL,
    CALL_PARA_VAL,
    EXEC_TME,
    LOG_DESC,
    INPT_DM
  FROM (
    SELECT
      ROW_NUMBER () OVER (ORDER BY INPT_DM DESC) AS ROW_NUM,
      LOG_NO,
      SVC_NM,
      DB_NM,
      TBL_NM,
      PROCDR_NM,
      CALL_URL,
      CALL_MTHD_SP_VAL,
      CALL_PARA_VAL,
      EXEC_TME,
      LOG_DESC,
      INPT_DM
    FROM TB_CM_OP_L
  ) WHERE ROW_NUM BETWEEN ((NVL(1, 0)-1) * 10 + 1) AND (NVL(1, 0) * 10);
  -- mybatis 페이징 처리
  -- ROW_NUM BETWEEN ((NVL(#{page}, 0)-1) * #{size} + 1) AND (NVL(#{page}, 0) * #{size});  

 

2. 12g 이후 버전

    SELECT
        ROW_NUMBER () OVER (ORDER BY INPT_DM DESC) AS ROW_NUM,
        LOG_NO,
        SVC_NM,
        DB_NM,
        TBL_NM,
        PROCDR_NM,
        CALL_URL,
        CALL_MTHD_SP_VAL,
        CALL_PARA_VAL,
        EXEC_TME,
        LOG_DESC,
        INPT_DM
    FROM TB_CM_OP_L
    OFFSET #{offset} ROWS FETCH NEXT #{limit} ROWS ONLY;