Oracle day5

#  WITH절

• 오라클9 이후 버전부터 사용이 가능하며 이름이 부여된 서브쿼리
• 한번 실행할 쿼리문내에 정의되어 있을경우, 그 쿼리문안에서만 실행된다.

SELECT TA.LNACT, TA.LNACT_SEQ, TA.LN_DT, TA.EXP_DT, TA.DLQ_DT, TA.DLQ_CNT, 
       TP.계획이자, TR.입금이자
  FROM (SELECT LNACT, LNACT_SEQ, LN_DT, EXP_DT, DLQ_DT, DLQ_CNT
          FROM TACCT 
         WHERE DLQ_CNT   > 0 
           AND LMT_TYP  IS NULL) TA 
      ,(SELECT LNACT, LNACT_SEQ, SUM(INT_MON_AMT) AS 입금이자
          FROM TREPAY
         WHERE PAY_DT    <= SYSDATE
        GROUP BY LNACT, LNACT_SEQ) TR 
      ,(SELECT LNACT, LNACT_SEQ, SUM(INT_MON_AMT) AS 계획이자
          FROM TREPAY_PLAN
         WHERE PAY_DT    <= SYSDATE
        GROUP BY LNACT, LNACT_SEQ) TP 
 WHERE TA.LNACT     = TR.LNACT 
   AND TA.LNACT_SEQ = TR.LNACT_SEQ 
   AND TA.LNACT     = TP.LNACT 
   AND TA.LNACT_SEQ = TP.LNACT_SEQ
ORDER BY TA.LNACT, TA.LNACT_SEQ;

• 다음과 같이 FROM 절에 사용된 여러번 반복되는 서브 쿼리를  WITH 절로 만들어서 테이블을 사용하듯 사용할 수 있다.

WITH TA AS (SELECT LNACT, LNACT_SEQ, LN_DT, EXP_DT, DLQ_DT, DLQ_CNT
              FROM TACCT 
             WHERE DLQ_CNT   > 0 
               AND LMT_TYP  IS NULL) 
    ,TR AS (SELECT LNACT, LNACT_SEQ, SUM(INT_MON_AMT) AS 입금이자
              FROM TREPAY
             WHERE PAY_DT    <= SYSDATE
            GROUP BY LNACT, LNACT_SEQ)
    ,TP AS (SELECT LNACT, LNACT_SEQ, SUM(INT_MON_AMT) AS 계획이자
              FROM TREPAY_PLAN
             WHERE PAY_DT    <= SYSDATE
            GROUP BY LNACT, LNACT_SE

• 사용예제.

SELECT TA.LNACT, TA.LNACT_SEQ, TA.LN_DT, TA.EXP_DT, TA.DLQ_DT, TA.DLQ_CNT, 
       TP.계획이자, TR.입금이자
  FROM TA 
  JOIN TR 
    ON TA.LNACT     = TR.LNACT 
   AND TA.LNACT_SEQ = TR.LNACT_SEQ 
  JOIN TP 
    ON  TA.LNACT     = TP.LNACT 
    AND TA.LNACT_SEQ = TP.LNACT_SEQ 
ORDER BY 1,2 ;

결과