Oracle実験室 / 再帰with句

# 前書き
- 記事一覧 [Oracle実験室 / Oracle データベース各種操作](https://qiita.com/Que-sera-sera/items/862c77963b841a95ba95)

# HR.EMPLOYEESテーブルを再帰with句を使用して検索
- EMPLOYEESテーブルを再帰with句でツリー形式の組織図を検索してみます.
- Oracle Live SQLにアクセスします.<font color='blue'>Oracle Accountが必要です.</font>
[Oracle Live SQL](https://livesql.oracle.com/)
- SQLを実行します.
```SQL
with p (employee_id, manager_id, depth) as (
  /* 初期集合を指定する*/
  select employee_id
       , manager_id
       , 1
    from hr.employees
   where manager_id is null /* 最上位 */
--   where employee_id = 148 /* 中間層のIDをルートとして指定する場合 */
  union all
  /* 親子関係を特定する条件を指定する */
  select c.employee_id
       , c.manager_id
       , p.depth + 1
    from p
   inner join hr.employees c
      on p.employee_id = c.manager_id /* 親のidが子の親idと一致する */
--     and p.depth + 1 <= 2 /* ノードの深さを3以下に制限する場合 */
)
search depth first by employee_id set sort_key
--search breadth  first by employee_id set sort_key
select t.EMPLOYEE_ID
     , p.depth
     , LPAD('>　', (p.depth - 1) * 4, '-') || FIRST_NAME || ' ' || LAST_NAME AS NAME
--     , FIRST_NAME || ' ' || LAST_NAME AS NAME
     , p.sort_key
  from hr.employees t
inner join p
on t.employee_id = p.employee_id
order by sort_key
;
```
- 実行結果
メニュー表示用のデータを取り出すとき等に利用できそうです.

|EMPLOYEE_ID|DEPTH|NAME|SORT_KEY|
| --- | --- | --- | --- |
|100|1|Steven King|1|
|101|2|->　Neena Kochhar|2|
|108|3|----->　Nancy Greenberg|3|
|109|4|--------->　Daniel Faviet|4|
|110|4|--------->　John Chen|5|
|111|4|--------->　Ismael Sciarra|6|
|112|4|--------->　Jose Manuel Urman|7|
|113|4|--------->　Luis Popp|8|
|200|3|----->　Jennifer Whalen|9|
|203|3|----->　Susan Mavris|10|
|204|3|----->　Hermann Baer|11|
|205|3|----->　Shelley Higgins|12|
|206|4|--------->　William Gietz|13|
|102|2|->　Lex De Haan|14|
|103|3|----->　Alexander Hunold|15|
|104|4|--------->　Bruce Ernst|16|
|105|4|--------->　David Austin|17|
|106|4|--------->　Valli Pataballa|18|
|107|4|--------->　Diana Lorentz|19|
|114|2|->　Den Raphaely|20|
|115|3|----->　Alexander Khoo|21|
|116|3|----->　Shelli Baida|22|
|117|3|----->　Sigal Tobias|23|
|118|3|----->　Guy Himuro|24|
|119|3|----->　Karen Colmenares|25|
|120|2|->　Matthew Weiss|26|
|125|3|----->　Julia Nayer|27|
|126|3|----->　Irene Mikkilineni|28|
|127|3|----->　James Landry|29|
|128|3|----->　Steven Markle|30|
|180|3|----->　Winston Taylor|31|
|181|3|----->　Jean Fleaur|32|
|182|3|----->　Martha Sullivan|33|
|183|3|----->　Girard Geoni|34|
|121|2|->　Adam Fripp|35|
|129|3|----->　Laura Bissot|36|
|130|3|----->　Mozhe Atkinson|37|
|131|3|----->　James Marlow|38|
|132|3|----->　TJ Olson|39|
|184|3|----->　Nandita Sarchand|40|
|185|3|----->　Alexis Bull|41|
|186|3|----->　Julia Dellinger|42|
|187|3|----->　Anthony Cabrio|43|
|122|2|->　Payam Kaufling|44|
|133|3|----->　Jason Mallin|45|
|134|3|----->　Michael Rogers|46|
|135|3|----->　Ki Gee|47|
|136|3|----->　Hazel Philtanker|48|
|188|3|----->　Kelly Chung|49|
|189|3|----->　Jennifer Dilly|50|
|190|3|----->　Timothy Gates|51|
|191|3|----->　Randall Perkins|52|
|123|2|->　Shanta Vollman|53|
|137|3|----->　Renske Ladwig|54|
|138|3|----->　Stephen Stiles|55|
|139|3|----->　John Seo|56|
|140|3|----->　Joshua Patel|57|
|192|3|----->　Sarah Bell|58|
|193|3|----->　Britney Everett|59|
|194|3|----->　Samuel McCain|60|
|195|3|----->　Vance Jones|61|
|124|2|->　Kevin Mourgos|62|
|141|3|----->　Trenna Rajs|63|
|142|3|----->　Curtis Davies|64|
|143|3|----->　Randall Matos|65|
|144|3|----->　Peter Vargas|66|
|196|3|----->　Alana Walsh|67|
|197|3|----->　Kevin Feeney|68|
|198|3|----->　Donald OConnell|69|
|199|3|----->　Douglas Grant|70|
|145|2|->　John Russell|71|
|150|3|----->　Peter Tucker|72|
|151|3|----->　David Bernstein|73|
|152|3|----->　Peter Hall|74|
|153|3|----->　Christopher Olsen|75|
|154|3|----->　Nanette Cambrault|76|
|155|3|----->　Oliver Tuvault|77|
|146|2|->　Karen Partners|78|
|156|3|----->　Janette King|79|
|157|3|----->　Patrick Sully|80|
|158|3|----->　Allan McEwen|81|
|159|3|----->　Lindsey Smith|82|
|160|3|----->　Louise Doran|83|
|161|3|----->　Sarath Sewall|84|
|147|2|->　Alberto Errazuriz|85|
|162|3|----->　Clara Vishney|86|
|163|3|----->　Danielle Greene|87|
|164|3|----->　Mattea Marvins|88|
|165|3|----->　David Lee|89|
|166|3|----->　Sundar Ande|90|
|167|3|----->　Amit Banda|91|
|148|2|->　Gerald Cambrault|92|
|168|3|----->　Lisa Ozer|93|
|169|3|----->　Harrison Bloom|94|
|170|3|----->　Tayler Fox|95|
|171|3|----->　William Smith|96|
|172|3|----->　Elizabeth Bates|97|
|173|3|----->　Sundita Kumar|98|
|149|2|->　Eleni Zlotkey|99|
|174|3|----->　Ellen Abel|100|
|175|3|----->　Alyssa Hutton|101|
|176|3|----->　Jonathon Taylor|102|
|177|3|----->　Jack Livingston|103|
|178|3|----->　Kimberely Grant|104|
|179|3|----->　Charles Johnson|105|
|201|2|->　Michael Hartstein|106|
|202|3|----->　Pat Fay|107|