Tree structures in Oracle

Let's create the following table:


CREATE TABLE tab1 (
    id        NUMBER,
    parent_id NUMBER,
    CONSTRAINT tab1_pk PRIMARY KEY (id),
    CONSTRAINT tab1_tab1_fk FOREIGN KEY (parent_id) REFERENCES tab1(id)
  );


This weird construct (a column which has a foreign key to another column of the same table) allows us to implement a tree structure in relational databases.


How can we find the leaf nodes?


SELECT tree1.id
FROM tab1 tree1
WHERE tree1.id NOT IN (SELECT DISTINCT nvl(tree2.parent_id, 1)
                                         FROM tab1 tree2)


And how can we traverse the tree? Using recursion:


PROCEDURE tree_traversal(parent_in IN NUMBER) IS

  CURSOR children_cur(parent_in_cur IN NUMBER) IS
    SELECT id
    FROM tab1
    WHERE parent_id = parent_in_cur;

BEGIN
    FOR c IN children_cur(parent_in) LOOP
      dbms_output.put_line(c.id);
      tree_traversal(c.id); -- recursion
    END LOOP;
END;

Hope you find this helpful!