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.

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é?
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
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