Manage Learn to apply best practices and optimize your operations.

How to use iterative scans to create hierarchical queries in SQL

PL/SQL expert Dan Clamage explains how to use iterative scans to create hierarchical queries in SQL that are database agnostic.

We are implementing hierarchy in our app using the adjacency model. We have been using CONNECT BY PRIOR to retrieve the leaf nodes and traverse through the hierarchy.

We need to develop a SQL function that can be database agnostic. But first we want to do that with Oracle. Can you give me pointers regarding a recursive function to traverse through the hierarchy?

My table structure is very simple:

Node ID Parent ID <Node Attributes1-5>...

Your response is highly appreciated.

If you’re already on Oracle, why do you wish to be database-agnostic? Once you choose a database platform, you should leverage its unique capabilities to your fullest advantage.

If you have SQL2000 in-house, then you already know it doesn’t support hierarchical queries. You have to simulate them procedurally.

The least database-dependent method I can think of is to do an iterative scan, instead of using the hierarchy clauses (start with, connect by). This requires you to keep some housekeeping variables on hand. You can still do it recursively. At least then the algorithm you develop can be readily translated between PL/SQL and T-SQL (or whatever procedural database language you’re using). Your function will be calling itself, so it needs a reliable method of terminating the recursion.

Have a question for Dan Clamage? Send an e-mail to editor@searchoracle.com




Dig Deeper on Using Oracle PL-SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.