SQL en estructuras jerárquicas con Oracle

SQL para estructuras jerárquicas con bases de datos Oracle. START WITH .. CONNECT BY ..

Últimamente he estado trabajando con estructuras jerárquicas de datos, en conjunción con la base de datos Oracle. Para trabajar más cómodamente existe una clausula “START WITH .. CONNECT BY ..” en el lenguaje SQL.

La sintaxis para su uso es:

SELECT .. 
[START WITH condicion_inicial] 
CONNECT BY [NOCYCLE] condicion_recursiva;

START WITH : Especifica la raíz o fila / condición inicial desde la que comienza la estructura.
CONNECT BY : Especifica la relación de jerarquía entre los elementos Padre - Hijo.
NOCYCLE: Parámetro opcional que permite el correcto funcionamiento en caso de que exista un bucle en los datos jerárquicos.

Para generar la condición recursiva es necesario el uso del operador PRIOR que internamente hace referencia al elemento padre. Si se quiere recorrer la estructura jerárquica de Padre a Hijos hay que usarlo PRIOR expresion_padre = expresion_hijo , y si por lo contrario se quiere recorrer la estructura de Hijos a Padres hay que usarlo PRIOR expresion_hijo = expresion_padre .

Además de la función anterior, desde la versión de Oracle 9i, se introdujo un nuevo operador SYS_CONNECT_BY_PATH y de esta forma permite mostrar el camino entero desde el Padre al Hijo.
La sintaxis es:

SYS_CONNECT_BY_PATH( campo, 'caracter_separador' )

Y desde la versión de Oracle 10g otro nuevo operador CONNECT_BY_ROOT que permite mostrar la raiz de la estructura jerárquica. La sintaxis es:

CONNECT_BY_ROOT ( campo )

Ejemplos

Dada la siguiente estructura del sistema de ficheros UNIX:

(id = 1)__ /raiz
(id = 2)__|__folder A
(id = 4)__| |__folder A.1
(id = 7)__| | |__file 1
(id = 8)__| | |__file 2
(id = 3)__|__folder B
(id = 5)_____|__folder B.1
(id = 6)________|__folder B.1.1
(id = 9)___________|__file 3

Almacenada en una tabla FILESYSTEM (ID,PARENT_ID,NAME);

	SELECT ID, SYS_CONNECT_BY_PATH(NAME, '|')
	FROM FILESYSTEM fs
	START WITH fs.PARENT_ID = 0
	CONNECT BY PRIOR fs.ID = fs.PARENT_ID;

` 1	/raiz`
` 2	/raiz	folder A`
` 4	/raiz	folder A	folder A.1`
` 7	/raiz	folder A	file 1`
` 8	/raiz	folder A	file 2`
` 3	/raiz	folder B`
` 5	/raiz	folder B	folder B.1`
` 6	/raiz	folder B	folder B.1	folder B.1.1`
` 9	/raiz	folder B	folder B.1	folder B.1.1	file 3`

	
	SELECT ID, SYS_CONNECT_BY_PATH(NAME, '|')
	FROM FILESYSTEM fs
	START WITH fs.PARENT_ID = 2
	CONNECT BY PRIOR fs.ID = fs.PARENT_ID;

` 4	folder A.1`
` 2	folder A.1	folder A`
` 1	folder A.1	folder A	/raiz`
` 7	file 1`
` 2	file 1	folder A`
` 1	file 1	folder A	/raiz`
` 8	file 2`
` 2	file 2	folder A`
` 1	file 2	folder A	/raiz`

		
	SELECT ID, PARENT_ID, CONNECT_BY_ROOT(NAME)
	FROM FILESYSTEM fs
	START WITH fs.PARENT_ID = 0
	CONNECT BY PRIOR fs.ID = fs.PARENT_ID;

` 1 /raiz<br> 2 /raiz<br> 4 /raiz<br> 7 /raiz<br> 8 /raiz<br> 3 /raiz<br> 5 /raiz<br> 6 /raiz<br> 9 /raiz`

		
	SELECT ID, PARENT_ID, CONNECT_BY_ROOT(NAME)
	FROM FILESYSTEM fs
	START WITH fs.PARENT_ID = 1
	CONNECT BY PRIOR fs.ID = fs.PARENT_ID;

` 2 folder A<br> 4 folder A<br> 7 folder A<br> 8 folder A<br> 3 folder B<br> 5 folder B<br> 6 folder B<br> 9 folder B`

Espero que os sirva. Nos vemos pronto!