Con la cláusula WITH se especifica un conjunto temporal de resultados con nombre (expresión de tabla común -CTE-) que puede incluir referencias a el mismo, lo que se conoce como recursividad. Deriva de una consulta definida en el ámbito de ejecución de una instrucción SELECT.
Nombre del archivo SQL: recursion.sql
-- Ejemplo de procedimiento recursivo que determina el árbol de dependencias de los trabajadores.
-- Crear tabla para el test.
CREATE TABLE trabajadores (
id_trabajador INT, nombre VARCHAR(30), apellidos VARCHAR(40), puesto VARCHAR(50),
id_departamento INT, id_superior INT, CONSTRAINT pk_id_trabajador PRIMARY KEY (id_trabajador));
-- Introducción de filas de prueba.
INSERT INTO trabajadores
VALUES (195, 'Pedro', 'Suárez', 'Gerente de Ventas de España', 21, 335);
INSERT INTO trabajadores
VALUES (384, 'Antonio', 'Álvarez', 'Gerente de Ventas de Portugal', 81, 335);
INSERT INTO trabajadores
VALUES (378, 'Lucas', 'Menéndez', 'Representante de Ventas', 65, 195);
INSERT INTO trabajadores
VALUES (189, 'Marcelino', 'Sánchez', 'Director Ejecutivo', 26, NULL);
INSERT INTO trabajadores
VALUES (391, 'Ana', 'Aguirre', 'Gerente de Marketing', 27, 335);
INSERT INTO trabajadores
VALUES (335, 'Juan', 'Rodríguez', 'Vicepresidente de Ventas', 53, 189);
INSERT INTO trabajadores
VALUES (549, 'Miguel', 'Solar', 'Representante de Ventas', 16, 195);
INSERT INTO trabajadores
VALUES (269, 'Elena', 'Rajoy', 'Representante de Ventas', 88, 384);
INSERT INTO trabajadores
VALUES (700, 'Teresa', 'Fernández', 'Especialista en Marketing', 17, 391);
-- Ejecución de la sentencia recursiva.
WITH autoridades (nivel, id_trabajador, puesto_trabajador, id_superior, puesto_superior) AS (
SELECT 1 AS nivel, id_trabajador, puesto AS puesto_trabajador, id_superior,
NULL AS puesto_superior
FROM trabajadores
WHERE id_superior IS NULL
UNION ALL
SELECT autoridades.nivel + 1 AS nivel, trabajadores.id_trabajador,
trabajadores.puesto AS puesto_trabajador, trabajadores.id_superior,
autoridades.puesto_trabajador AS puesto_superior
FROM trabajadores, autoridades
WHERE trabajadores.id_superior = autoridades.id_trabajador
)
SELECT nivel, id_trabajador, puesto_trabajador, id_superior, puesto_superior
FROM autoridades
ORDER BY nivel, id_trabajador;
-- Borrado de la taba de test.
DROP TABLE trabajadores;
Resultado.
NIVEL | ID_TRABAJADOR | PUESTO_TRABAJADOR | ID_SUPERIOR | PUESTO_SUPERIOR |
---|---|---|---|---|
1 | 189 | Director Ejecutivo | NULL | NULL |
2 | 335 | Vicepresidente de Ventas | 189 | Director Ejecutivo |
3 | 195 | Gerente de Ventas de España | 335 | Vicepresidente de Ventas |
3 | 384 | Gerente de Ventas de Portugal | 335 | Vicepresidente de Ventas |
3 | 391 | Gerente de Marketing | 335 | Vicepresidente de Ventas |
4 | 269 | Representante de Ventas | 384 | Gerente de Ventas de Portugal |
4 | 378 | Representante de Ventas | 195 | Gerente de Ventas de España |
4 | 549 | Representante de Ventas | 195 | Gerente de Ventas de España |
4 | 700 | Especialista en Marketing | 391 | Gerente de Marketing |
El ejemplo ha sido realizado con Oracle Database 18c.
Ir al inicio de la página.