Tema 17. Lenguajes de interrogación de bases de datos. Estándar ANSI SQL. Procedimientos almacenados. Eventos y disparadores.

1. Lenguajes de interrogación de bases de datos

1. Lenguajes de interrogación de bases de datos

🎯 Idea clave

• Los lenguajes de interrogación son mecanismos formales dotados de sintaxis y semántica precisas que permiten consultar, recuperar, filtrar, combinar y transformar datos almacenados en un sistema gestor de bases de datos.
• En el ámbito de las bases de datos relacionales, SQL constituye el lenguaje de referencia dominante, aunque el concepto abarca igualmente modelos documentales, grafos, clave-valor y motores de búsqueda.
• Se caracterizan por su naturaleza declarativa, expresando qué resultado se desea obtener sin especificar el algoritmo físico de acceso a los datos.
• El sistema gestor incorpora un optimizador que determina automáticamente el plan de ejecución, seleccionando índices, métodos de acceso y estrategias de procesamiento.
• Las consultas parametrizadas constituyen una práctica esencial para prevenir vulnerabilidades de seguridad como la inyección SQL.
• Estos lenguajes diferencian el filtrado de filas individuales mediante cláusulas específicas del filtrado de grupos agregados.

📚 Desarrollo

Definición formal. Los lenguajes de interrogación constituyen el mecanismo mediante el cual usuarios, aplicaciones o administradores se comunican con los datos almacenados. Poseen sintaxis y semántica precisas que permiten describir la estructura lógica de la información, manipular su contenido, formular consultas y controlar tanto el acceso como las propiedades transaccionales de las operaciones ejecutadas sobre ellos.

Carácter declarativo. A diferencia de la programación imperativa clásica, donde se describen paso a paso las instrucciones para realizar una tarea, estos lenguajes operan bajo un paradigma declarativo. El usuario expresa qué resultado desea obtener, especificando columnas, tablas, condiciones, agrupaciones y ordenación, mientras que el sistema gestor determina automáticamente la estrategia de ejecución más eficiente.

Funciones principales. Su propósito fundamental abarca la consulta, recuperación, filtrado, combinación y transformación de datos almacenados. En el ámbito relacional, SQL actúa como estándar de referencia, si bien el concepto abarca igualmente modelos documentales, grafos, sistemas clave-valor, motores de búsqueda e interfaces de programación específicas.

Optimización automática. El sistema gestor incorpora un optimizador responsable de decidir el plan físico de ejecución de las consultas. Este componente determina qué índices utilizar, qué métodos de acceso emplear, el orden óptimo de las operaciones de combinación entre tablas y las estrategias globales de procesamiento, liberando al usuario de estas decisiones técnicas.

Componentes sintácticos. En SQL, la instrucción SELECT constituye el núcleo de la interrogación, acompañada habitualmente de las cláusulas FROM, WHERE, GROUP BY, HAVING y ORDER BY. Estos elementos permiten especificar el origen de los datos, aplicar condiciones de filtrado sobre filas individuales o grupos, y establecer criterios de ordenación.

Gestión de valores nulos. El tratamiento de datos requiere atención especial ante la presencia de valores nulos, que no equivalen a cero ni a cadenas vacías. Estos valores exigen predicados específicos para su evaluación correcta dentro de las condiciones de búsqueda y operaciones de comparación, operando bajo una lógica de tres valores.

Seguridad en la consulta. La práctica de consultas parametrizadas se revela esencial para prevenir vulnerabilidades de seguridad, particularmente la inyección SQL. Esta técnica separa el código de la consulta de los datos de entrada, evitando que valores maliciosos alteren la estructura lógica de la instrucción ejecutada.

🧩 Elementos esenciales

• Lenguaje formal: Sistema dotado de sintaxis y semántica precisas para la comunicación con bases de datos y la formulación de preguntas estructuradas.
• Funciones básicas: Consultar, recuperar, filtrar, combinar y transformar información almacenada según criterios específicos del usuario.
• Paradigma declarativo: El usuario especifica el resultado deseado sin indicar el algoritmo físico de obtención ni el plan de acceso a los datos.
• SQL: Lenguaje dominante y estándar de facto en sistemas de bases de datos relacionales, aunque existen otros para modelos no relacionales.
• Optimizador: Componente del SGBD que decide índices, métodos de acceso, orden de joins y estrategias de ejecución física.
• Cláusulas principales: SELECT, FROM, WHERE, GROUP BY, HAVING y ORDER BY conforman la estructura básica de interrogación en SQL.
• Filtrado diferenciado: WHERE opera sobre filas individuales antes de la agrupación, mientras que HAVING actúa sobre grupos agregados después.
• Valores nulos: Requieren tratamiento específico mediante predicados diseñados para la lógica de tres valores, distintos de cero o cadena vacía.
• Consultas parametrizadas: Mecanismo de seguridad que previene inyección SQL mediante la separación entre código de la consulta y datos de entrada.
• Modelos no relacionales: Existen lenguajes específicos para documentos, grafos, RDF, búsqueda y sistemas clave-valor más allá del modelo relacional.
• Combinación de tablas: Los joins permiten unir información de múltiples tablas y deben elegirse según la conservación de filas deseada en el resultado.

