drop table tree;

create table tree(
	node_id 	integer primary key,
	parent_id 	integer null,
	nodenum 	integer null );

/* sample data */
insert into tree(node_id, parent_id) values (1, null);
insert into tree(node_id, parent_id) values (2,1);
insert into tree(node_id, parent_id) values (3,1);
insert into tree(node_id, parent_id) values (4,3);
insert into tree(node_id, parent_id) values (5,3);
insert into tree(node_id, parent_id) values (6,1);
insert into tree(node_id, parent_id) values (7,6);


create or replace procedure treecrawl (root_id 	in integer ) as
begin
declare
last_visited 	integer;
nxt 		integer;
last_nodenum 	integer;
begin

last_visited := root_id;
last_nodenum := 1;
update tree set nodenum = 1 where node_id = root_id;

loop
	nxt := NULL;
	begin
		select min(node_id) into nxt from tree c
		where c.parent_id = last_visited
		and   c.nodenum is null ;
	exception when no_data_found then nxt := null;
	end;

	if nxt is null then begin
		select min(c2.node_id) into nxt from tree c, tree c2
	         where c.parent_id = c2.parent_id
		 and c.node_id = last_visited
		 and c.node_id < c2.node_id ;
		exception when no_data_found then nxt := null;
		end;
	end if;

	if nxt is null then begin
		select parent_id into nxt from tree c
		 where node_id = last_visited
		 and   c.parent_id is not null;
		exception when no_data_found then nxt := null;
		end;
	end if;

	update tree set nodenum = last_nodenum + 1
	where node_id = nxt and nodenum is null;

	if ( sql%rowcount > 0 ) then
		last_nodenum := last_nodenum + 1;
	end if;

	if nxt is not null then
	 	last_visited := nxt;
	else
		exit;  /* no more nodes */
	end if;

end loop;

end;end;
/