Recursividad en PL/SQL (II) – Implementación alternativa
28/11/2010 -
Como bien se identifica en el ejemplo de la referencia, se está tratando con datos existentes en la BBDD que tienen estructura jerárquica. También se identifica una relación unívoca entre un ‘maestro’ / ‘detalle’ (‘Carpeta’ vs ‘Documento’) y operaciones donde si se actúa sobre la entidad ‘maestro’ implica acciones sobre la ‘detalle’ –operación de ‘DELETE’-.
Partiendo de esas dos premisas iniciales, a continuación se expone una propuesta de nuevo modelo de programación a partir del caso del ejemplo de la referencia que, aunque considerándose como un ejemplo muy acertado, permitirá ver otro tipo de soluciones más óptimas.
1.- Uso del Modelo Relacional
Por definición, si un registro de ‘Carpeta’ es borrado, carece de sentido la existencia de registros asociados en ‘Documento’. Es más, por integridad referencial no es posible borrar una carpeta que tenga documentos vinculados.
Por lo tanto es recomendable modificar la relación establecida y definida a nivel de ‘CONSTRAINT’ de clave foránea -‘FK’- :
De esta forma, si se procede a la creación de esta ‘FK’ con ‘ON DELETE CASCADE’, en lugar de borrar todos los registros de ‘Documento’ que pertenecen al que se desea borrar de ‘Carpeta’, simplemente se procedería a modificar:
Consecuentemente, es recomendable aplicar este cambio.
2.- Búsqueda de información jerárquica
(consulta con Recursividad Implícita)
El lenguaje ‘SQL‘ permite el tratamiento y extracción de información de estructura jerárquica, mediante el uso de ‘CONNECT BY PRIOR …’.
Muestra de un ejemplo:
Teniendo esta forma de extracción, se procede a la definición de un nuevo modelo de codificación.
3.- Solución planteada
Partiendo de la consideración de las premisas anteriores, primero se plantea una solución basada en un caso de ‘excepción‘, y a continuación se presenta la solución final de ‘implementación alternativa‘ para el ejemplo de referencia.
– Solución 1 (‘Excepción con error’)
Se trata de una solución simple y breve. Quedaría etiquetada como la mejor si no fuese por el ‘error‘ que genera. Se incluye por tanto sólo como ‘documentación añadida e informativa‘.
Se crea la siguiente función:
Entonces, simplemente se ejecutaría el siguiente comando/consulta:
Y es cuando se detecta el error:
Lo que indica que no se pueden ejecutar ‘funciones‘ con sentencias ‘DML’ (‘INSERT’, ‘DELETE’, …). Incidencia más que normal, ya que Oracle lo restringe por considerar la posibilidad de que si dentro de ésta se hiciesen cambios directos sobre los objetos consultados entonces se crearía un ‘conflicto auto-generado’ que distorsionaría el resultado por posible influencia directa.
No obstante, si la ‘función‘ tan sólo hiciese acciones de ‘consulta‘ o no interactuase contra los datos, esta solución se consideraría como la más simple, sencilla y efectiva‘ .
– Solución final
Consiste en la utilización de la recogida de información jerárquica con el ‘CONNECT BY PRIOR …’ (y por supuesto utilizando el ‘ON DELETE CASCADE’ en la definición de la ‘FK’).
Creación del procedimiento siguiente:
Ejecución:
SQL> exec borrar_carpeta_rrq_2(1)
SQL> commit;
Funciona perfectamente y borra todo el árbol jerárquico con sus documentos asociados al igual que en el caso original, pero ‘con resultados de ejecución mejores‘.
– Resultados (sólo en tiempo de ejecución)
Según el ‘Ejemplo inicial‘ (para todo el árbol):
SQL> exec borrar_carpeta(1)
Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:00.04
Según las modificaciones sugeridas:
SQL> exec borrar_carpeta_rrq_2(1)
Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:00.01
Como se puede observar, a nivel de tiempo de ejecución (aunque el ‘volumen de datos no sea muy significativo‘) la ganancia es más que aceptable. Y si se mostrasen más parámetros sobre ‘Tiempo de CPU‘, ‘uso de memoria‘ y ‘acceso a disco‘, aún se notaría más la mejora.
