[SQL][Oracle] WITH RECURSIVE

 

WITH [RECURSIVE] CTE_name AS
	(
		SELECT query (Non Recursive query or the Base query)
			UNION [ALL]
		SELECT query (Recursive query using CTE_name [with  a termination condition])
	)
SELECT * FROM CTE_name;

 

 

WITH CONTINUOUS(NUM, RESULT) AS(
    SELECT 1,1 FROM DUAL
    UNION ALL
    SELECT
        NUM+1
        ,(NUM+1) + RESULT
    FROM CONTINUOUS
    WHERE NUM < 9
)
SELECT
    NUM
    ,RESULT
FROM CONTINUOUS;

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER,
  parent_id NUMBER,
  CONSTRAINT tab1_pk PRIMARY KEY (id),
  CONSTRAINT tab1_tab1_fk FOREIGN KEY (parent_id) REFERENCES tab1(id)
);

CREATE INDEX tab1_parent_id_idx ON tab1(parent_id);

INSERT INTO tab1 VALUES (1, NULL);
INSERT INTO tab1 VALUES (2, 1);
INSERT INTO tab1 VALUES (3, 2);
INSERT INTO tab1 VALUES (4, 2);
INSERT INTO tab1 VALUES (5, 4);
INSERT INTO tab1 VALUES (6, 4);
INSERT INTO tab1 VALUES (7, 1);
INSERT INTO tab1 VALUES (8, 7);
INSERT INTO tab1 VALUES (9, 1);
INSERT INTO tab1 VALUES (10, 9);
INSERT INTO tab1 VALUES (11, 10);
INSERT INTO tab1 VALUES (12, 9);
COMMIT;

SET PAGESIZE 20 LINESIZE 110

WITH t1(id, parent_id) AS (
  -- Anchor member.
  SELECT id,
         parent_id
  FROM   tab1
  WHERE  parent_id IS NULL
  UNION ALL
  -- Recursive member.
  SELECT t2.id,
         t2.parent_id
  FROM   tab1 t2, t1
  WHERE  t2.parent_id = t1.id
)
SELECT id,
       parent_id
FROM   t1;

( https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2 )