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

Transcript

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

describe tb_funcionarios;

Name Null? Type
---------- -------- -------------
ID NOT NULL NUMBER(38)
NOME NOT NULL VARCHAR2(200)
CARGO NOT NULL VARCHAR2(100)
GERENTE_ID NUMBER(38)

View Slide

Name Null? Type
---------- -------- -------------

View Slide

Name Null? Type
---------- -------- -------------
(FOREIGN KEY)

View Slide

Name Null? Type
---------- -------- -------------
(FOREIGN KEY)

View Slide

select f.*
from tb_funcionarios f
;

select f.*
;

select f.*
;

select f.*
;

select f.*
;

select f.*
;

select f.*
;

select f.*
;

select f.*
;

select f.*
;

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

View Slide

Sabe aquele relatório?

View Slide

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

View Slide

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

View Slide

E o gerente?

View Slide

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
;

View Slide

,f2.nome as gerente
,tb_funcionarios f2
order by f.nome
;

View Slide

E o gerente do
gerente?

View Slide

,f2.nome as gerente
,f3.nome as gerente_do_gerente
,tb_funcionarios f2
,tb_funcionarios f3
and f2.gerente_id = f3.id
order by f.nome
;

View Slide

,f2.nome as gerente
,f3.nome as gerente_do_gerente
,tb_funcionarios f2
,tb_funcionarios f3
and f2.gerente_id = f3.id
order by f.nome
;

View Slide

E o gerente do gerente
do gerente … do
gerente?

View Slide

Qual a profundidade
dessa hierarquia?

View Slide

Ou faz na aplicação,
né?

View Slide

CONNECT BY CLAUSE

View Slide

CONNECT BY CLAUSE
Oracle

View Slide

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

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;
Stevens
Thaio

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;
Stevens
Thaio
Principe Victor Aldeir

View Slide

select f.id as id
,f.cargo as cargo
;
Stevens
Thaio

View Slide

select f.id as id
,f.cargo as cargo
;
Stevens
Thaio

View Slide

select f.id as id
,f.cargo as cargo
;
Stevens
Thaio

View Slide

select f.id as id
,f.cargo as cargo
;
Stevens
Thaio

View Slide

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

View Slide

select f.id as id
,f.cargo as cargo
,????? as gerente_nome
;

View Slide

select f.id as id
,f.cargo as cargo
,????? as gerente_nome
;

View Slide

select f.id as id
,f.cargo as cargo
,prior f.nome as gerente_nome
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

Mas tem algo
estranho…

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

Stevens?

View Slide

select f.id as id
,f.cargo as cargo
;
Chris
Rafael
Plauto
Ricardo
Stevens
Thaio
Principe
Guto …
Victor Aldeir
Maia
Pimentel Roberto
…

View Slide

select f.id as id
,f.cargo as cargo
;
Chris
Rafael
Plauto
Ricardo
Stevens
Thaio
Principe
Guto …
Victor Aldeir
Maia
Pimentel Roberto
…

View Slide

select f.id as id
,f.cargo as cargo
;
Chris
Rafael
Plauto
Ricardo
Stevens
Thaio
Principe
Guto …
Victor Aldeir
Maia
Pimentel Roberto
…

View Slide

select f.id as id
,f.cargo as cargo
;
Chris
Rafael
Plauto
Ricardo
Stevens
Thaio
Principe
Guto …
Victor Aldeir
Maia
Pimentel Roberto
…
Root Node

View Slide

select f.id as id
,f.cargo as cargo
;
Chris
Rafael
Plauto
Ricardo
Stevens
Thaio
Principe
Guto …
Victor Aldeir
Maia
Pimentel Roberto
…
Root Node

View Slide

Mas como chego na
raiz?

select f.*
;

select f.*
;

select f.*
;

select f.*
;

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

View Slide

select f.id as id
,f.cargo as cargo
start with f.id = 3
;

View Slide

select f.id as id
,f.cargo as cargo
start with f.id = 3
;

View Slide

select f.id as id
,f.cargo as cargo
start with f.id = 3
;

