Kongzz' blog

Oracle 재귀Query

재귀Query의 시작은 Start With 라는 문장으로

C:\>sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on 금 9월 5 17:18:58 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

다음에 접속됨:
Oracle Database 11g Release 11.2.0.1.0 - Production

SQL> set line 150
SQL> set pagesize 200
SQL> col level format 99
SQL> col first_name format a12
SQL> col last_name format a12
SQL> col pr_emp format 999
SQL> col manager_id format 999
SQL> col cheif format a10
SQL> col isLeaf format 9
SQL> col path format a30
SQL>
SQL> select level, employee_id, first_name, last_name, prior employee_id pr_emp, manager_id,
  2          connect_by_root last_name cheif,
  3          connect_by_isleaf isLeaf,
  4          sys_connect_by_path(last_name, '/') path
  5     from employees
  6    start with manager_id is null
  7    connect by prior employee_id = manager_id
  8    order siblings by last_name;   (선택)

* 3 : 최종 노드인 경우 : 1

* 4 : 최초에서 최종까지를 구분자를 넣어서 표시를 해 줌

* 7 : connect by에서 나의 manager_id라는 값과 나의 선행에 해당하는 employee_id(prior employee_id) 값과 동일한 값

* 8 : 재귀 쿼리 내에서만 정렬됨 

