Recursive Queries in Oracle

I’ve come across a very neat trick in Oracle that I thought I’d share. Its useful for situations where you can have circular references in the data diagram, for example when the table includes a column which is a reference for another entry in the same table. Usually it would be necessary to write some function which could be called recursively and allow you to traverse this tree layout, however in Oracle there is the CONNECT BY PRIOR syntax.

The users and groups example

Sticking with my earlier example let’s say I’ve got an employees table and the employee can have a manager, which is an employee

users

ID USERNAME FIRST_NAME LAST_NAME MANAGER JOB_TITLE
1 esme Esmerelda Jones   Chief
2 rose Rose Micklethwaite 1 Queen Secretary
3 simon Simon Hirst 2 Secretary
4 tom Thomas Pockleton 1 Assistant Chief
5 miranda Miranda De Silva 4 Chief Assistant to the Assistant Chief
6 emily Emily Smith 2 Secretary
select first_name || ' ' || last_name, level
from users
connect by prior id = manager
start with id = 1

Will output the list of people followed by their reports. We can distinguish who is where in the tree using the magical column “level” which is available when we connect in this way. I’ve used the level to indent the name column by two spaces for each level down the tree the person is and to show their job title:

select lpad('  ', (level - 1) * 14) || first_name || ' ' || last_name, job_title
from users
connect by prior id = manager
start with id = 1

Leave a Reply

Please use [code] and [/code] around any source code you wish to share.

This site uses Akismet to reduce spam. Learn how your comment data is processed.