SQL & PL/SQL

I am looking to write a method that will create a collection of records, each of which represents a node in a tree.

TYPE t_all_folders IS TABLE OF get_all_folders%ROWTYPE INDEX BY PLS_INTEGER;
v_all_folders t_all_folders;

so first need help in figuring out what the cursor 'get_all_folders' would look like


---
I have a folder structure represented in a database that is used basically to show visually
(with a front end app) a folder structure (much like a folder structure in a file system).

So each row has an entry in the 'folders' table like do:

table folder:
column column
folder_id name
1 folder1 <= say this is a root folder
2 folder2 <= say this is a root folder
3 folder3 <= say this is a root folder
4 folder1a <= all below are child folders..
5 folder1b
6 folder1c
7 folder1aa
8 folder1ab
...

There is nothing in this table that indicates a hiearchy.

---
The hiearchy is represented by another single table with two columns
(I cannot change this, it is what it is)

There is no left node or right node (not like a tree), just imagine sub folders.

table: parent_child
column column
------ ------
parent_id child_id

---
such that visually when the tables are queried and the UI uses a folder icon to
represent each row:

it would look like this:

folder1 1
- folder1a 2
-folder1aa 3
- folder1ab 4
- folder1b 5
- folder1ba 6
- folder1bb 7
- folder1c 8
folder2 9
folder3 10

I am attempting to create a query that will add to a collection folder records in the
order above (1..10)

In other words traverse the tree depth first going from:
folder1 -> folder1a -> folder1aa -> folder1ab ->(back out to next level) folder1b -> folder1ba -> folder1bb -> folder1c
then add folder2 (and traverse down that hiearch if needed)
and then add folder3 to the colleciton and traverse down that hiearchy if there is one
and continue adn so on.

The requirement is to have them added to the collection in that order and when I iterate through the collection,
they would of course need to be pulled out in that order (so use vararray with a counter to iterate through
after the collection has been created.

After the collection has been created, I have to iterate in that specific order to create records in another table where there is a column that requires an integer value that is the 1... order they come out of the collection
and then have to iterate again and do something else in that order (and then other things - all the while needing in that order).

Edited by: user12200443 on Nov 19, 2012 11:49 AM