Declaración de variables:

DECLARE nombre_variable tipo_variable 
[DEFAULT valor];

Asignación de valor a una variable directamente:

SET nombre_variable = valor_variable;

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

SELECT campo1, campo2, . . . INTO variable1, variable2, . . . 
FROM nombre_tabla WHERE . . .

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

• Sentencia IF

IF condicion THEN
    sentencias;
[ELSEIF condicion2 THEN
    sentencias;] . . .
[ELSE 
    sentencias;]
END IF

• Sentencia CASE

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

• Sentencia LOOP

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

[etiqueta_inicio:] LOOP
    sentencias;
END LOOP [etiqueta_fin]

• Sentencia LEAVE

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

LEAVE etiqueta;

• Sentencia ITERATE

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

ITERATE etiqueta;

• Sentencia REPEAT

[etiqueta_inicio:] REPEAT
    sentencias;
UNTIL condicion
END REPEAT [etiqueta_fin]

• Sentencia WHILE

[etiqueta_inicio:] WHILE condicion DO
    sentencias;
END WHILE [etiqueta_fin]

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.

CREATE PROCEDURE nombre_procedimiento([param1 tipo, param2 tipo, . . .])
BEGIN
    sentencias;
END

CREATE FUNCTION nombre_funcion([param1 tipo, param2 tipo, . . .])
RETURNS tipo
BEGIN
    sentencias;
END

Ejemplos:

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

-- 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;

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

-- 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;

-- 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;

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.

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

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.

DELIMITER |
CREATE PROCEDURE ver_pistas()
BEGIN
    SELECT * FROM pistas;
END
|
DELIMITER ;

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

CALL ver_pistas();

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:

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;

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.

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.

-- 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;

-- 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;

-- 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;

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.

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.

1. Con las siguientes tablas, implementa los procedimientos/funciones que se enumeran a continuación

   Empleados (#id, nombre, apellidos, oficio, fecha_alta, salario,
             comision, -id_departamento)
   Departamentos (#id, nombre, ubicacion)

   • 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
2. 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
3. Dadas estas tablas, realiza los siguientes procedimientos/funciones:

   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))

   • 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
4. Realiza los siguientes procedimientos/funciones sobre estas tablas:

   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))

   • 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áctica 4.1 Procedimientos y funciones almacenadas en MySQL
• Práctica 4.2 Disparadores en MySQL

© 2016 Santiago Faci