====== Programación de Bases de Datos ======

===== Introducción al lenguaje de programación =====

==== Variables ====

Declaración de variables:

<code sql>
DECLARE nombre_variable tipo_variable 
[DEFAULT valor];
</code>

Asignación de valor a una variable directamente:

<code sql>
SET nombre_variable = valor_variable;
</code>

Asignación de valor a una o más variables como resultado de una consulta. 

<code sql>
SELECT campo1, campo2, . . . INTO variable1, variable2, . . . 
FROM nombre_tabla WHERE . . .
</code>
==== Estructuras de control de flujo ====

Hay que destacar que MySQL, actualmente, no soporta bucles \emph{FOR}

  * Sentencia IF
    
<code sql>
IF condicion THEN
    sentencias;
[ELSEIF condicion2 THEN
    sentencias;] . . .
[ELSE 
    sentencias;]
END IF
</code>

  * Sentencia CASE
    
<code sql>
CASE variable
    WHEN valor1 THEN
        sentencias;
    [WHEN valor2 THEN
        sentencias;] . . .
    [ELSE 
        sentencias;]
END CASE

CASE
    WHEN condicion THEN
        sentencias;
    [WHEN condicion2 THEN
        sentencias;] . . .
    [ELSE 
        sentencias;]
END CASE
</code>

  * Sentencia LOOP

Los bucles //LOOP// no incoporan condición de salida, sino que debe ser implementada utilizando la instrucción ''LEAVE''

<code sql>
[etiqueta_inicio:] LOOP
    sentencias;
END LOOP [etiqueta_fin]
</code>

  * Sentencia LEAVE

Se utiliza para romper la ejecución de cualquier instrucción de control de flujo que se haya etiquetado, normalmente bucles //LOOP//

<code sql>
LEAVE etiqueta;
</code>

  * Sentencia ITERATE
    