LEVEL EMPLOYEE_ID FIRST_NAME   LAST_NAME    PR_EMP MANAGER_ID CHEIF      ISLEAF PATH
----- ----------- ------------ ------------ ------ ---------- ---------- ------ ----------------------------
    1         100 Steven       King                           King            0 /King
    2         148 Gerald       Cambrault       100        100 King            0 /King/Cambrault
    3         172 Elizabeth    Bates           148        148 King            1 /King/Cambrault/Bates
    3         169 Harrison     Bloom           148        148 King            1 /King/Cambrault/Bloom
    3         170 Tayler       Fox             148        148 King            1 /King/Cambrault/Fox
    3         173 Sundita      Kumar           148        148 King            1 /King/Cambrault/Kumar
    3         168 Lisa         Ozer            148        148 King            1 /King/Cambrault/Ozer
    3         171 William      Smith           148        148 King            1 /King/Cambrault/Smith
    2         102 Lex          De Haan         100        100 King            0 /King/De Haan
    3         103 Alexander    Hunold          102        102 King            0 /King/De Haan/Hunold
    4         105 David        Austin          103        103 King            1 /King/De Haan/Hunold/Austin
    4         104 Bruce        Ernst           103        103 King            1 /King/De Haan/Hunold/Ernst
    4         107 Diana        Lorentz         103        103 King            1 /King/De Haan/Hunold/Lorentz
    4         106 Valli        Pataballa       103        103 King            1 /King/De Haan/Hunold/Pataballa
    2         147 Alberto      Errazuriz       100        100 King            0 /King/Errazuriz
    3         166 Sundar       Ande            147        147 King            1 /King/Errazuriz/Ande
    3         167 Amit         Banda           147        147 King            1 /King/Errazuriz/Banda
    3         163 Danielle     Greene          147        147 King            1 /King/Errazuriz/Greene
    3         165 David        Lee             147        147 King            1 /King/Errazuriz/Lee
    3         164 Mattea       Marvins         147        147 King            1 /King/Errazuriz/Marvins
    3         162 Clara        Vishney         147        147 King            1 /King/Errazuriz/Vishney
    2         121 Adam         Fripp           100        100 King            0 /King/Fripp
    3         130 Mozhe        Atkinson        121        121 King            1 /King/Fripp/Atkinson
    3         129 Laura        Bissot          121        121 King            1 /King/Fripp/Bissot
    3         185 Alexis       Bull            121        121 King            1 /King/Fripp/Bull
    3         187 Anthony      Cabrio          121        121 King            1 /King/Fripp/Cabrio
    3         186 Julia        Dellinger       121        121 King            1 /King/Fripp/Dellinger
    3         131 James        Marlow          121        121 King            1 /King/Fripp/Marlow
    3         132 TJ           Olson           121        121 King            1 /King/Fripp/Olson
    3         184 Nandita      Sarchand        121        121 King            1 /King/Fripp/Sarchand
    2         201 Michael      Hartstein       100        100 King            0 /King/Hartstein
    3         202 Pat          Fay             201        201 King            1 /King/Hartstein/Fay
    2         122 Payam        Kaufling        100        100 King            0 /King/Kaufling
    3         188 Kelly        Chung           122        122 King            1 /King/Kaufling/Chung
    3         189 Jennifer     Dilly           122        122 King            1 /King/Kaufling/Dilly
    3         190 Timothy      Gates           122        122 King            1 /King/Kaufling/Gates
    3         135 Ki           Gee             122        122 King            1 /King/Kaufling/Gee
    3         133 Jason        Mallin          122        122 King            1 /King/Kaufling/Mallin
    3         191 Randall      Perkins         122        122 King            1 /King/Kaufling/Perkins
    3         136 Hazel        Philtanker      122        122 King            1 /King/Kaufling/Philtanker
    3         134 Michael      Rogers          122        122 King            1 /King/Kaufling/Rogers
    2         101 Neena        Kochhar         100        100 King            0 /King/Kochhar
    3         204 Hermann      Baer            101        101 King            1 /King/Kochhar/Baer
    3         108 Nancy        Greenberg       101        101 King            0 /King/Kochhar/Greenberg
    4         110 John         Chen            108        108 King            1 /King/Kochhar/Greenberg/Chen
    4         109 Daniel       Faviet          108        108 King            1 /King/Kochhar/Greenberg/Faviet
    4         113 Luis         Popp            108        108 King            1 /King/Kochhar/Greenberg/Popp
    4         111 Ismael       Sciarra         108        108 King            1 /King/Kochhar/Greenberg/Sciarra

    4         112 Jose Manuel  Urman           108        108 King            1 /King/Kochhar/Greenberg/Urman
    3         205 Shelley      Higgins         101        101 King            0 /King/Kochhar/Higgins
    4         206 William      Gietz           205        205 King            1 /King/Kochhar/Higgins/Gietz
    3         203 Susan        Mavris          101        101 King            1 /King/Kochhar/Mavris
    3         200 Jennifer     Whalen          101        101 King            1 /King/Kochhar/Whalen
    2         124 Kevin        Mourgos         100        100 King            0 /King/Mourgos
    3         142 Curtis       Davies          124        124 King            1 /King/Mourgos/Davies
    3         197 Kevin        Feeney          124        124 King            1 /King/Mourgos/Feeney
    3         199 Douglas      Grant           124        124 King            1 /King/Mourgos/Grant
    3         143 Randall      Matos           124        124 King            1 /King/Mourgos/Matos
    3         198 Donald       OConnell        124        124 King            1 /King/Mourgos/OConnell
    3         141 Trenna       Rajs            124        124 King            1 /King/Mourgos/Rajs
    3         144 Peter        Vargas          124        124 King            1 /King/Mourgos/Vargas
    3         196 Alana        Walsh           124        124 King            1 /King/Mourgos/Walsh
    2         146 Karen        Partners        100        100 King            0 /King/Partners
    3         160 Louise       Doran           146        146 King            1 /King/Partners/Doran
    3         156 Janette      King            146        146 King            1 /King/Partners/King
    3         158 Allan        McEwen          146        146 King            1 /King/Partners/McEwen
    3         161 Sarath       Sewall          146        146 King            1 /King/Partners/Sewall
    3         159 Lindsey      Smith           146        146 King            1 /King/Partners/Smith
    3         157 Patrick      Sully           146        146 King            1 /King/Partners/Sully
    2         114 Den          Raphaely        100        100 King            0 /King/Raphaely
    3         116 Shelli       Baida           114        114 King            1 /King/Raphaely/Baida
    3         119 Karen        Colmenares      114        114 King            1 /King/Raphaely/Colmenares
.

.

.

    3         180 Winston      Taylor          120        120 King            1 /King/Weiss/Taylor
    2         149 Eleni        Zlotkey         100        100 King            0 /King/Zlotkey
    3         174 Ellen        Abel            149        149 King            1 /King/Zlotkey/Abel
    3         178 Kimberely    Grant           149        149 King            1 /King/Zlotkey/Grant
    3         175 Alyssa       Hutton          149        149 King            1 /King/Zlotkey/Hutton
    3         179 Charles      Johnson         149        149 King            1 /King/Zlotkey/Johnson
    3         177 Jack         Livingston      149        149 King            1 /King/Zlotkey/Livingston
    3         176 Jonathon     Taylor          149        149 King            1 /King/Zlotkey/Taylor

107 개의 행이 선택되었습니다.

SQL>