View Slide

select f.id as id
,f.cargo as cargo
start with f.id = 3
;

View Slide

select f.id as id
,f.cargo as cargo
start with f.id = 3
;

View Slide

select f.id as id
,f.cargo as cargo
start with f.id = 3
;

View Slide

select f.id as id
,f.cargo as cargo
start with f.id = 3
;

View Slide

select f.id as id
,f.cargo as cargo
start with f.id = 3
;

View Slide

E se houver muitas
raizes?

View Slide

select f.id as id
,f.cargo as cargo
start with f.id = 3
;

View Slide

select f.id as id
,f.cargo as cargo
start with f.id in (3, 45, 104)
;

View Slide

Mas o ID vem de uma
sequence, e agora?

select f.*
;

select f.*
;

select f.*
;

select f.*
;

select f.id as id
,f.cargo as cargo
start with f.id in (3, 45, 104)
;

View Slide

select f.id as id
,f.cargo as cargo
start with f.gerente_id is null
;

View Slide

select f.id as id
,f.cargo as cargo
;

View Slide

Como visualizar melhor essa
hierarquia?

View Slide

Profundidade

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

Profundidade 
(level)

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

Stevens
Thaio

View Slide

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

View Slide

select f.id as id
,f.cargo as cargo
,level
,lpad('.', (level-1), '.') || id as tree
;

View Slide

select f.id as id
,f.cargo as cargo
,level
,lpad('.', (level-1), '.') || f.id as tree
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
,lpad('.', (level-1), '.') || id as tree
;

View Slide

select f.id as id
,f.cargo as cargo
,level
,lpad('.', (level-1), '.') || f.nome as tree
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

Ordenando…

View Slide

select f.id as id
,f.cargo as cargo
,level
order siblings by f.nome
;

View Slide

select f.id as id
,f.cargo as cargo
,level
order by f.nome
;

View Slide

select f.id as id
,f.cargo as cargo
,level
order by f.nome
;

View Slide

select f.id as id
,f.cargo as cargo
,level
order by f.nome
;
????

View Slide

select f.id as id
,f.cargo as cargo
,level
order by f.nome
;

View Slide

select f.id as id
,f.cargo as cargo
,level
order by f.nome
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;
x

View Slide

Outra forma de
apresentar... 
(path)

View Slide

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
…

View Slide

select f.id as id
,f.cargo as cargo
,level
,sys_connect_by_path(f.nome, '->') as path
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

Loop inﬁnito?

View Slide

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

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
start with f.gerente_id = 3
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;
ERROR:
ORA-01436: CONNECT BY loop in user data

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
connect by nocycle f.gerente_id = prior f.id
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
,connect_by_cycle as is_cycle
;

View Slide

select f.id as id
,f.cargo as cargo
,level
,connect_by_iscycle as is_cycle
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

Tem mais?

View Slide

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

View Slide

select f.id as id
,f.cargo as cargo
,level
,connect_by_iscycle as is_cycle 
,connect_by_isleaf as is_leaf
,connect_by_root f.id as root_id
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

select f.id as id
,f.cargo as cargo
,level
;

View Slide

Dá pra fazer mais?

View Slide

SELECT level
FROM DUAL
CONNECT BY level <= 100
;

View Slide

SELECT level
FROM DUAL
;

View Slide

SELECT level
FROM DUAL
;

View Slide

INSERT INTO t(seq)
SELECT level
FROM DUAL
;

View Slide

INSERT INTO tb(seq)
SELECT level
FROM DUAL
;

View Slide

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
;

View Slide

SELECT
FROM (
FROM DUAL
)
CONNECT BY
;

View Slide

SELECT
FROM (
FROM DUAL
)
CONNECT BY
;

View Slide

SELECT
FROM (
FROM DUAL
)
CONNECT BY
;

View Slide

SELECT
FROM (
FROM DUAL
)
CONNECT BY
;
x
está incluindo hoje,
2019-01-31

View Slide

Concluindo

View Slide

Obrigado!
TechDay 2019 - @rponte