Saltar al contenido
Codifíca.me | Desarrollo web | Programación

Consultas jerarquicas en Oracle

16 septiembre, 2010

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