Skip to content

Instantly share code, notes, and snippets.

@arbo-hacker
Last active March 8, 2016 21:48
Show Gist options
  • Save arbo-hacker/fbaa5a43c4989eac0b0f to your computer and use it in GitHub Desktop.
Save arbo-hacker/fbaa5a43c4989eac0b0f to your computer and use it in GitHub Desktop.
Consultas Jerárquicas en Oracle | Connect By Prior
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
CREATE TABLE EMPLEADOS
( COD_EMPLEADO NUMBER(7) PRIMARY KEY,
CARGO VARCHAR2(20),
REPORTA_A NUMBER(7));
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 );
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
ALTER TABLE EMPLEADOS
ADD CONSTRAINT FK_EMP
FOREIGN KEY (REPORTA_A)
REFERENCES EMPLEADOS (COD_EMPLEADO);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment