Administración de Sistemas Gestores de Bases de Datos

Esta página es de solo lectura. Puedes ver la fuente pero no puedes cambiarla. Pregunta a tu administrador si crees que esto es incorrecto.
====== Programación de Procedimientos y Funciones ======

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

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


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

==== Salir de un procedimiento: Etiquetas ====

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

</code>

==== Función LAST_INSERT_ID() ====
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:

<code sql>
-- 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);

</code>
==== Ejecutar procedimientos y funciones ====
Usamos CALL para procedimientos y SELECT para funciones. Podemos usar una función en la cláusula SELECT de una consulta.

<code sql>
CALL nombre_procedimiento();

SELECT nombre_funcion();
</code>


==== Parámetros ====

  * 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.

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

</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 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.
==== 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, 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.

<code sql>
DELIMITER |

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

DELIMITER ;
</code>



{{ youtube>XQfPcx7YI0A }}
\\



===== Transacciones =====

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:

<code sql>
SET AUTOCOMMIT = 0;
</code>

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:
        
<code sql>
START TRANSACTION;
    UPDATE usuarios SET descuento = 0.10 WHERE ciudad = 'Zaragoza';
    UPDATE usuarios SET descuento = 0.20 WHERE ciudad = 'Teruel';
COMMIT;
</code>

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

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)
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;
</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.

----

(c) {{date> %Y}} Santiago Faci y Fernando Valdeón