Share
Embed
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.
Rafael Ponte
January 31, 2019
Transcript
161 slides
-
10 features do Oracle que você não conhecia #1 CONNECT
BY CLAUSE TechDay 2019 - @rponte -
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; -
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; -
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) -
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) -
Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor
Aldeir Maia Pimentel Roberto … Hierarquia -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 -
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 ; -
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 -
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 -
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 -
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 -
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 -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 … -
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 … -
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 … -
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 -
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 -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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) ; -
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) ; -
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 ; -
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 ; -
Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor
Aldeir Maia Pimentel Roberto … -
Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor
Aldeir Maia Pimentel Roberto … 1 -
Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor
Aldeir Maia Pimentel Roberto … 1 2 -
Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor
Aldeir Maia Pimentel Roberto … 1 2 3 -
Chris Rafael Plauto Ricardo Stevens Thaio Principe Guto … Victor
Aldeir Maia Pimentel Roberto … 1 2 3 4 5 6 -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; ???? -
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 ; -
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 ; -
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 ; -
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 ; -
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 -
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 … -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
UPDATE tb_funcionarios SET gerente_id = 1 -- Plauto WHERE id
= 3 -- Chris ; -
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 ; -
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 ; -
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 ; -
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 -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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 ; -
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