오라클 9i 이상 경우 계층형 쿼리 함수 "CONNECT_BY_ROOT"를 이용하시면 됩니다.
http://www.gurubee.net/lecture/1903
WITH T(seq, re_seq) AS ( SELECT 1, NULL FROM DUAL UNION ALL SELECT 2, 1 FROM DUAL UNION ALL SELECT 3, 2 FROM DUAL UNION ALL SELECT 4, NULL FROM DUAL UNION ALL SELECT 5, NULL FROM DUAL UNION ALL SELECT 6, 5 FROM DUAL UNION ALL SELECT 7, NULL FROM DUAL UNION ALL SELECT 8, 7 FROM DUAL UNION ALL SELECT 9, 7 FROM DUAL UNION ALL SELECT 10, 9 FROM DUAL ) SELECT seq, re_seq, LEVEL, CONNECT_BY_ROOT seq AS root_seq FROM T START WITH re_seq IS NULL CONNECT BY PRIOR seq = re_seq
WITH bbs AS ( SELECT 1 seq, NULL re_seq FROM dual UNION ALL SELECT 2, 1 FROM dual UNION ALL SELECT 3, 2 FROM dual UNION ALL SELECT 4, NULL FROM dual UNION ALL SELECT 5, NULL FROM dual UNION ALL SELECT 6, 5 FROM dual UNION ALL SELECT 7, NULL FROM dual UNION ALL SELECT 8, 7 FROM dual UNION ALL SELECT 9, 7 FROM dual UNION ALL SELECT 10, 9 FROM dual ) SELECT seq FROM bbs WHERE CONNECT_BY_ISLEAF = 1 START WITH seq = :v_seq CONNECT BY seq = PRIOR re_seq ;