-------------------------------------------------------------------------------------

WITH RECURSIVE REC_TEMP(LEVEL) AS

(

 SELECT 1 AS LEVEL

   FROM DUAL

  UNION ALL

 SELECT LEVEL + 1

   FROM REC_TEMP RT

  WHERE 1 = 1

    AND LEVEL < 100

)

SELECT *

  FROM REC_TEMP

 WHERE 1 = 1

;

-- 하위조직을 찾는데 1개씩 있는조직이다. 1:N 이 아님

WITH RECURSIVE INCLUDED_PARTS(SUB_PART, PART, QUANTITY) AS

(

SELECT SUB_PART

     , PART

     , QUANTITY

  FROM PARTS

 WHERE 1 = 1

   AND PART = 'OUR_PRODUCT'

 UNION ALL

SELECT P.SUB_PART

     , P.PART

     , P.QUANTITY

  FROM INCLUDED_PARTS PR

     , PARTS P

 WHERE 1 = 1

   AND P.PART = PR.SUB_PART

)

SELECT SUB_PART

     , SUM(QUANTITY) AS TOTAL_QUANTITY

  FROM INCLUDED_PARTS

 GROUP BY SUB_PART

;

-- 노 cycle 옵션을 주기

WITH RECURSIVE SEARCH_GRAPH(ID, LINK, DATA, DEPTH, PATH, CYCLE) AS

(

 SELECT G.ID

      , G.LINK

      , G.DATA

      , 1

      , ARRAY[G.ID]

      , FALSE

    FROM GRAPH G

   WHERE 1 = 1

   UNION ALL

  SELECT G.ID

       , G.LINK

       , G.DATA

       , SG.DEPTH + 1

       , PATH || G.ID

       , G.ID = ANY(PATH)

    FROM GRAPH G

       , SEARCH_GRAPH SG

   WHERE 1 = 1

     AND G.ID = SG.LINK

     AND NOT CYCLE

)

SELECT *

  FROM SEARCH_GRAPH

 WHERE 1 = 1

;

-- 두개 이상의 컬럼으로 NO CYCLE 체크하기

WITH RECURSIVE SEARCH_GRAPH(ID, LINK, DATA, DEPTH, PATH, CYCLE) AS

(

SELECT G.ID

     , G.LINK

     , G.DATA

     , 1

     , ARRAY[ROW(G.F1, G.F2)]

     , FALSE

  FROM GRAPH G

 WHERE 1 = 1

 UNION ALL

SELECT G.ID

     , G.LINK

     , G.DATA

     , SG.DEPTH + 1

     , PATH || ROW(G.F1, G.F2)

     , ROW(G.F1, G.F2) = ANY(PATH)

  FROM GRAPH G

     , SEARCH_GRAPH SG

 WHERE 1 = 1

   AND G.ID = SG.LINK

   AND NOT CYCLE

)

SELECT *

  FROM SEARCH_GRAPH

 WHERE 1 = 1