🧠 Recuerda

• Los lenguajes de interrogación expresan qué información se necesita, no cómo obtenerla paso a paso.
• SQL es el estándar de facto para bases de datos relacionales, aunque existen otros modelos con sus propios lenguajes.
• El optimizador del SGBD determina automáticamente la forma más eficiente de ejecutar una consulta declarativa.
• SELECT es la instrucción central de interrogación en SQL, combinada con otras cláusulas.
• WHERE filtra filas individuales antes de cualquier agrupación; HAVING filtra grupos después del agrupamiento.
• NULL no equivale a cero ni a cadena vacía y requiere operadores específicos para su evaluación.
• Las consultas parametrizadas son esenciales para prevenir inyección SQL y proteger la integridad del sistema.
• La naturaleza declarativa distingue estos lenguajes de la programación imperativa clásica.
• El concepto de lenguaje de interrogación abarca más allá de las bases relacionales tradicionales.
• Las combinaciones de tablas mediante joins deben seleccionarse considerando qué filas se desean conservar en el resultado final.

2. Estándar ANSI SQL

2. Estándar ANSI SQL

🎯 Idea clave

• El estándar ANSI SQL normaliza el lenguaje SQL como lenguaje declarativo para bases de datos relacionales.
• La referencia internacional actual pertenece a la familia de normas ISO/IEC 9075.
• SQL fue estandarizado inicialmente por ANSI en 1986 y por ISO en 1987, con revisiones posteriores.
• El lenguaje se organiza en categorías funcionales: DDL, DML, DCL y TCL.
• El carácter declarativo separa la especificación lógica de la ejecución física gestionada por el optimizador.
• El estándar facilita la portabilidad entre sistemas, aunque los productos reales incorporan dialectos propios.

📚 Desarrollo

Marco normativo. El estándar ANSI SQL establece la normalización del lenguaje SQL como herramienta declarativa completa para definir, consultar, manipular y controlar datos en sistemas relacionales. Aunque se mantenga la denominación histórica ANSI, la referencia técnica internacional vigente corresponde a la familia de normas ISO/IEC 9075, desarrollada por organismos internacionales con participación activa de comités nacionales en su evolución continua.

Naturaleza declarativa. SQL se configura como lenguaje declarativo porque expresa únicamente el resultado deseado mediante relaciones y predicados lógicos, sin especificar algoritmos físicos de acceso ni estructuras de almacenamiento. El optimizador del sistema gestor determina internamente el plan de ejecución más eficiente, decidiendo entre el uso de índices, operaciones hash join o escaneos secuenciales para materializar el resultado solicitado.

Organización por categorías. El estándar estructura el lenguaje en grupos funcionales diferenciados que abarcan todas las operaciones sobre bases de datos: DDL para la definición de estructuras mediante instrucciones como CREATE TABLE y vistas, DML para la manipulación de datos con INSERT, UPDATE, DELETE y SELECT, DCL para el control de permisos mediante GRANT y REVOKE, y TCL para la gestión transaccional.

Evolución histórica. La primera normalización formal surgió en 1986 por ANSI y en 1987 por ISO, estableciendo la base semántica común. Sucesivas revisiones denominadas SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2008, SQL:2011 y SQL:2016 han ampliado progresivamente las capacidades del lenguaje, introduciendo elementos objeto-relacionales y consultas recursivas en 1999, además de soporte para XML, ventanas y JSON en ediciones posteriores.

Portabilidad y limitaciones. El estándar persigue reducir la dependencia de proveedores específicos, facilitando que aplicaciones y conocimientos sean transferibles entre sistemas gestores distintos. Sin embargo, la portabilidad absoluta no existe en entornos reales, pues cada motor incorpora dialectos y extensiones propietarias que solo operan correctamente dentro de su propio entorno, obligando a conocer el núcleo común antes de abordar particularidades específicas.

Componentes centrales. La instrucción SELECT constituye el mecanismo fundamental de consulta, mientras que las restricciones PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK y NOT NULL garantizan la integridad referencial y de dominio. Los valores NULL siguen una lógica de tres valores que requiere predicados específicos para su evaluación, diferenciándose del tratamiento de valores booleanos convencionales en las condiciones de búsqueda.

