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!

30 minutes of code everyday

I got this idea from my friend ngeor:

Nowadays, I am writing code everyday for at most 30 minutes against a GitHub project. This is outside work hours of course. The important thing is to never exceed 30 minutes. If you spend too much time coding, you’ll end up missing out on other things in your daily routine. If you don’t code at all, you’ll miss it. I find half an hour to be a good balance. Keeps the appetite going, doesn’t leave you hungry, doesn’t leave you full either.