Tweet
Tweet

Slide 1

Slide 1 text

10 features do Oracle que você não conhecia #1 CONNECT BY CLAUSE TechDay 2019 - @rponte

Slide 2

Slide 2 text

describe tb_funcionarios;

Slide 3

Slide 3 text

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;

Slide 4

Slide 4 text

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;

Slide 5

Slide 5 text

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)

Slide 6

Slide 6 text

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)

Slide 7

Slide 7 text

select f.* from tb_funcionarios f ;

Slide 8

Slide 8 text

select f.* from tb_funcionarios f ;

Slide 9

Slide 9 text

select f.* from tb_funcionarios f ;

Slide 10

Slide 10 text

select f.* from tb_funcionarios f ;

Slide 11

Slide 11 text

select f.* from tb_funcionarios f ;

Slide 12

Slide 12 text

select f.* from tb_funcionarios f ;

Slide 13

Slide 13 text

select f.* from tb_funcionarios f ;

Slide 14

Slide 14 text

select f.* from tb_funcionarios f ;

Slide 15

Slide 15 text

select f.* from tb_funcionarios f ;

Slide 16

Slide 16 text

select f.* from tb_funcionarios f ;

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

Sabe aquele relatório?

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

E o gerente?

Slide 22

Slide 22 text

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 ;

Slide 23

Slide 23 text

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 ;

Slide 24

Slide 24 text

E o gerente do gerente?

Slide 25

Slide 25 text

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 ;

Slide 26

Slide 26 text

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 ;

Slide 27

Slide 27 text

E o gerente do gerente do gerente … do gerente?

Slide 28

Slide 28 text

Qual a profundidade dessa hierarquia?

Slide 29

Slide 29 text

Ou faz na aplicação, né?

Slide 30

Slide 30 text

No content

Slide 31

Slide 31 text

CONNECT BY CLAUSE

Slide 32

Slide 32 text

CONNECT BY CLAUSE Oracle

Slide 33

Slide 33 text

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 ;

Slide 34

Slide 34 text

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 ;

Slide 35

Slide 35 text

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 ;

Slide 36

Slide 36 text

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 ;

Slide 37

Slide 37 text

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 ;

Slide 38

Slide 38 text

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 ;

Slide 39

Slide 39 text

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 ;

Slide 40

Slide 40 text

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 ;

Slide 41

Slide 41 text

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 ;

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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 ;

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

No content

Slide 50

Slide 50 text

PRIOR (acessando informações do parent-node)

Slide 51

Slide 51 text

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 ;

Slide 52

Slide 52 text

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 ;

Slide 53

Slide 53 text

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 ;

Slide 54

Slide 54 text

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 ;

Slide 55

Slide 55 text

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 ;

Slide 56

Slide 56 text

Mas tem algo estranho…

Slide 57

Slide 57 text

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 ;

Slide 58

Slide 58 text

Stevens?

Slide 59

Slide 59 text

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 …

Slide 60

Slide 60 text

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 …

Slide 61

Slide 61 text

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 …

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

Mas como chego na raiz?

Slide 65

Slide 65 text

select f.* from tb_funcionarios f ;

Slide 66

Slide 66 text

select f.* from tb_funcionarios f ;

Slide 67

Slide 67 text

select f.* from tb_funcionarios f ;

Slide 68

Slide 68 text

select f.* from tb_funcionarios f ;

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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 ;

Slide 71

Slide 71 text

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 ;

Slide 72

Slide 72 text

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 ;

Slide 73

Slide 73 text

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 ;

Slide 74

Slide 74 text

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 ;

Slide 75

Slide 75 text

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 ;

Slide 76

Slide 76 text

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 ;

Slide 77

Slide 77 text

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 ;

Slide 78

Slide 78 text

E se houver muitas raizes?

Slide 79

Slide 79 text

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 ;

Slide 80

Slide 80 text

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) ;

Slide 81

Slide 81 text

Mas o ID vem de uma sequence, e agora?

Slide 82

Slide 82 text

select f.* from tb_funcionarios f ;

Slide 83

Slide 83 text

select f.* from tb_funcionarios f ;

