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 |