Consultas Jerárquicas en Oracle | Connect By Prior

consulta-jerarquica.sql

SELECT     LEVEL, COD_EMPLEADO, CARGO
FROM       EMPLEADOS
START WITH COD_EMPLEADO = 1
CONNECT BY REPORTA_A = PRIOR COD_EMPLEADO;

-- OUTPUT:

-- LEVEL             COD_EMPLEADO            CARGO
-- ----------------- ---------------------- --------------------
-- 1                 1                      CEO
-- 2                 2                      VP
-- 3                 5                      Director 1  
-- 3                 6                      Director 2
-- 4                 9                      Manager 1
-- 2                 3                      SVP
-- 3                 7                      Director 3
-- 3                 8                      Director 4
-- 2                 4                      CFO

creacion-tabla-empleados.sql

  CREATE TABLE EMPLEADOS
  (  COD_EMPLEADO  NUMBER(7) PRIMARY KEY,
     CARGO        VARCHAR2(20),
     REPORTA_A   NUMBER(7));

insert-data.sql

INSERT INTO EMPLEADOS VALUES (1, 'CEO', NULL );
INSERT INTO EMPLEADOS VALUES (2, 'VP', 1 );
INSERT INTO EMPLEADOS VALUES (3, 'SVP', 1 );
INSERT INTO EMPLEADOS VALUES (4, 'CFO', 1 );
INSERT INTO EMPLEADOS VALUES (5, 'DIRECTOR 1', 2 );
INSERT INTO EMPLEADOS VALUES (6, 'DIRECTOR 2', 2 );
INSERT INTO EMPLEADOS VALUES (7, 'DIRECTOR 3', 3 );
INSERT INTO EMPLEADOS VALUES (8, 'DIRECTOR 4', 3 );
INSERT INTO EMPLEADOS VALUES (9, 'MANAGER 1', 6 );

reporte-consulta-jerarquica.sql

SELECT     LEVEL, COD_EMPLEADO, LPAD(' ', LEVEL*2) || CARGO CARGO
FROM       EMPLEADOS
START WITH COD_EMPLEADO = 1
CONNECT BY REPORTA_A = PRIOR COD_EMPLEADO;

-- LEVEL             EMPLOYEE_ID       TITLE
-- ----------------- ----------------- ---------------------------
-- 1                 1                   CEO
-- 2                 2                     VP
-- 3                 5                       Director 1
-- 3                 6                       Director 2
-- 4                 9                         Manager 1
-- 2                 3                     SVP
-- 3                 7                       Director 3
-- 3                 8                       Director 4
-- 2                 4                     CFO

restriccion-tabla-empleados.sql

ALTER TABLE EMPLEADOS
  ADD CONSTRAINT FK_EMP
    FOREIGN KEY (REPORTA_A)
    REFERENCES EMPLEADOS (COD_EMPLEADO);