top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Oracle: How does one code a tree-structured query?

0 votes
90 views
Oracle: How does one code a tree-structured query?
posted Jun 22, 2015 by Suchithra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for testing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.

The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:

SQL> SELECT     level, empno, ename, mgr
2    FROM     emp
3  CONNECT BY PRIOR empno = mgr
4    START WITH mgr IS NULL
5  /
   LEVEL      EMPNO ENAME             MGR
---------- ---------- ---------- ----------
       1       7839 KING
       2       7566 JONES            7839
       3       7788 SCOTT            7566
...

One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:

SQL> SELECT     LPAD(' ', LEVEL * 2) || ename
2    FROM     emp
3  CONNECT BY PRIOR empno = mgr
4    START WITH mgr IS NULL;
LPAD(,LEVEL*2)||ENAME
------------------------------------------------------
KING
  JONES
    SCOTT
...

Use the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.

One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.

answer Jun 23, 2015 by Shivaranjini
...