Separación de niveles. Una idea central del estándar es la distinción entre especificación lógica y ejecución física, coherente con el modelo relacional. El usuario trabaja conceptualmente con relaciones y predicados, mientras que el sistema se responsabiliza de decidir cómo materializar el resultado, ocultando los detalles de implementación y permitiendo que diferentes técnicas de almacenamiento sean transparentes para la consulta.

Objetivo de interoperabilidad. La estandarización busca que una parte relevante del conocimiento y las aplicaciones sea portable entre productos distintos, permitiendo migrar soluciones entre entornos heterogéneos. Esta interoperabilidad conceptual resulta valiosa para el diseño de sistemas, aunque en la práctica sea necesario distinguir entre el núcleo normalizado y las extensiones particulares de cada implementación comercial.

🧩 Elementos esenciales

• ISO/IEC 9075: Familia de normas que constituye la referencia técnica internacional actual del estándar SQL.
• ANSI 1986: Año de la primera estandarización formal por el American National Standards Institute.
• ISO 1987: Año de la publicación del estándar internacional correspondiente por ISO.
• Lenguaje declarativo: Paradigma que expresa el resultado deseado sin especificar el algoritmo de acceso físico.
• DDL: Categoría que agrupa instrucciones de definición de estructuras como CREATE TABLE, ALTER TABLE y DROP TABLE.
• DML: Conjunto de operaciones de manipulación de datos incluyendo INSERT, UPDATE, DELETE y SELECT.
• DCL: Subconjunto dedicado al control de permisos mediante instrucciones como GRANT y REVOKE.
• TCL: Lenguaje de control de transacciones que gestiona operaciones COMMIT y ROLLBACK.
• SQL:1999: Revisión histórica que introdujo elementos objeto-relacionales y consultas recursivas.
• SELECT: Instrucción central del lenguaje para la consulta y recuperación de información.
• Lógica de tres valores: Mecanismo de evaluación aplicado a los valores NULL que requiere predicados específicos.
• Restriciones de integridad: Reglas PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK y NOT NULL que protegen la consistencia de los datos.

🧠 Recuerda

• ANSI SQL es el nombre histórico, pero la norma vigente es ISO/IEC 9075.
• SQL es declarativo: indicas qué quieres, no cómo obtenerlo.
• El optimizador del sistema decide el plan de ejecución físico.
• DDL define, DML manipula, DCL autoriza y TCL controla transacciones.
• Existen revisiones periódicas desde 1986 hasta SQL:2016 y posteriores.
• La portabilidad total es un objetivo teórico dificultado por los dialectos propietarios.
• SELECT es la instrucción fundamental de consulta.
• Los valores NULL requieren predicados específicos por su lógica de tres valores.
• Las restricciones de clave protegen la integridad de la información.

3. Procedimientos almacenados

3. Procedimientos almacenados

🎯 Idea clave

• Un procedimiento almacenado es una rutina persistente guardada y ejecutada dentro del sistema gestor de bases de datos, no en el cliente.
• Se invoca explícitamente mediante la sentencia CALL, a diferencia de los triggers que se disparan automáticamente ante eventos.
• Puede contener sentencias SQL, variables, parámetros de entrada y salida, estructuras de control de flujo y tratamiento de errores según el motor.
• Su propósito principal es encapsular lógica próxima a los datos, facilitar la reutilización de operaciones y reducir código repetido en aplicaciones.
• Aunque existe el estándar SQL/PSM (ISO/IEC 9075-4), cada sistema gestor implementa su propio lenguaje procedimental, limitando la portabilidad.
• Ofrecen ventajas de rendimiento mediante planes precompilados y control de permisos, pero presentan desafíos en versionado y depuración.

📚 Desarrollo

Definición y naturaleza. Un procedimiento almacenado constituye una unidad de código persistente guardada en el sistema gestor de bases de datos y ejecutada bajo petición dentro del propio entorno del servidor. A diferencia de las consultas SQL estándar, puede agrupar múltiples instrucciones y controlar flujos de ejecución complejos.

Ejecución e invocación. La aplicación cliente invoca el procedimiento mediante una sentencia de llamada explícita, pasando parámetros y recibiendo resultados. El servidor ejecuta la rutina utilizando su motor, su lenguaje procedimental específico y los permisos configurados, lo que permite concentrar operaciones complejas en el servidor.

