ORACLE WITH AS 사용방법

ORACLE WITH AS 사용방법 - 1 

-- [WITH 구문 사용방법]
 
WITH AA AS
 (SELECT ROWNUM, 'TEST1', SYSDATE
    FROM DUAL
  UNION ALL
  SELECT ROWNUM, 'TEST2', SYSDATE
    FROM DUAL
  UNION ALL
  SELECT ROWNUM, 'TEST3', SYSDATE FROM DUAL)
SELECT * FROM AA;

출력 결과

ROWNUM	'TEST1'	SYSDATE
1	TEST1	2017-07-25 오후 6:14:08
1	TEST2	2017-07-25 오후 6:14:08
1	TEST3	2017-07-25 오후 6:14:08

ORACLE WITH AS 사용방법 - 2 

 -- [WITH 구문(2개 SUB쿼리) 사용방법]
 
 WITH AA AS (SELECT ROWNUM AS SEQ
                             , 'TEST1' AS NAME
                             , SYSDATE 
                      FROM DUAL),
          BB AS (SELECT ROWNUM AS SEQ
                              , 'TEST2' AS NAME
                              , SYSDATE
                       FROM DUAL)  
         SELECT * 
           FROM AA, BB 
          WHERE AA.SEQ = BB.SEQ 

출력 결과

SEQ	NAME	SYSDATE	SEQ_1	NAME_1	SYSDATE_1
1	TEST1	2017-07-25 오후 6:16:53	1	TEST2	2017-07-25 오후 6:16:53