Programación de Procedimientos y Funciones

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. (La consulta debe devolver una sola fila)

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;

-- 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)
BEGIN
 
    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);
 
END;

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

Si deseo terminar un procedimiento en un momento dado, puedo usar la sentencia LEAVE. Como podemos querer salir o terminar también un bucle, es necesario poner etiquetas a los bloques de código para indicar de qué bloque queremos salir.

-- 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; -- indico el bloque del que quiero salir
    END IF;
 
    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);
 
END procedimiento;

Cuando queremos ejecutar varias operaciones que modifican la base de datos es recomendable agruparlas en una Transacción. Aparte de esto, es posible que al una inserción en una tabla cuya clave primaria es AUTO_INCREMENT necesitemos conocer el valor del id generado, para poder utilizarlo en la siguiente inserción en caso de que las tablas estén relacionadas.

La función LAST_INSERT_ID() nos devuelve el valor del último campo AUTO_INCREMENT generado en nuestra sesión, manteniendo el control concurrente de diferentes usuarios a la misma base de datos:

-- Se desea insertar una nueva pista abierta. Para ello debo
-- insertar la pista en la tabla pistas, y también en la tabla
-- pistas abiertas:
 
-- Primero hago la inserción en la tabla padre: pistas
 
    INSERT INTO pistas (codigo, tipo, precio, id_polideportivo)
        VALUES (p_codigo, p_tipo, p_precio, p_id_polideportivo);
 
-- Después hago la inserción en la tabla hija: pistas_abiertas
-- En este caso necesito conocer el id que se generó en la inserción
-- anterior, ya que es clave ajena en esta tabla
 
    INSERT INTO pistas_abiertas (id_pista, operativa)
        VALUES (LAST_INSERT_ID(), TRUE);

Usamos CALL para procedimientos y SELECT para funciones. Podemos usar una función en la cláusula SELECT de una consulta.

CALL nombre_procedimiento();
 
SELECT nombre_funcion();

• IN: entrada, es el tipo de parámetro por defecto, por lo que si se omite, los parámetros serán de entrada.
• OUT: salida, cuando se indica delante de un parámetro, se suele utilizar para almacenar resultado en variables de sesión.
• INOUT: funciona como parámetro de entrada y además puede ser modificado obteniendo el valor de vuelta.

CREATE PROCEDURE cantidad_pedidos_por_estado (IN  p_estado_pedido VARCHAR(25)
                                               , OUT total INT)
BEGIN
	SELECT COUNT(*)
	INTO total
	FROM pedidos
	WHERE estado = p_estado_pedido;
END;
 
CALL cantidad_pedidos_por_estado('Enviado',@total);
SELECT @total;

• 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 o usuarios 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 al no tener que mandar multiples consultas, sino tan solo el nombre de un procedimiento o función.

• 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, ya 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 ;

Por defecto, en MySQL, se trabaja con la opción AUTOCOMMIT activada. Eso significa que cualquier operación de escritura sobre la base de datos se valida automáticamente a disco. Así, si queremos tener un cierto control sobre cuando se deben realizar esas validaciones a disco, podemos modificar esa opción con el siguiente comando:

SET AUTOCOMMIT = 0;

Así, cualquier operación que realicemos sobre tablas transaccionales (InnoDB) tendrá que ser validada mediante la instrucción COMMIT o bien ignorada mediante la instrucción ROLLBACK, que devolverá los datos de esas tablas al estado donde se validó la última transacción. Hay que tener en cuenta que si se trabaja con tablas MyISAM está opción no funcionará puesto que no tienen soporte para transacciones.

También se puede desactivar temporalmente la opción AUTOCOMMIT haciendo uso de la sentencia START TRANSACTION hasta que se validen o ignoren las instrucciones contenidas en la transacción:

START TRANSACTION;
    UPDATE usuarios SET descuento = 0.10 WHERE ciudad = 'Zaragoza';
    UPDATE usuarios SET descuento = 0.20 WHERE ciudad = 'Teruel';
COMMIT;

Si en un momento dado se quiere consultar el estado de la opción AUTOCOMMIT se puede realizar con la instrucción SHOW VARIABLES

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)
BEGIN
    -- Primero se declaran las variables necesarias
 
    -- Después se declara el Handler
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        -- Si se quiere mostrar un mensaje: (opcional)
        SELECT 'An error has occurred, operation rollbacked';
    END;
 
 
    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;

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.

© 2024 Santiago Faci y Fernando Valdeón