How to manage Hierarchical Queries in PLSQL

Table Definition
create table SOCIETA
(
  IDSOCIETA   NUMBER(38) not null,
  IDSOCIETAPADRE  NUMBER(38) not null,
  DESCRIPTION  VARCHAR2(255)
)

 

 

 

SELECT  societa.*, level
    FROM SOCIETA
    START WITH idsocieta in ( select idsocieta from societa where idsocieta=2 /*or idsocieta=3*/)
    CONNECT BY PRIOR idsocieta = idsocietapadre
    order by denominazione
    ;

 

 

The CONNECT BY clause defines the relationship between the parent rows and the
child rows of the hierarchy.

 

The START WITH clause is optional. It specifies the rows that are the root(s) of the hierarchical query.