10 Features do Oracle que voce não conhecia - CONNECT BY CLAUSE

10 Features do Oracle que voce não conhecia - CONNECT BY CLAUSE

(Palestra ministrada no TechDay da MDIAS em JAN-2019)

Todo banco de dados possui features na qual desconhecemos, o que acaba nos levando a soluções mais complicadas ou menos eficientes, com o Oracle não é diferente. Por esse motivo, pretendo apresentar de forma bastante didática uma série com 10 features do Oracle 11g que eu desconhecia e que tem facilitado bastante minha vida desde meados de 2016.

Como primeira feature, apresento o CONNECT BY CLAUSE, na qual nos permite escrever consultas hierárquicas de forma simples levando todo o processamento para dentro do banco de dados. Além disso, também apresento outras maneiras de tirar proveito desta feature no dia a dia.

F853760c988228c4a153333407e64f09?s=128

Rafael Ponte

January 31, 2019
Tweet

Transcript

  1. 10 features do Oracle que você não conhecia #1 CONNECT

    BY CLAUSE TechDay 2019 - @rponte
  2. describe tb_funcionarios;

  3. Name Null? Type ---------- -------- ------------- ID NOT NULL NUMBER(38)

    NOME NOT NULL VARCHAR2(200) CARGO NOT NULL VARCHAR2(100) GERENTE_ID NUMBER(38) describe tb_funcionarios;
  4. Name Null? Type ---------- -------- ------------- ID NOT NULL NUMBER(38)

    NOME NOT NULL VARCHAR2(200) CARGO NOT NULL VARCHAR2(100) GERENTE_ID NUMBER(38) describe tb_funcionarios;
  5. Name Null? Type ---------- -------- ------------- ID NOT NULL NUMBER(38)

    NOME NOT NULL VARCHAR2(200) CARGO NOT NULL VARCHAR2(100) GERENTE_ID NUMBER(38) describe tb_funcionarios; (FOREIGN KEY)
  6. Name Null? Type ---------- -------- ------------- ID NOT NULL NUMBER(38)

    NOME NOT NULL VARCHAR2(200) CARGO NOT NULL VARCHAR2(100) GERENTE_ID NUMBER(38) describe tb_funcionarios; (FOREIGN KEY)
  7. select f.* from tb_funcionarios f ;

  8. select f.* from tb_funcionarios f ;

  9. select f.* from tb_funcionarios f ;

  10. select f.* from tb_funcionarios f ;

  11. select f.* from tb_funcionarios f ;

  12. select f.* from tb_funcionarios f ;

  13. select f.* from tb_funcionarios f ;

  14. select f.* from tb_funcionarios f ;

  15. select f.* from tb_funcionarios f ;

  16. select f.* from tb_funcionarios f ;

  17. Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor

    Aldeir Maia Pimentel Roberto … Hierarquia
  18. Sabe aquele relatório?

  19. select f.nome as funcionario from tb_funcionarios f order by f.nome

    ;
  20. select f.nome as funcionario from tb_funcionarios f order by f.nome

    ;
  21. E o gerente?

  22. select f1.nome as funcionario ,f2.nome as gerente from tb_funcionarios f1

    ,tb_funcionarios f2 where f1.gerente_id = f2.id order by f.nome ;
  23. select f1.nome as funcionario ,f2.nome as gerente from tb_funcionarios f1

    ,tb_funcionarios f2 where f1.gerente_id = f2.id order by f.nome ;
  24. E o gerente do gerente?

  25. select f1.nome as funcionario ,f2.nome as gerente ,f3.nome as gerente_do_gerente

    from tb_funcionarios f1 ,tb_funcionarios f2 ,tb_funcionarios f3 where f1.gerente_id = f2.id and f2.gerente_id = f3.id order by f.nome ;
  26. select f1.nome as funcionario ,f2.nome as gerente ,f3.nome as gerente_do_gerente

    from tb_funcionarios f1 ,tb_funcionarios f2 ,tb_funcionarios f3 where f1.gerente_id = f2.id and f2.gerente_id = f3.id order by f.nome ;
  27. E o gerente do gerente do gerente … do gerente?

  28. Qual a profundidade dessa hierarquia?

  29. Ou faz na aplicação, né?

  30. None
  31. CONNECT BY CLAUSE

  32. CONNECT BY CLAUSE Oracle

  33. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  34. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  35. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  36. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  37. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  38. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  39. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  40. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  41. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  42. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Stevens Thaio
  43. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  44. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Stevens Thaio Principe Victor Aldeir
  45. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Stevens Thaio Principe Victor Aldeir
  46. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Stevens Thaio Principe Victor Aldeir
  47. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Stevens Thaio Principe Victor Aldeir
  48. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Stevens Thaio Principe Victor Aldeir
  49. None
  50. PRIOR (acessando informações do parent-node)

  51. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,????? as gerente_nome from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  52. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,????? as gerente_nome from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  53. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,prior f.nome as gerente_nome from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  54. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,prior f.nome as gerente_nome from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  55. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,prior f.nome as gerente_nome from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  56. Mas tem algo estranho…

  57. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ;
  58. Stevens?

  59. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor Aldeir Maia Pimentel Roberto …
  60. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor Aldeir Maia Pimentel Roberto …
  61. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor Aldeir Maia Pimentel Roberto …
  62. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor Aldeir Maia Pimentel Roberto … Root Node
  63. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id ; Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor Aldeir Maia Pimentel Roberto … Root Node
  64. Mas como chego na raiz?

  65. select f.* from tb_funcionarios f ;

  66. select f.* from tb_funcionarios f ;

  67. select f.* from tb_funcionarios f ;

  68. select f.* from tb_funcionarios f ;

  69. Então vamos começar a hierarquia por ele…

  70. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id = 3 ;
  71. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id = 3 ;
  72. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id = 3 ;
  73. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id = 3 ;
  74. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id = 3 ;
  75. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id = 3 ;
  76. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id = 3 ;
  77. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id = 3 ;
  78. E se houver muitas raizes?

  79. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id = 3 ;
  80. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id in (3, 45, 104) ;
  81. Mas o ID vem de uma sequence, e agora?

  82. select f.* from tb_funcionarios f ;

  83. select f.* from tb_funcionarios f ;

  84. select f.* from tb_funcionarios f ;

  85. select f.* from tb_funcionarios f ;

  86. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.id in (3, 45, 104) ;
  87. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  88. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  89. Como visualizar melhor essa hierarquia?

  90. Profundidade

  91. Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor

    Aldeir Maia Pimentel Roberto …
  92. Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor

    Aldeir Maia Pimentel Roberto … 1
  93. Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor

    Aldeir Maia Pimentel Roberto … 1 2
  94. Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor

    Aldeir Maia Pimentel Roberto … 1 2 3
  95. Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor

    Aldeir Maia Pimentel Roberto … 1 2 3 4 5 6
  96. Profundidade
 (level)

  97. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  98. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  99. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  100. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  101. Stevens Thaio Principe Victor Aldeir

  102. Stevens Thaio Principe Victor Aldeir .Stevens ..Thaio ...Principe ...Victor ...Aldeir

  103. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || id as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  104. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.id as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  105. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.id as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  106. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.id as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  107. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.id as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  108. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || id as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  109. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  110. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null ;
  111. Ordenando…

  112. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  113. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order by f.nome ;
  114. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order by f.nome ;
  115. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order by f.nome ; ????
  116. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order by f.nome ;
  117. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order by f.nome ;
  118. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  119. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  120. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ; x
  121. Outra forma de apresentar...
 (path)

  122. Outra forma de apresentar...
 (path) Chris Chris->Ricardo Chris->Ricardo->Plauto Chris->Ricardo->Plauto->Stevens Chris->Ricardo->Plauto->Stevens->Thaio

    Chris->Ricardo->Plauto->Stevens->Thaio->Aldeir Chris->Ricardo->Plauto->Stevens->Thaio->Principe Chris->Ricardo->Plauto->Stevens->Thaio->Victor Chris->Ricardo->Plauto->Stevens->Thaio->… Chris->Ricardo->Plauto->Guto …
  123. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  124. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  125. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  126. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  127. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  128. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  129. Loop infinito?

  130. UPDATE tb_funcionarios SET gerente_id = 1 -- Plauto WHERE id

    = 3 -- Chris ;
  131. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  132. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  133. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id = 3 order siblings by f.nome ;
  134. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id = 3 order siblings by f.nome ; ERROR: ORA-01436: CONNECT BY loop in user data
  135. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by f.gerente_id = prior f.id start with f.gerente_id = 3 order siblings by f.nome ;
  136. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id = 3 order siblings by f.nome ;
  137. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  138. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path ,connect_by_cycle as is_cycle from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id = 3 order siblings by f.nome ;
  139. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path ,connect_by_iscycle as is_cycle from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id = 3 order siblings by f.nome ;
  140. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path ,connect_by_cycle as is_cycle from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id = 3 order siblings by f.nome ;
  141. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path ,connect_by_cycle as is_cycle from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id = 3 order siblings by f.nome ;
  142. Tem mais?

  143. UPDATE tb_funcionarios SET gerente_id = null WHERE id = 3

    -- Chris ;
  144. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path ,connect_by_iscycle as is_cycle
 ,connect_by_isleaf as is_leaf ,connect_by_root f.id as root_id from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  145. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path ,connect_by_iscycle as is_cycle
 ,connect_by_isleaf as is_leaf ,connect_by_root f.id as root_id from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  146. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path ,connect_by_iscycle as is_cycle
 ,connect_by_isleaf as is_leaf ,connect_by_root f.id as root_id from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  147. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path ,connect_by_iscycle as is_cycle
 ,connect_by_isleaf as is_leaf ,connect_by_root f.id as root_id from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  148. select f.id as id ,f.nome as funcionario ,f.cargo as cargo

    ,f.gerente_id as gerente_id ,level ,lpad('.', (level-1), '.') || f.nome as tree ,sys_connect_by_path(f.nome, '->') as path ,connect_by_iscycle as is_cycle
 ,connect_by_isleaf as is_leaf ,connect_by_root f.id as root_id from tb_funcionarios f connect by nocycle f.gerente_id = prior f.id start with f.gerente_id is null order siblings by f.nome ;
  149. Dá pra fazer mais?

  150. SELECT level FROM DUAL CONNECT BY level <= 100 ;

  151. SELECT level FROM DUAL CONNECT BY level <= 100 ;

  152. SELECT level FROM DUAL CONNECT BY level <= 100 ;

  153. INSERT INTO t(seq) SELECT level FROM DUAL CONNECT BY level

    <= 100 ;
  154. INSERT INTO tb(seq) SELECT level FROM DUAL CONNECT BY level

    <= 100 ;
  155. SELECT add_months(input, (level-1) * 12) as start_date ,add_months(input, level *

    12) as end_date FROM ( -- Set the input date here SELECT DATE'2012-02-01' as input FROM DUAL ) CONNECT BY add_months(input, (level-1) * 12) < Sysdate ;
  156. SELECT add_months(input, (level-1) * 12) as start_date ,add_months(input, level *

    12) as end_date FROM ( -- Set the input date here SELECT DATE'2012-02-01' as input FROM DUAL ) CONNECT BY add_months(input, (level-1) * 12) < Sysdate ;
  157. SELECT add_months(input, (level-1) * 12) as start_date ,add_months(input, level *

    12) as end_date FROM ( -- Set the input date here SELECT DATE'2012-02-01' as input FROM DUAL ) CONNECT BY add_months(input, (level-1) * 12) < Sysdate ;
  158. SELECT add_months(input, (level-1) * 12) as start_date ,add_months(input, level *

    12) as end_date FROM ( -- Set the input date here SELECT DATE'2012-02-01' as input FROM DUAL ) CONNECT BY add_months(input, (level-1) * 12) < Sysdate ;
  159. SELECT add_months(input, (level-1) * 12) as start_date ,add_months(input, level *

    12) as end_date FROM ( -- Set the input date here SELECT DATE'2012-02-01' as input FROM DUAL ) CONNECT BY add_months(input, (level-1) * 12) < Sysdate ; x está incluindo hoje, 2019-01-31
  160. Concluindo

  161. Obrigado! TechDay 2019 - @rponte