오라클 with 간단 설명 및 예제 (With... Select)

 

반복되는 쿼리를 공용으로 사용하는 것이 View 지요!

그런데, View 를 사용하지 않고, Query 구문에서 바로 사용하고 싶은 경우, Oracle에서 WITH 를 사용합니다.

아래의 예 처럼 WITH 구문에 View명칭을 지정하고 ()안에 해당 쿼리를 작성하면,

바로 아래에 Select 구문에서 View 처럼 사용할 수 있습니다.

 

--예 1.)
WITH Temp1 AS (SELECT 'A' as Col1, 'B' as Col2 FROM dual), 
         Temp2 AS (SELECT 'C' as Col3, 'D' as Col4 FROM dual) 
SELECT  Col1,Col2,Col3,Col4 FROM Temp1, Temp2;
 
 
 
--예 2.)
 
WITH Temp AS (Select 'A' as Col1 from dual
                          union 
                          Select 'B' from dual
                          union
                          Select 'C' from dual
                      )
SELECT * FROM Temp 
 
 
 
--예 3.)
 
WITH Temp1 AS (SELECT 'A' as Col1, 'a' as Col2 FROM dual
                         UNION
                         SELECT 'B' as Col1, 'b' as Col2 FROM dual),
         Temp2 AS (SELECT 'A' as Col1, '1' as Col3, 'y' as Col4 FROM dual
                         UNION
                         SELECT 'B' as Col1, '2' as Col3, 'z' as Col4 FROM dual) 
SELECT  A.Col2, B.Col4
  FROM Temp1 A INNER JOIN Temp2 B
                                     ON A.Col1 = B.Col1
 WHERE A.Col1 = 'A'