WITH문 : 쿼리를 뷰처럼 재사용

■ WITH문 : 쿼리를 뷰처럼 재사용 ->  WITH name AS ( 쿼리 )
 
1.기본
WITH viewData AS
(
  SELECT *
    FROM TestTable_1
)
  SELECT *
    FROM viewData;
 
 
2.WITH문에 다중 쿼리를 사용하는 CASE
WITH viewData1 AS
(
  SELECT *
    FROM TestTable_1
),
viewData2 AS
(
  SELECT *
    FROM TestTable_2
)
  SELECT V1.id,
         V2.name,
         V2.address
    FROM viewData1 V1,
         viewData2 V2
   WHERE V1.id = V2.id
     AND V1.name LIKE 'H%';
 
3.여러 테이블을 뷰 처럼 활용하는 CASE
WITH viewData1 AS
(
    SELECT *
       FROM TestTable_1
),
viewData2 AS  
(
    SELECT *
        FROM TestTable_2
),
viewData3 AS
(
    SELECT V1.id,
           V2.name,
           V2.address
      FROM viewData1 V1,
           viewData2 V2
     WHERE V1.id = V2.id
       AND V1.name LIKE 'H%'
)
SELECT *
  FROM viewData3
 WHERE ROWNUM <= 10;