Tabelle erzeugen:
CREATE TABLE cat(id AutoInc, parent_cat Integer, text Char(10 )) IN DATABASE;
Rekursive Prozedur, um alle Unter-Kategorien zu bekommen:
SQL-Code:
CREATE PROCEDURE subcat(parent_cat Integer, id Integer OUTPUT)
BEGIN
declare @c cursor;
declare @parent_cat integer;
@parent_cat=(select parent_cat from __input);
if @parent_cat is null
then open @c as select id from cat where parent_cat is null;
else open @c as select id from cat where parent_cat = @parent_cat;
endif;
while fetch @c do
insert into __output values (@c.id);
insert into __output select * from (execute procedure subcat(@c.id)) a;
endwhile;
close @c;
END;
Verwendung (gib alle Unter-Kategorien von Eintrag 1):
SQL-Code:
select c.* from cat c
inner join (EXECUTE PROCEDURE subcat(1)) a
on c.id=a.id;