Slide 84

Slide 84 text

select f.* from tb_funcionarios f ;

Slide 85

Slide 85 text

select f.* from tb_funcionarios f ;

Slide 86

Slide 86 text

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) ;

Slide 87

Slide 87 text

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 ;

Slide 88

Slide 88 text

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 ;

Slide 89

Slide 89 text

Como visualizar melhor essa hierarquia?

Slide 90

Slide 90 text

Profundidade

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor Aldeir Maia Pimentel Roberto … 1 2 3 4 5 6

Slide 96

Slide 96 text

Profundidade
 (level)

Slide 97

Slide 97 text

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 ;

Slide 98

Slide 98 text

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 ;

Slide 99

Slide 99 text

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 ;

Slide 100

Slide 100 text

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 ;

Slide 101

Slide 101 text

Stevens Thaio Principe Victor Aldeir

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

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 ;

Slide 104

Slide 104 text

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 ;

Slide 105

Slide 105 text

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 ;

Slide 106

Slide 106 text

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 ;

Slide 107

Slide 107 text

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 ;

Slide 108

Slide 108 text

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 ;

Slide 109

Slide 109 text

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 ;

Slide 110

Slide 110 text

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 ;

Slide 111

Slide 111 text

Ordenando…

Slide 112

Slide 112 text

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 ;

Slide 113

Slide 113 text

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 ;

Slide 114

Slide 114 text

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 ;

Slide 115

Slide 115 text

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

Slide 116

Slide 116 text

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 ;

Slide 117

Slide 117 text

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 ;

Slide 118

Slide 118 text

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 ;

Slide 119

Slide 119 text

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 ;

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

Outra forma de apresentar...
 (path)

Slide 122

Slide 122 text

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 …

Slide 123

Slide 123 text

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 ;

Slide 124

Slide 124 text

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 ;

Slide 125

Slide 125 text

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 ;

Slide 126

Slide 126 text

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 ;

Slide 127

Slide 127 text

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 ;

Slide 128

Slide 128 text

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 ;

Slide 129

Slide 129 text

Loop infinito?

Slide 130

Slide 130 text

UPDATE tb_funcionarios SET gerente_id = 1 -- Plauto WHERE id = 3 -- Chris ;

Slide 131

Slide 131 text

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 ;

Slide 132

Slide 132 text

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 ;

Slide 133

Slide 133 text

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 ;

Slide 134

Slide 134 text

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

Slide 135

Slide 135 text

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 ;

Slide 136

Slide 136 text

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 ;

Slide 137

Slide 137 text

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 ;

Slide 138

Slide 138 text

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 ;

Slide 139

Slide 139 text

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 ;

Slide 140

Slide 140 text

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 ;

Slide 141

Slide 141 text

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 ;

Slide 142

Slide 142 text

Tem mais?

Slide 143

Slide 143 text

UPDATE tb_funcionarios SET gerente_id = null WHERE id = 3 -- Chris ;

Slide 144

Slide 144 text

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 ;

Slide 145

Slide 145 text

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 ;

Slide 146

Slide 146 text

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 ;

Slide 147

Slide 147 text

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 ;

Slide 148

Slide 148 text

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 ;

Slide 149

Slide 149 text

Dá pra fazer mais?

Slide 150

Slide 150 text

SELECT level FROM DUAL CONNECT BY level <= 100 ;

Slide 151

Slide 151 text

SELECT level FROM DUAL CONNECT BY level <= 100 ;

Slide 152

Slide 152 text

SELECT level FROM DUAL CONNECT BY level <= 100 ;

Slide 153

Slide 153 text

INSERT INTO t(seq) SELECT level FROM DUAL CONNECT BY level <= 100 ;

Slide 154

Slide 154 text

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

Slide 155

Slide 155 text

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 ;

Slide 156

Slide 156 text

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 ;

Slide 157

Slide 157 text

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 ;

Slide 158

Slide 158 text

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 ;

Slide 159

Slide 159 text

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

Slide 160

Slide 160 text

Concluindo

Slide 161

Slide 161 text

Obrigado! TechDay 2019 - @rponte