| A handy feature in Oracle is the tree walking feature. For those people
who have not come across this handy feature it allows you to recursively join a table back
on to itself to retrieve all records in a hierarchy. For example you can retrieve all
employees who report to a given person and all the employees who report to those people
and so on until you have all the people.
To achieve this you need to make use of the CONNECT BY command. In the following
example we select the employee name and then specify the value in the tree where we want
to begin. To do this we use the START WITH command to identify where to begin, then we
issue the CONNECT BY PRIOR which recursively joins all employee records where the
emp_reports_to_emp_id is equal to the previous emp_id.
SELECT emp_name
FROM employee
START WITH emp_id = 270
CONNECT BY PRIOR emp_id = emp_reports_to_emp_id;
This is a great feature and can be a real timesaver. |