[오라클] Recursive Subquery Factoring

서브쿼링 팩토링 즉 WITH절로 Recursive(재귀호출)를 구현할 수 있습니다.
이름하여 Recursive Subquery Factoring 입니다.
예제는 아래와 같습니다.

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

WITH t1(id, parent_id, lvl, root_id, path) AS (

  -- Anchor member.

  SELECT id,

         parent_id,

         1 AS lvl,

         id AS root_id,

         TO_CHAR(id) AS path

  FROM   tab1

  WHERE  id = 1

  UNION ALL

  -- Recursive member.

  SELECT t2.id,

         t2.parent_id,

         lvl+1,

         t1.root_id,

         t1.path || '-' || t2.id AS path         -- SYS_CONNECT_BY_PATH

  FROM   tab1 t2, t1

  WHERE  t2.parent_id = t1.id

)

SEARCH DEPTH FIRST BY id SET order1

CYCLE id SET cycle TO 1 DEFAULT 0

SELECT id,

       parent_id,

       RPAD('.', (lvl-1)*2, '.') || id AS tree,

       lvl,

       root_id,

       path,

       cycle

FROM t1

ORDER BY order1;

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

The ordering of the rows is specified using the SEARCH clause, which can use two methods.

BREADTH FIRST BY : Sibling rows are returned before child rows are processed.

DEPTH FIRST BY : Child rows are returned before siblings are processed.

출처: https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2#cyclic