Sólo puede aparecer en instrucciones de bucle (//{LOOP//, //REPEAT// y //WHILE//). Indica que el bucle debe volver a ejecutarse

<code sql>
ITERATE etiqueta;
</code>

  * Sentencia REPEAT

<code sql>
[etiqueta_inicio:] REPEAT
    sentencias;
UNTIL condicion
END REPEAT [etiqueta_fin]
</code>

  * Sentencia WHILE

<code sql>
[etiqueta_inicio:] WHILE condicion DO
    sentencias;
END WHILE [etiqueta_fin]
</code>
===== Procedimientos y funciones almacenadas =====

Los procedimientos y funciones almacenadas son conjuntos de comandos SQL que pueden ser almacenados en el servidor. Se asocia un nombre a un conjunto determinado de instrucciones para, posteriormente, ejecutar dicho conjunto tantas veces como se desee sin necesidad de volver a escribirlas.

<code sql>
CREATE PROCEDURE nombre_procedimiento([param1 tipo, param2 tipo, . . .])
BEGIN
    sentencias;
END
</code>

<code sql>
CREATE FUNCTION nombre_funcion([param1 tipo, param2 tipo, . . .])
RETURNS tipo
BEGIN
    sentencias;
END
</code>

Ejemplos:

<code sql>
-- Muestra toda la información sobre los usuarios
CREATE PROCEDURE lista_usuarios()
BEGIN
    SELECT * FROM usuarios;
END
</code>

<code sql>
-- Muestra la información sobre las reservas de una pista determinada 
-- (se pasa como parámetro)
CREATE PROCEDURE ver_pista_reserva(p_id_reserva INT)
BEGIN
    SELECT * FROM reservas WHERE id_pista = p_id_pista;
END;
</code>

Cuando el procedimiento almacenado va a realizar varias operaciones y éstas están relacionadas, es muy conveniente tratarlas como una transacción:

<code sql>
-- Procedimiento para dar de alta una nueva pista en un polideportivo 
-- determinado. Se pasan como parámetros todos los datos necesarios 
-- para dar de alta la nueva pista asumiendo que se trata de una 
-- pista abierta al público
CREATE PROCEDURE nueva_pista(p_codigo VARCHAR(10), p_tipo VARCHAR(255), 
    p_precio FLOAT, p_id_polideportivo INT)
procedimiento: BEGIN
    DECLARE existe_polideportivo TINYINT(1);

    SET existe_polideportivo = (SELECT COUNT(*) FROM polideportivos
                                WHERE id = p_id_polideportivo);
    IF existe_polideportivo = 0 THEN
        LEAVE procedimiento;
    END IF;

    START TRANSACTION;

    INSERT INTO pistas (codigo, tipo, precio, id_polideportivo)
        VALUES (p_codigo, p_tipo, p_precio, p_id_polideportivo);
    INSERT INTO pistas_abiertas (id_pista, operativa)
        VALUES (LAST_INSERT_ID(), TRUE);

    COMMIT;
END procedimiento;
</code>

<code sql>
-- Función que devuelva el número de reservas que ha realizado un usuario
-- determinado
CREATE FUNCTION get_numero_reservas(p_id_usuario INT)
RETURNS INT
BEGIN
    DECLARE cantidad INT;
    DECLARE existe_usuario INT;

    SET existe_usuario = (SELECT COUNT(*) FROM usuarios
                          WHERE id = p_id_usuario);
    IF existe_usuario = 0 THEN
        -- Si el usuario no existe se devuelve valor de error
        RETURN -1;
    END IF;

    -- Si todo va bien, se calcula la cantidad y se devuelve
    SET cantidad = (SELECT COUNT(*) FROM reservas R, usuario_reserva UR
        WHERE R.id = UR.id_reserva AND UR.id_usuario = p_id_usuario);
    RETURN cantidad;

END;
</code>
==== Ventajas ====

Resultan útiles, por ejemplo, en casos en los que varias aplicaciones diferentes trabajan sobre la misma Base de Datos y deben ejecutar el mismo código. En vez de escribir ese código para cada una de las diferentes aplicaciones, se puede escribir una sola vez en el servidor y ejecutarse desde las diferentes aplicaciones clientes

Además, aportan mayor seguridad puesto que las aplicaciones no necesitan acceder directamente a la información de la Base de Datos, sino que solamente acceden a ejecutar determinados procedimientos o funciones.

Y por último, se reduce el tráfico de red generado entre la aplicación cliente y el servidor.
==== Desventajas ====

Como principal desventaja se podría destacar la simplicidad del código que se puede utilizar en su implementación, puesto que no se pueden combinar con otros lenguajes más potentes como Java, C++, . . .


Además, resultan bastante complicados de escribir y mantener puesto que requieren conocimientos bastante precisos que no todos los programadores suelen tener
==== Creación de scripts ====

A la hora de implementar nuevos procedimientos y funciones hay que tener en cuenta algunas cuestiones. Puesto que el delimitador ';' se utiliza para finalizar cualquier orden sobre el motor MySQL, éste debe ser modificado mientras se implementa cualquier procedimiento o función, puesto que    cualquier instrucción //SQL// que forme parte del código, sería interpretada de forma independiente.

Así, la forma habitual de escribir procedimientos o funciones es a través de la creación de scripts //SQL// utilizando la orden ''DELIMITER'' que permite modificar el delimitador de fin de orden en MySQL. Por ejemplo, el código que se muestra a continuación permitiría crear un procedimiento desde un script (o bien pegando el código directamente en la consola). El delimitador de fin de orden se modifica al inicio del procedimiento y se vuelve a restaurar al final de la implementación de éste.

<code sql>
DELIMITER |
CREATE PROCEDURE ver_pistas()
BEGIN
    SELECT * FROM pistas;
END
|
DELIMITER ;
</code>

Una vez creado el procedimiento o función, desde un script o directamente en la consola, la forma de invocarlo es a través de la instrucción ''CALL'' seguido del nombre del procedimiento

<code sql>
CALL ver_pistas();
</code>

{{ youtube>XQfPcx7YI0A }}
\\
==== Control de errores ====

Como ocurre con muchos lenguajes de programación, //MySQL// también es capaz de gestionar, mediante excepciones, los errores que se puedan producir durante la ejecución de un fragmento de código en entornos transaccionales. En estos, si durante la ejecución de una transacción, se produce    algún fallo, es posible deshacer toda la operación para evitar inconsistencias en los datos.

Veamos un ejemplo, basándonos en un ejemplo anterior:

<code sql>
CREATE PROCEDURE nueva_pista(p_codigo VARCHAR(10), p_tipo VARCHAR(255), 
    p_precio FLOAT, p_id_polideportivo INT)
procedimiento: BEGIN
    DECLARE existe_polideportivo TINYINT(1);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    SET existe_polideportivo = (SELECT COUNT(*) FROM polideportivos
                                WHERE id = p_id_polideportivo);
    IF existe_polideportivo = 0 THEN
        LEAVE procedimiento;
    END IF;

    START TRANSACTION;

        INSERT INTO pistas (codigo, tipo, precio, id_polideportivo)
            VALUES (p_codigo, p_tipo, p_precio, p_id_polideportivo);
        INSERT INTO pistas_abiertas (id_pista, operativa)
            VALUES (LAST_INSERT_ID(), TRUE);

    COMMIT;
END procedimiento;
</code>

Con el control de las excepciones hemos conseguido controlar cualquier posible fallo que puedan generar las instrucciones de la transacción y, en su lugar, lanzar una orden ''ROLLBACK'' que deshará todos los pasos intermedios de dicha transacción que ya se hubieran ejecutado. Hay que tener en cuenta que el control de excepciones hace que el fallo nunca se propague hacia quién hubiera ejecutado este código, por lo que el programa que invoque a este código no se quedará en ningún estado inconsistente.
===== Eventos y disparadores =====

Los disparadores o triggers son procedimientos de la Base de Datos que se ejecutan o activan cada vez que ocurren un evento determinado sobre una tabla determinada, según se haya indicado en el momento de su implementación.

Los eventos que se pueden asociar a la ejecución de un trigger son 3: //INSERT//, //UPDATE// y //DELETE//. Y también puede decidirse que se activen antes o después del evento en cuestión, utilizando las palabras reservadas ''BEFORE'' y ''AFTER''. Por ejemplo, el disparador del    siguiente ejemplo se activará una vez por cada fila (''FOR EACH ROW'') actualizada (''UPDATE''). Y concretamente antes de que éstas se actualicen (''BEFORE'').

Las palabras ''NEW'' y ''OLD'' se emplean para referirse a las filas afectadas por el disparador, es decir, a las filas de la tabla sobre la que se activa, para referirse al estado de esa fila, antes (''OLD'') o después (''NEW'') de haber actuado el disparador. En un disparador //INSERT// sólo se podrá utilizar la palabra //NEW// puesto que no hay versión anterior de esa fila, en un disparador    //UPDATE// se podrá acceder a cada fila antes (//OLD//) y después (//NEW//) de haber sido actualizada; y en un disparador //DELETE// sólo se puede utilizar la palabra //OLD// puesto que no nueva versión de la fila.

<code sql>
-- Calcula automáticamente la edad de los usuarios
-- en el mismo momento en el que se dan de alta
-- a partir de la fecha de nacimiento que introduzca 
-- el usuario
CREATE TRIGGER nuevo_usuario BEFORE INSERT ON usuarios
FOR EACH ROW
BEGIN
    IF NEW.fecha_nacimiento IS NOT NULL THEN
        SET NEW.edad = YEAR(CURRENT_DATE()) - YEAR(NEW.fecha_nacimiento);
    END IF;
END;
</code>

<code sql>
-- Actualiza la fecha de última reserva de una pista
-- cada vez que ésta se reserva
CREATE TRIGGER anota_ultima_reserva AFTER INSERT ON reservas
FOR EACH ROW
BEGIN
    UPDATE pistas_abiertas
        SET fecha_ultima_reserva = CURRENT_TIMESTAMP()
        WHERE id_pista = NEW.id_pista;
END;
</code>

<code sql>
-- Registra una pista como pista clausurada al público cuando 
-- ésta se elimina de la Base de Datos
CREATE TRIGGER retira_pista AFTER DELETE ON pistas_abiertas
FOR EACH ROW
BEGIN
    INSERT INTO pistas_cerradas (id_pista, fecha_clausura, motivo)
        VALUES (OLD.id_pista, CURRENT_TIMESTAMP(), 'Eliminada');
END;
</code>

Hay que tener en cuenta que toda columna precedida por \emph{OLD} es de sólo lectura, por lo que se podrá hacer referencia a ella pero sólo para leerla. En el caso de las columnas precedidas por ''NEW'', tal y como se muestra en el ejemplo anterior, se podrá leer y también modificar su    valor con la instrucción ''SET''.

==== Usos y limitaciones de los disparadores ====

Antes de comenzar a utilizar los disparadores, conviene conocer cuándo deben ser utilizados, y cuáles son sus limitaciones.

Uno de los usos más comunes de los disparadores es el utilizarlos para mantener actualizados los campos calculados, de manera que cuando ocurra algún cambio en los datos se pueda actualizar automaticamente dicho campo calculado, si tuviera que verse afectado.

Además, permiten realizar tareas de auditoría, puesto que es posible registrar la actividad que ocurre en una o varias tablas en otra tabla, con el fin de registrar las operaciones que se realizan sobre ella, cuando se hacen, quién las hace, . . .

En cuanto a las limitaciones, cabe destacar que desde un disparador no es posible invocar a un procedimiento almacenado.

----
===== Ejercicios =====

{{ ejercicio.png}}

  - Con las siguientes tablas, implementa los procedimientos/funciones que se enumeran a continuación <code>
Empleados (#id, nombre, apellidos, oficio, fecha_alta, salario,
          comision, -id_departamento)
Departamentos (#id, nombre, ubicacion)
</code>
    * Muestra todos los empleados de un departamento determinado
    * Da de alta un empleado
    * Da de baja un empleado
    * Sube el salario a todos los empleados de un determinado departamento
    * Función que devuelva el salario total de los empleados de un departamento determinado
    * Función que devuelva el número de empleados que trabajan en un departamento determinado
  - Sobre el modelo relacional del Ejercicio 1 del tema anterior, realiza los siguientes procedimientos/funciones:
    * Da de alta un jugador
    * Da de alta un equipo
    * Registra el resultado de un partido
    * Elimina un jugador
    * Anota las incidencias de un partido determinado
    * Función que devuelva el número de goles que un equipo ha metido en un partido
    * Función que devuelva el número de goles que un equipo ha metido a otro equipo, comprobando que ambos equipos existen
  - Dadas estas tablas, realiza los siguientes procedimientos/funciones:<code>
Empleados (#id, dni, nombre, salario)
Vendedores (#id, nro_vendedor, zona, -id_empleado)
Polizas (#id, nro_poliza, importe, beneficiario, -id_vendedor, fecha, 
         fecha_vencimiento)
Empleado_Jefe (#(-id_empleado, -id_jefe))
</code>
    * Da de alta un empleado como vendedor. Comprueba que no existe otro empleado con el mismo DNI
    * Asigna un jefe a un empleado determinado. Comprueba que ambos existen
    * Registra una nueva póliza, comprobando si existe el vendedor que se le asigna
    * Elimina una póliza determinado. Comprueba antes que existe
    * Función que devuelva el número de vendedores
    * Función que devuelva cuantas pólizas tiene asignadas un vendedor determinado. Comprobar si existe el vendedor
    * Función que compruebe si un determinado empleado es jefe de otro empleado
    * Función que devuelva cuantos vendedores hay en una zona determinada
  - Realiza los siguientes procedimientos/funciones sobre estas tablas:<code>
Autores (#id, nombre, fecha_nacimiento, fecha_fallecimiento, 
         nacionalidad)
Obras (#id, titulo, fecha, -id_museo)
Museos (#id, nombre, direccion, ciudad, pais)
Obra_Autor (#(-id_obra, -id_autor))
</code>
    * Da de alta un autor. Comprueba que no existe anteriormente
    * Da de alta una obra. Comprueba que no existe anteriormente y que el museo asignado existe
    * Asigna una obra determinado a un autor
    * Elimina un autor. Si éste tiene obras asignadas no podrá ser eliminado
    * Elimina una obra. Si está asignada a algún autor, se desvinculará antes de éste
    * Modifica el museo de una obra. Comprueba que el nuevo museo existe
    * Elimina un museo. Asigna todas las obras de dicho museo a otro determinado
    * Función que devuelva el número de obras de un museo determinado. Comprobar que existe dicho museo
    * Función que devuelva el número de obras que ha creado un autor determinado. Comprobar que existe dicho autor
    * Función que devuelva el autor de una obra determinada

===== Prácticas =====

  * **Práctica 4.1** Procedimientos y funciones almacenadas en MySQL
  * **Práctica 4.2** Disparadores en MySQL

----

(c) 2016-2020 Santiago Faci