On its face, the relational database management system would appear to be a very poor tool for representing and manipulating trees. This chapter is designed to accomplish the following things:

show you that a row in an SQL database can be thought of as an object

show you that a pointer from one object to another can be represented by storing an integer key in a regular database column

demonstrate the Oracle tree extensions (CONNECT BY … PRIOR)

show you how to work around the limitations of CONNECT BY with PL/SQL

The canonical example of trees in Oracle is the org chart.

create table corporate_slaves (

slave_id integer primary key,

supervisor_id references corporate_slaves,

name varchar(100)

);

insert into corporate_slaves values (1, NULL, ‘Big Boss Man’);

insert into corporate_slaves values (2, 1, ‘VP Marketing’);

insert into corporate_slaves values (3, 1, ‘VP Sales’);

insert into corporate_slaves values (4, 3, ‘Joe Sales Guy’);

insert into corporate_slaves values (5, 4, ‘Bill Sales Assistant’);

insert into corporate_slaves values (6, 1, ‘VP Engineering’);

insert into corporate_slaves values (7, 6, ‘Jane Nerd’);

insert into corporate_slaves values (8, 6, ‘Bob Nerd’);