Estándar y dialectos. SQL estándar contempla los módulos persistentes mediante SQL/PSM (ISO/IEC 9075-4), aunque la implementación real varía significativamente entre productos. Oracle utiliza PL/SQL, SQL Server emplea Transact-SQL, PostgreSQL implementa PL/pgSQL, y MySQL y MariaDB disponen de su propia sintaxis específica.

Estructura y parámetros. La definición incluye nombre, lista de parámetros y cuerpo con delimitadores. Los parámetros pueden clasificarse como de entrada (IN), salida (OUT) o entrada/salida (INOUT), permitiendo comunicación bidireccional entre la aplicación y la rutina. Algunos sistemas también permiten devolver conjuntos de resultados completos.

Diferencia con funciones. Mientras que un procedimiento ejecuta acciones y se invoca mediante CALL, una función devuelve un valor escalar y puede utilizarse dentro de expresiones SQL. Esta distinción determina su ámbito de aplicación respectivo dentro del diseño de bases de datos.

Elementos procedimentales. Los lenguajes de procedimientos incluyen variables, estructuras condicionales (IF/CASE), bucles (LOOP/WHILE/FOR), gestión de excepciones mediante bloques TRY/CATCH o EXCEPTION, y manipulación de cursores para recorrer conjuntos de resultados fila a fila.

Ventajas e inconvenientes. Los procedimientos mejoran el rendimiento mediante planes de ejecución precompilados, reducen el tráfico de red y facilitan el control de permisos mediante privilegios EXECUTE. Sin embargo, limitan la portabilidad entre SGBD diferentes, complican el versionado y pueden convertir la base de datos en un sistema monolítico difícil de mantener.

🧩 Elementos esenciales

• SQL/PSM: Es la denominación estándar (ISO/IEC 9075-4) para los módulos persistentes almacenados, aunque su implementación teórica difiere de la práctica comercial.
• PL/SQL: Lenguaje procedimental de Oracle que incluye paquetes, tipos de objeto, colecciones y características avanzadas como transacciones autónomas.
• T-SQL: Dialecto propio de SQL Server y Sybase, caracterizado por bloques TRY...CATCH y variables prefijadas con arroba.
• PL/pgSQL: Lenguaje principal de PostgreSQL, sintácticamente cercano a PL/SQL pero con diferencias semánticas importantes.
• Parámetros IN: Aportan datos al procedimiento desde la aplicación llamadora sin posibilidad de modificación interna.
• Parámetros OUT: Devuelven valores desde el procedimiento hacia la aplicación, permitiendo comunicación de resultados.
• Parámetros INOUT: Combina ambas funcionalidades, recibiendo un valor inicial y pudiendo modificarse para retornar el resultado.
• Cursores: Estructuras que permiten recorrer conjuntos de resultados fila por fila mediante operaciones DECLARE, OPEN, FETCH y CLOSE.
• Encapsulamiento: Propiedad que permite ocultar la complejidad de operaciones multicapa exponiendo únicamente una interfaz simple de llamada.
• Inyección SQL: Riesgo de seguridad que no desaparece automáticamente al usar procedimientos, especialmente cuando se emplea SQL dinámico sin parametrización adecuada.

🧠 Recuerda

• Los procedimientos residen y se ejecutan en el servidor, no en el cliente.
• Se distinguen de los triggers porque requieren invocación explícita mediante CALL.
• La portabilidad entre diferentes SGBD es limitada debido a los dialectos propietarios.
• Los parámetros pueden ser de entrada, salida o entrada/salida según el diseño requerido.
• SQL/PSM es el estándar teórico, pero cada fabricante implementa su propia variante.
• Mejoran el rendimiento gracias a planes de ejecución precompilados y almacenados.
• Reducen el tráfico de red al ejecutar múltiples operaciones en una sola llamada.
• El uso de SQL dinámico dentro de procedimientos requiere validación y parametrización cuidadosa.
• La depuración suele ser menos potente que en lenguajes de programación de aplicaciones.
• Deben documentarse sus efectos, parámetros, permisos requeridos y comportamiento transaccional.

4. Eventos y disparadores

4. Eventos y disparadores

🎯 Idea clave

• Los eventos y disparadores son mecanismos que ejecutan lógica automáticamente ante hechos que ocurren en la base de datos o su entorno de ejecución.
• Un disparador o trigger es una rutina almacenada que se activa sin llamada explícita cuando se produce un evento definido sobre un objeto.
• Los triggers DML reaccionan a operaciones de manipulación de datos como INSERT, UPDATE o DELETE sobre tablas o vistas.
• Existen triggers DDL que responden a operaciones de definición de esquema como CREATE, ALTER o DROP, así como triggers de sesión LOGON y LOGOFF.
• Los triggers pueden configurarse para ejecutarse antes, después o en lugar de la operación que los dispara, con granularidad por fila o por sentencia.
• Su automatización permite implementar auditoría y reglas de negocio centralizadas, pero introduce riesgos de comportamiento oculto y dificultades de depuración.

