Oracle – Hierarchical Queries

Hi Everyone,

I am here to explain the Oracle Hierarchical queries with the help of an example.

Oracle stores data in terms of records and there is no mechanism to store data in hierarchically. But there are methods available in Oracle 11g package to relate and display the hierarchical data. I will introduce a Sample Table TBL_ORGN, which stores the reporting structure of organization.

--Creates table

CREATE TABLE TBL_ORGN

(

                EMPID NUMBER,

                ENAME VARCHAR2(100),

                MGRID NUMBER

);

--Inserts records into the table

BEGIN

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (1, 'SAM',NULL);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (2,'JIM',1);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (3,'GREG',1);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (4,'HARRY',2);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (5,'SHANE',3);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (6,'HILTON',3);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (7,'CHRIS',2);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (8,'JENNA',4);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (9,'MARK',5);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (10,'ANGEL',6);

INSERT INTO TBL_ORGN (EMPID, ENAME, MGRID) VALUES (11,'JANE',7);

END;

--Select the records

SELECT * FROM TBL_ORGN;

If we need to know who is reporting to JIM.

SELECT EMPID, ENAME FROM TBL_ORGN WHERE MGRID=2;

But if we need to know all the employees working under JIM. Oracle helps to traverse the hierarchical data in the table. There are some keywords used for this.

START WITH

It specifies the start of the hierarchy. In this example, JIM’s record in the starting point in the hierarchy.

CONNECT BY PRIOR

It defines how two records are related. In this example, EMPID (child) and MGRID (parent) are related.

PRIOR

This is used to achieve the traversal of the hierarchical data in the table.

SELECT

EMPID, ENAME, MGRID, LEVEL

FROM TBL_ORGN

START WITH EMPID = 2 --JIM'S ID

CONNECT BY PRIOR EMPID = MGRID

ORDER BY LEVEL, EMPID

Direction of the Data Traversal

The direction of the traversal is decided by how we set the child-parent relation in the CONNECT BY PRIOR clause.

In the above example, it gives a downward reporting structure. i.e. employees under JIM.

If we need to know who are the reporting managers of JIM.

SELECT

EMPID, ENAME, MGRID

FROM TBL_ORGN

START WITH EMPID = 2 --JIM'S ID

CONNECT BY PRIOR MGRID = EMPID

ORDER BY EMPID

Thanks for reading my Blog !