Saltar al contenido

Consultas jerarquicas en Oracle

Desde la versión 9i podemos realizar consultas jerárquicas en Oracle simulando un árbol jerárquico. Tenemos la tabla MENU, con estos datos.

 

IDMENU POST_TITLE IDRELACION URL
1 Java
2 Ubuntu
3 Iphone
4 Password – Ubuntu 2 http://..password-ubuntu
5 Ubuntu – Beryl 2 http://..ubuntu-beryl
6 Clase System 1 http://..clase-system
7 ACCEDER AL IPOD TOUCH MEDIANTE FTP 3 http://..cceder-al-ipod-touch-mediante-ftp
8 Resumen Java – Modificadores de Acceso. 1 http://..resumen-java-modificadores-de-acceso
9 Resumen Java – Tipos de Variables. 6 http://..resumen-java-tipos-de-variables
10 Super Bluetooth Hack 3 http://..super-bluetooth-hack

 

Visualmente queríamos obtener algo asi: 1. Java 1.1.Resumen Java – Modificadores de Acceso. 1.2.Clase System 1.2.1.Resumen Java – Tipos de Variables. 2 Ubuntu 2.1. Password – Ubuntu 2.2. Ubuntu – Beryl 3 Iphone 3.1.Super Bluetooth Hack 3.2.ACCEDER AL IPOD TOUCH MEDIANTE FTP Con la select siguiente obtendremos el nombre de los que dependen del post  ’Java’ ( con START WITH) y CONNECT BY PRIOR nos indicará cual son las columnas que hay relación padre-hijo. La pseudocolumna LEVEL nos indicará el nivel en el que se encuentra el registro.

select POST_TITLE, LEVEL from menu CONNECT BY PRIOR idMenu=idRelacion START WITH POST_TITLE = ‘Java’

POST_TITLE LEVEL
Java 1
Resumen Java – Modificadores de Acceso. 2
Resumen Java – Tipos de Variables. 3
Clase System 2

 

Si ahora queremos obtener cual son los “padres” de ‘Resumen Java – Tipos de Variables’:

select POST_TITLE, LEVEL from menu CONNECT BY PRIOR idRelacion= idMenu START WITH POST_TITLE = ‘Resumen Java – Tipos de Variables.’

POST_TITLE LEVEL
Resumen Java – Tipos de Variables. 1
Clase System 2
Java 3

 

Es decir, este post depende de Clase System y este de Java. ORDER SIBLINGS (Oracle 10)  devolverá los registros ordenados alfabéticamente por nivel. Añadiremos espacios dependiendo del nivel (LEVEL) para verlo más claro:

select lpad(’  ‘, (level – 1) * 2) || POST_TITLE from menu CONNECT BY PRIOR idMenu=idRelacion START WITH idRelacion is null ORDER siblings BY POST_TITLE

Iphone
  ACCEDER AL IPOD TOUCH MEDIANTE FTP
  Super Bluetooth Hack
Java
  Clase System
     Resumen Java – Tipos de Variables.
  Resumen Java – Modificadores de Acceso.
Ubuntu
  Password – Ubuntu
  Ubuntu – Beryl

 

SYS_CONNECT_BY_PATH. Permite obtener la ruta completa desde la raíz (en este caso ‘JAVA’). Sólo puede utilizarse en columnas tipo carácter.

 

select SYS_connect_by_path(POST_TITLE,’/’) as nombre, LEVEL from menu CONNECT BY PRIOR idMenu = idRelacion START WITH POST_TITLE = ‘Java’ ORDER siblings BY idMenu

NOMBRE LEVEL
/Java 1
/Java/Resumen Java – Modificadores de Acceso. 2
/Java/Clase System 2
/Java/Clase System/Resumen Java – Tipos de Variables. 3

 

CONNECT_BY_ROOT. Podemos devolver la raíz (padre) a la que pertenece ese objeto (Oracle 10)

select lpad(’  ‘, (level – 1) * 2) || POST_TITLE as TITULO, CONNECT_BY_ROOT POST_TITLE as PERTENECE_A from menu CONNECT BY PRIOR idMenu=idRelacion START WITH idRelacion is null ORDER siblings BY POST_TITLE

TITULO PERTENECE_A
Iphone Iphone
  ACCEDER AL IPOD TOUCH MEDIANTE FTP Iphone
  Super Bluetooth Hack Iphone
Java Java
  Clase System Java
    Resumen Java – Tipos de Variables. Java
  Resumen Java – Modificadores de Acceso. Java
Ubuntu Ubuntu
  Password – Ubuntu Ubuntu
  Ubuntu – Beryl Ubuntu

 

CONNECT_BY_ISLEAF. Podemos saber si un elemento es terminal o no, si es la raíz nos devolverá 0, en caso contrario 1 (Oracle 10)

select lpad(’  ‘, (level – 1) * 2) || POST_TITLE as TITULO, CONNECT_BY_ISLEAF as PRINCIPAL from menu CONNECT BY PRIOR idMenu=idRelacion START WITH idRelacion is null ORDER siblings BY POST_TITLE

TITULO PRINCIPAL
Iphone 0
  ACCEDER AL IPOD TOUCH MEDIANTE FTP 1
  Super Bluetooth Hack 1
Java 0
  Clase System 0
    Resumen Java – Tipos de Variables. 1
  Resumen Java – Modificadores de Acceso. 1
Ubuntu 0
  Password – Ubuntu 1
  Ubuntu – Beryl 1