📚 Desarrollo

Definición fundamental. Un disparador es un subprograma almacenado en el diccionario de datos que el sistema gestor asocia a un objeto específico, típicamente una tabla, vista, esquema o la propia instancia. Su característica definitoria radica en su modo de activación: el motor detecta automáticamente el evento y ejecuta la rutina sin necesidad de una llamada explícita mediante CALL desde la aplicación cliente.

Distinción con procedimientos. Mientras que un procedimiento almacenado requiere una invocación directa por parte del usuario o aplicación, un trigger se ejecuta automáticamente cuando ocurre el evento asociado. Esta transparencia operativa implica que, desde la perspectiva del cliente, la operación principal se ejecuta como si no existiera código adicional, aunque internamente se haya procesado lógica arbitrariamente compleja.

Tipología de eventos. Los triggers DML constituyen la categoría más extendida, reaccionando a modificaciones de datos sobre tablas o vistas. Paralelamente, los triggers DDL se activan ante operaciones de definición de esquemas como CREATE, ALTER o DROP, permitiendo auditoría de cambios estructurales y gobierno del esquema. Adicionalmente, existen triggers de sesión como LOGON y LOGOFF, útiles para registrar accesos, aplicar políticas de seguridad o configurar entornos.

Momentos de ejecución. La sintaxis estándar SQL:2003 define tres momentos de activación. Los triggers BEFORE ejecutan su lógica antes de materializar la operación en la tabla, permitiendo validaciones o modificaciones de valores entrantes. Los AFTER se ejecutan tras la persistencia, resultando idóneos para registrar auditoría o propagar cambios. Los INSTEAD OF sustituyen completamente la operación original, utilizándose principalmente en vistas complejas no actualizables directamente.

Granularidad de activación. Los triggers pueden actuar con dos niveles de granularidad. La opción FOR EACH ROW ejecuta el código una vez por cada fila afectada por la operación, mientras que FOR EACH STATEMENT lo hace una única vez por la sentencia que genera el evento, independientemente del número de registros modificados.

Riesgos y control. La automatización de triggers conlleva peligros significativos: efectos ocultos no visibles para el usuario de la sentencia principal, problemas de rendimiento derivados de ejecuciones masivas no anticipadas, riesgo de recursividad infinita si un trigger dispara otro, y dificultades para la depuración. Por ello, deben documentarse exhaustivamente, probarse rigurosamente y controlarse en transacciones y permisos.

🧩 Elementos esenciales

• Trigger DML: Disparador asociado a operaciones de manipulación de datos sobre tablas o vistas como INSERT, UPDATE o DELETE.
• Trigger DDL: Mecanismo que reacciona ante operaciones de definición de esquema como CREATE, ALTER o DROP para auditoría estructural.
• Eventos de sesión: Triggers LOGON y LOGOFF que se ejecutan al establecer o cerrar una conexión de usuario.
• Momento BEFORE: Ejecución previa a la operación que permite validar o modificar datos antes de su persistencia.
• Momento AFTER: Ejecución posterior a la operación útil para registrar auditoría o propagar cambios a otras tablas.
• Momento INSTEAD OF: Sustitución completa de la operación original, habitual en vistas complejas con joins o agregaciones.
• Granularidad por fila: Modalidad FOR EACH ROW que ejecuta el código una vez por cada registro afectado.
• Granularidad por sentencia: Modalidad FOR EACH STATEMENT que ejecuta el código una vez por cada instrucción SQL.
• Transparencia operativa: Característica que hace que la ejecución del trigger sea invisible al usuario de la sentencia principal.
• Riesgo de recursividad: Peligro de que un trigger dispare otro evento que active nuevamente un trigger causando bucles infinitos.

🧠 Recuerda

• Un trigger se ejecuta automáticamente sin llamada explícita cuando ocurre el evento asociado.
• Los triggers DML son los más comunes y actúan sobre INSERT, UPDATE y DELETE.
• BEFORE valida y modifica, AFTER audita e INSTEAD OF sustituye la operación original.
• Pueden activarse por cada fila modificada o una sola vez por sentencia completa.
• No deben usarse como sustituto de restricciones declarativas simples cuando estas bastan.
• Documentar y probar los triggers es esencial debido a sus efectos ocultos.
• Pueden generar problemas de rendimiento significativos en operaciones masivas.
• Existen triggers de sistema para eventos de servidor como arranque o parada de la instancia.