UT6: PROGRAMACIÓN

DE BASES DE DATOS

Néstor Sabater Hernández

1. INTRODUCCIÓN
• En esta unidad vamos a estudiar las rutinas que pueden existir en BBDD. Estas rutinas pueden ser:

• Procedimientos:
• Se trata de un conjunto de instrucciones SQL que se guardan en el servidor y se ejecutan cuando ese procedimiento es llamado.

• Funciones:
• Son subrutinas que devuelven un valor.

• Triggers:
• Es un procedimiento que se ejecuta automáticamente cuando se cumple una determinada condición.
• Los triggers pueden utilizarse para sentencias INSERT, UPDATE y DELETE.
• Se utilizan para prevenir errores y mejorar la administración de la base de datos sin necesidad de que el usuario ejecute la
sentencia SQL, ya que se ejecuta automáticamente en función de que se cumpla una determinada condición
2. PROCEDIMIENTOS

• Son rutinas o subprogramas

• Compuestos por un conjunto nombrado de sentencias SQL agrupados lógicamente para realizar
una tarea específica
• Se guardan en la base de datos
• Se ejecutan como una unidad cuando son invocados por su nombre.

• Para crear un procedimiento, MySQL ofrece la directiva CREATE PROCEDURE.

• Al crearlo, éste, es asociado con la base de datos en uso, tal como cuando creamos una tabla.
2. PROCEDIMIENTOS

• Un procedimiento puede tener cero o muchos parámetros de entrada y cero o muchos

parámetros de salida.
2. PROCEDIMIENTOS

• Estas son las características principales de los procedimientos almacenados MySQL:

• Pueden recibir y devolver parámetros.

• Pueden manejar tablas, ejecutando operaciones e iteraciones de lectura/escritura.
• Pueden mostrar una tabla como resultado.
• Se almacenan en la base de datos en la cual se crean.
• No dependen de ninguna tabla en particular.
• Pueden aceptar recursividad..
2. PROCEDIMIENTOS

• Programa iterativo VS recursivo

ITERATIVO RECURSIVO
2. PROCEDIMIENTOS

• Sintaxis
El cuerpo de la rutina:
BEGIN
• Puede haber más de un parámetro (se separan …
END
con comas) o puede no haber ninguno
• (En este caso deben seguir presentes los
paréntesis, aunque no haya nada dentro).

• Cuerpo de la rutina – es el cuerpo del

procedimiento y está compuesto por el
procedimiento en sí: aquí se define qué hace,
cómo lo hace y bajo qué circunstancias lo
hace.

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
2. PROCEDIMIENTOS

• Sintaxis

Dentro de características es posible incluir comentarios o

definir si el procedimiento obtendrá los mismos resultados
ante entradas iguales, entre otras cosas.

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
2. PROCEDIMIENTOS

• DELIMITER

• En MySQL las sentencias se ejecutan después de escribir el punto y coma (;)

• Para definir un procedimiento almacenado es necesario modificar temporalmente el carácter separador que
se utiliza para delimitar las sentencias SQL.

• Esto se hace para evitar que se ejecute al encontrar el símbolo (;) tenemos que asignar la función de
delimitador a otro carácter, como por ejemplo la barra (|) o (//) o ($).

• Al finalizar asignaremos al punto y coma su función habitual.

2. PROCEDIMIENTOS

• DELIMITER

• En este ejemplo estamos configurando los caracteres $$ como los separadores entre las sentencias
SQL.

• DELIMITER $$

• En este ejemplo volvemos a configurar que el carácter separador es el punto y coma.

• DELIMITER ;
2. PROCEDIMIENTOS

• DELIMITER - EJEMPLO

• Obtener un listado de todos los clientes y otro de todos los vehículos de un taller. (La base de datos
deberá estar abierta).

• Ejemplo de procedimiento en el que no hay parámetros. DELIMITER |

• Aunque no hay parámetros el uso de paréntesis es obligatorio. CREATE PROCEDURE Listados()
BEGIN
SELECT * FROM clientes;
SELECT * FROM vehiculos;
END |
DELIMITER ;
2. PROCEDIMIENTOS
• Parámetros de entrada, salida y entrada/salida

• En los procedimientos almacenados podemos tener tres tipos de parámetros:

• Entrada: Se indican poniendo la palabra reservada IN delante del nombre del parámetro. Estos parámetros no pueden
cambiar su valor dentro del procedimiento, es decir, cuando el procedimiento finalice estos parámetros tendrán el mismo
valor que tenían cuando se hizo la llamada al procedimiento. En programación sería equivalente al paso por valor de un
parámetro.

• Salida: Se indican poniendo la palabra reservada OUT delante del nombre del parámetro. Estos parámetros cambian su
valor dentro del procedimiento. Cuando se hace la llamada al procedimiento empiezan con un valor inicial y cuando
finaliza la ejecución del procedimiento pueden terminar con otro valor diferente. En programación sería equivalente al paso
por referencia de un parámetro.

• Entrada/Salida: Es una combinación de los tipos IN y OUT. Estos parámetros se indican poniendo la palabra reservada
IN/OUT delante del nombre del parámetro.
2. PROCEDIMIENTOS
• Parámetros

• Los parámetros tienen la siguiente estructura:

• Modo-Nombre-Tipo

• Modo: es opcional y puede ser

• IN (el valor por defecto, son los parámetros que el procedimiento recibirá),
• OUT (son los parámetros que el procedimiento podrá modificar)
• INOUT (mezcla de los dos anteriores).

• Nombre: es el nombre del parámetro.

• Tipo: es cualquier tipo de dato de los provistos por MySQL (INT, VARCHAR, DATE,...).
2. PROCEDIMIENTOS

• Parámetros - EJEMPLOS

• EJEMPLO1: En este ejemplo, se muestra la cabecera de un procedimiento llamado listar_productos

que sólo tiene el parámetro gama que es de entrada (IN).

• EJEMPLO2: Aquí se muestra la cabecera de un procedimiento llamado contar_productos que tiene

el parámetro gama de entrada (IN) y el parámetro total de salida (OUT).
2. PROCEDIMIENTOS

• Parámetros

• Ejemplo de procedimiento con parámetros de entrada

• Buscar los clientes que contengan una letra pasada como parámetro de entrada y mostrarlos.
• BD FilmStore
2. PROCEDIMIENTOS

• Parámetros

• Ejemplo de procedimiento con parámetros de entrada

• Buscar los clientes que contengan una letra pasada como parámetro de entrada y mostrarlos.
• BD FilmStore
2. PROCEDIMIENTOS

• Parámetros

• Ejemplo de procedimiento con parámetros de entrada

• Buscar los clientes que contengan una letra pasada como parámetro de entrada y mostrarlos.
• BD FilmStore
2. PROCEDIMIENTOS

• Parámetros

• Ejemplo de procedimiento con parámetros de entrada

• Escriba un procedimiento llamado listar_productos() que reciba como entrada el nombre de la gama y
muestre un listado de todos los productos que existen dentro de esa gama. Este procedimiento no devuelve
ningún parámetro de salida, lo que hace es mostrar el listado de los productos.
• BD jardinería
2. PROCEDIMIENTOS

• Parámetros

• Ejemplo de procedimiento con parámetros de entrada

• Escriba un procedimiento llamado listar_productos() que reciba como entrada el nombre de la gama y
muestre un listado de todos los productos que existen dentro de esa gama. Este procedimiento no devuelve
ningún parámetro de salida, lo que hace es mostrar el listado de los productos.
• BD jardinería DELIMITER $$
DROP PRECEDURE IF EXISTS listar_productos$$
CREATE PROCEDURE listar_productos( IN gama VARCHAR(50))
BEGIN
SELECT * FROM producto
WHERE producto.gama = gama;
END$$
DELIMITER ;
2. PROCEDIMIENTOS
• Para ejecutar un procedimiento, una vez almacenado, usaremos la sentencia:
• CALL NombreBD.NombreProcedimiento(parámetros);

• En el ejemplo del listado de clientes ejecutaremos:

• CALL Listados();

• Para almacenar los resultados de una consulta directamente en variables usamos la sentencia:
• SELECT NombreColumnas de la consulta [, …] INTO NombreVariables [, …] from…

• De esta manera dejamos los valores devueltos en las variables indicadas en lugar de mostrarlos en pantalla. Para hacer
esto la consulta debe devolver una sola fila y un solo dato porque no se puede asignar a una variable una lista de
contenidos.

https://dev.mysql.com/doc/refman/8.0/en/call.html
2. PROCEDIMIENTOS

• Parámetros de salida

• Cuando un procedimiento utiliza un parámetro de salida para llamar al procedimiento, es necesario pasar
una variable que cargue el dato devuelto por el procedimiento.

• Para definir una variable desde la línea de comandos, que será una variable temporal de sistema, la
variable debe tener el nombre precedido del carácter @ y se usa la sentencia:
• SET @NombreVariable=Valor;

• Ejemplos: En temas anteriores utilizamos select @x := 5

• SET @Edad=23;
• SET @Fecha=' 2011-03-16’; Se trata de otra notación.
• SET @Nombre='Alejandra'; Si utilizamos select debemos utilizar :=

https://dev.mysql.com/doc/refman/8.0/en/user-variables.html
2. PROCEDIMIENTOS
• Sentencia SET

• Se utiliza para asignar un valor a cualquier variable, ya sea local, global o pasada como parámetro.

• Las variables que se asignan con SET pueden declararse dentro de una rutina o como variables globales de servidor.

• Para modificar el valor de una variable o de un parámetro utilizando una asignación debe utilizarse la siguiente Sintaxis:
• SET NombreVariable=Expresión;

• En Expresión puede haber una constante, una función, una variable, una operación entre ellas o incluso una sentencia
SELECT que devuelva un solo resultado.

• Ejemplo:
• SET m=(SELECT Marca FROM VEHICULOS WHERE Matricula=’4884AB’);
2. PROCEDIMIENTOS

• Parámetros de salida

• También se puede crear una variable de sistema especificándola como parámetro en la llamada a un
procedimiento, tanto si ya se tenía creada la anterior variable @Num como si se crea en la propia
llamada al procedimiento:
• CALL NombreProcedimiento(@Num);

• Una vez cargado ese valor @Num se puede usar en cualquier sentencia como por ejemplo:
• SELECT @Num;
2. PROCEDIMIENTOS

• Ejemplo de procedimiento con parámetros de entrada y salida

• Buscar los clientes que contengan una letra pasada como parámetro de entrada y devolver el número
de clientes que contienen esa letra.

• BD FilmStore
2. PROCEDIMIENTOS

• Ejemplo de procedimiento con parámetros de entrada y salida

• Buscar los clientes que contengan una letra pasada como parámetro de entrada y devolver el número
de clientes que contienen esa letra.

• BD FilmStore
2. PROCEDIMIENTOS

• EJEMPLO

• Utilizar la BD clientes-pagos

• Crear un procedimiento con el nombre de listados que muestre contenido de todas las tablas de la
base de datos.

• Ejecutar el procedimiento
2. PROCEDIMIENTOS

• EJEMPLO

• Utilizar la BD clientes-pagos

• Crear un procedimiento con el nombre de listados que muestre contenido de todas las tablas de la
base de datos.
Recuerda que la primera línea
• Ejecutar el procedimiento es para decirle a MySQL que
a partir de ahora hasta que no
introducimos // no se acaba
la sentencia.
2. PROCEDIMIENTOS

• EJEMPLO

• Escriba un procedimiento llamado contar_productos() que reciba como entrada el nombre de la gama
y devuelva el número de productos que existen dentro de esa gama. Resuelva el ejercicio de dos
formas distintas, utilizando SET y SELECT ... INTO.

• BD JARDINERÍA
2. PROCEDIMIENTOS

• EJEMPLO

• Escriba un procedimiento llamado

contar_productos() que reciba como entrada
el nombre de la gama y devuelva el número
de productos que existen dentro de esa gama.
Resuelva el ejercicio de dos formas distintas,
utilizando SET y SELECT ... INTO.

• BD JARDINERÍA
2. PROCEDIMIENTOS

• EJEMPLO

• Escriba un procedimiento llamado

contar_productos() que reciba como entrada
el nombre de la gama y devuelva el número
de productos que existen dentro de esa gama.
Resuelva el ejercicio de dos formas distintas,
utilizando SET y SELECT ... INTO.

• BD JARDINERÍA
2. PROCEDIMIENTOS

• EJEMPLO

• Escribe un procedimiento que se llame calcular_max_min_media, que reciba como parámetro de

entrada el nombre de la gama de un producto y devuelva como salida tres parámetros. El precio
máximo, el precio mínimo y la media de los productos que existen en esa gama. Resuelva el ejercicio
de dos formas distintas, utilizando SET y SELECT ... INTO.

• BD JARDINERÍA
2. PROCEDIMIENTOS

• EJEMPLO

• Escribe un procedimiento que se llame calcular_max_min_media, que reciba como parámetro de

entrada el nombre de la gama de un producto y devuelva como salida tres parámetros. El precio
máximo, el precio mínimo y la media de los productos que existen en esa gama. Resuelva el ejercicio
de dos formas distintas, utilizando SET y SELECT ... INTO.

• BD JARDINERÍA
2. PROCEDIMIENTOS

• EJEMPLO

• Escribe un procedimiento que se llame

calcular_max_min_media, que reciba como
parámetro de entrada el nombre de la gama de
un producto y devuelva como salida tres
parámetros. El precio máximo, el precio mínimo
y la media de los productos que existen en esa
gama. Resuelva el ejercicio de dos formas
distintas, utilizando SET y SELECT ... INTO.

• BD JARDINERÍA
2. PROCEDIMIENTOS

• EJEMPLO

• Escribe un procedimiento que se llame

calcular_max_min_media, que reciba como
parámetro de entrada el nombre de la gama de
un producto y devuelva como salida tres
parámetros. El precio máximo, el precio mínimo
y la media de los productos que existen en esa
gama. Resuelva el ejercicio de dos formas
distintas, utilizando SET y SELECT ... INTO.

• BD JARDINERÍA
EJERCICIO 1

• BD clientes-pagos

• Crear un procedimiento con el nombre de pagosCasas que muestre los pagos pendientes del
cliente con dni 44444444R.

• Ejecutar el procedimiento
EJERCICIO 1

• BD clientes-pagos

• Crear un procedimiento con el nombre de pagosCasas que muestre los pagos pendientes del
cliente con dni 44444444R.

• Ejecutar el procedimiento
EJERCICIO 2

• BD clientes-pagos

• Crear un procedimiento que reciba como parámetro de entrada el dni de un cliente y nos
muestre el importe total que debe.
EJERCICIO 2

• BD clientes-pagos

• Crear un procedimiento que reciba como parámetro de entrada el dni de un cliente y nos
muestre el importe total que debe.
EJERCICIO 3

• BD clientes-pagos

• Crear un procedimiento que reciba como parámetro de entrada el dni de un cliente y nos
muestre el número de pagos pendientes. Mostrar el resultado como “Nº de pagos pendientes”
y llamar al procedimiento cuantospagos.
EJERCICIO 3

• BD clientes-pagos

• Crear un procedimiento que reciba como parámetro de entrada el dni de un cliente y nos
muestre el número de pagos pendientes. Mostrar el resultado como “Nº de pagos pendientes”
y llamar al procedimiento cuantospagos.
EJERCICIO 4

• BD clientes-pagos

• Crear un procedimiento que reciba como parámetro de entrada el dni de un cliente y nos
muestre la información del cliente, y como parámetro de salida el número de pagos pendientes.

• Ejecutar el procedimiento.

• Mostrar el valor devuelto

EJERCICIO 4

• BD clientes-pagos

• Crear un procedimiento que reciba como parámetro de entrada el dni de un cliente y nos
muestre la información del cliente, y como parámetro de salida el número de pagos pendientes.

• Ejecutar el procedimiento.

• Mostrar el valor devuelto

EJERCICIO 5

• BD clientes-pagos

• Crear un procedimiento devuelveDNI() que reciba como parámetro de entrada el nombre de

un cliente y como parámetro de salida su dni.

• Ejecutar el procedimiento

• Mostrar el valor devuelto

EJERCICIO 5

• BD clientes-pagos

• Crear un procedimiento devuelveDNI() que reciba como parámetro de entrada el nombre de

un cliente y como parámetro de salida su dni.

• Ejecutar el procedimiento

• Mostrar el valor devuelto

2. PROCEDIMIENTOS

• Declaración de variables

• Dentro de cada procedimiento se pueden definir variables locales, es decir, que sólo existen
mientras se ejecuta el procedimiento y después se destruyen.

• Las variables locales únicamente son visibles dentro del bloque BEGIN … END donde estén
declaradas, y deben estar al comienzo de este bloque, antes de cualquier sentencia.

• Para declarar variables locales se utiliza la sintaxis:

https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html
2. PROCEDIMIENTOS

• Declaración de variables

• Para proporcionar un valor inicial a la variable declarada se utiliza DEFAULT.

• El valor inicial puede ser una constante o una expresión.

• En caso de no emplear DEFAULT el valor por defecto es NULL.

• En cuanto al tipo, puede utilizarse cualquier tipo válido en MySQL.

• Para cada variable que se declara es necesario utilizar una sentencia DECLARE distinta.
EJERCICIO 6

• BD clientes-pagos

• Realizar un procedimiento que reciba como parámetro de entrada el nombre de un cliente y

que muestre la información de sus pagos pendientes.

• Utilizar la declaración de variables.

EJERCICIO 6

• BD clientes-pagos
• Realizar un procedimiento que reciba como parámetro de entrada el nombre de un cliente y
que muestre la información de sus pagos pendientes.
• Utilizar la declaración de variables.
EJERCICIO 7

• BD clientes-pagos

• Realizar un procedimiento que reciba como parámetro de entrada el nombre de un cliente y

que muestre su nombre, su dni y el importe total de debe en una misma tabla.

• Utilizar la declaración de variables.

• Se puede resolver con un inner join y sin usar variables

EJERCICIO 7

• BD clientes-pagos

• Realizar un procedimiento que reciba como parámetro de entrada el nombre de un cliente y

que muestre su nombre, su dni y el importe total de debe en una misma tabla.

• Utilizar la declaración de variables.

• Se puede resolver con un inner join y sin usar variables

2. PROCEDIMIENTOS

• Eliminar procedimientos

• Ejemplos:

• DROP PROCEDURE IF EXISTS listados;

• DROP PROCEDURE listados;

2. PROCEDIMIENTOS

• Listar procedimientos

• Mostrar todos los procedimientos del sistema que empiecen por L:

• SHOW PROCEDURE STATUS LIKE ‘L%’;

• Mostrar cómo se creó el procedimiento:

• SHOW CREATE PROCEDURE listados;
2. PROCEDIMIENTOS

• Ventajas

• Mejora la Seguridad
• Cuando llamamos a un procedimiento almacenado, este deberá realizar todas las comprobaciones pertinentes de
seguridad.

• Mejora el Rendimiento
• Es capaz de trabajar más rápido con los datos que cualquier lenguaje del lado del servidor, y llevará a cabo las tareas
con más eficiencia.
• Solo realizamos una conexión al servidor y este ya es capaz de realizar todas las comprobaciones sin tener que
volver a establecer una conexión.

• Permiten reusar código

2. PROCEDIMIENTOS

• Desventajas

• El programa se guarda en la BD, por lo tanto, si se corrompe y perdemos la información también

perderemos nuestros procedimientos.
• Esto es fácilmente subsanable llevando a cabo una buena política de respaldos de la BD.

• Tener que aprender un nuevo lenguaje.

• Esto es siempre un problema, sobre todo si no tienes tiempo.
2. PROCEDIMIENTOS

• Desventajas

• Se puede modificar el procedimiento, pero lo

único que puedes modificar será lo
correspondiente a:

• La estructura y su funcionamiento no se puede

cambiar con alter procedure, para ello hay que
borrar el procedimiento y volver a crearlo.
EJERCICIOS – PROCEDIMIENTOS SIN SENTENCIAS
SQL
1. Escribe un procedimiento que no tenga ningún parámetro de entrada ni de salida y que muestre el texto ¡Hola
mundo!.
2. Escribe un procedimiento que reciba un número real de entrada y muestre un mensaje indicando si el número
es positivo, negativo o cero.
3. Modifique el procedimiento diseñado en el ejercicio anterior para que tenga un parámetro de entrada, con el
valor un número real, y un parámetro de salida, con una cadena de caracteres indicando si el número es
positivo, negativo o cero.
4. Escribe un procedimiento que reciba un número real de entrada, que representa el valor de la nota de un
alumno, y muestre un mensaje indicando qué nota ha obtenido teniendo en cuenta las siguientes condiciones:
• [0,5) = Insuficiente
• [5,6) = Aprobado
• [6, 7) = Bien
• [7, 9) = Notable
EJERCICIOS – PROCEDIMIENTOS CON SENTENCIAS
SQL
1. Escribe un procedimiento que reciba el nombre de un país como parámetro de entrada y
realice una consulta sobre la tabla cliente para obtener todos los clientes que existen en la
tabla de ese país.
• Modificar el procedimiento y contar el nº de clientes y si es superior a un número dado como media
establecer en una variable de salida ‘Es superior a la media’.
2. Escribe un procedimiento que reciba como parámetro de entrada una forma de pago, que será
una cadena de caracteres (Ejemplo: PayPal, Transferencia, etc). Y devuelva como salida el
pago de máximo valor realizado para esa forma de pago. Deberá hacer uso de la tabla pago
de la base de datos jardineria.
EJERCICIOS – PROCEDIMIENTOS CON SENTENCIAS
SQL
3. Escribe un procedimiento que reciba como parámetro de entrada una forma de pago, que será una cadena de
caracteres (Ejemplo: PayPal, Transferencia, etc). Y devuelva como salida los siguientes valores teniendo en cuenta la
forma de pago seleccionada como parámetro de entrada:
• El pago de máximo valor
• El pago de mínimo valor
• El valor medio de los pagos realizados
• La suma de todos los pagos
• El número de pagos realizados para esa forma de pago.
4. Crea una base de datos llamada procedimientos que contenga una tabla llamada cuadrados. La tabla cuadrados debe
tener dos columnas de tipo INT UNSIGNED, una columna llamada número y otra columna llamada cuadrado.
Una vez creada la base de datos y la tabla, deberá crear un procedimiento llamado calcular_cuadrados con las
siguientes características. El procedimiento recibe un parámetro de entrada llamado tope de tipo INT UNSIGNED y
calculará el valor de los cuadrados de los primeros números naturales hasta el valor introducido como parámetro. El
valor del números y de sus cuadrados deberán ser almacenados en la tabla cuadrados que hemos creado
previamente.
3. ESTRUCTURAS DE CONTROL DE FLUJO

• IF

• Estructura condicional básica

• SI se cumple la condición ENTONCES ….

• SI NO se cumple …..
3. ESTRUCTURAS DE CONTROL DE FLUJO

• IF

• EJEMPLO
EJERCICIO 8

• Realizar un procedimiento que reciba como parámetro de entrada el nombre de un director y

que devuelva en una variable de salida su edad si tiene 80 años o más.

• NOTA: timestampdiff devuelve la diferencia entre dos fechas pasadas por parámetro. Si
además de las fechas recibe (en primer lugar) otro parámetro indicando year transformará la
cuenta a años.

• BD filmStore
EJERCICIO 8

• Realizar un procedimiento que reciba como parámetro de entrada el nombre de un director y

que devuelva en una variable de salida su edad si tiene 80 años o más.

• NOTA: timestampdiff devuelve la diferencia entre dos fechas pasadas por parámetro. Si
además de las fechas recibe (en primer lugar) otro parámetro indicando year transformará la
cuenta a años.

• BD filmStore
3. ESTRUCTURAS DE CONTROL DE FLUJO

• CASE – SINTAXIS 1

• Estructura condicional compleja

• case_value es una expresión

• Este valor se compara con when_value hasta que uno de ellos es igual.

• Cuando se encuentra un when_value igual se ejecuta el correspodiente statement_list

https://dev.mysql.com/doc/refman/8.0/en/flow-control-statements.html
3. ESTRUCTURAS DE CONTROL DE FLUJO

• CASE – SINTAXIS 2

• Estructura condicional compleja

• Cada expresión search_condition es evaluada hasta que una es verdadera

• Entonces se ejecuta la cláusula statement_list

• Si ninguna search_condition se ejecuta statement_list de la cláusula ELSE (si existe)

https://dev.mysql.com/doc/refman/8.0/en/flow-control-statements.html
3. ESTRUCTURAS DE CONTROL DE FLUJO

• CASE

• Existen dos formas de utilizar CASE:

https://dev.mysql.com/doc/refman/8.0/en/case.html
3. ESTRUCTURAS DE CONTROL DE FLUJO

• CASE

• EJEMPLO
3. ESTRUCTURAS DE CONTROL DE FLUJO

• WHILE

• BUCLE

• statement_list se repite mientras search_condition sea verdadero

https://dev.mysql.com/doc/refman/8.0/en/flow-control-statements.html
3. ESTRUCTURAS DE CONTROL DE FLUJO

• WHILE

• EJEMPLO
3. ESTRUCTURAS DE CONTROL DE FLUJO

• WHILE

• EJEMPLO

• Crear un procedimiento para sumar todos los

números hasta un tope.

https://dev.mysql.com/doc/refman/8.0/en/while.html
3. ESTRUCTURAS DE CONTROL DE FLUJO

• REPEAT

• BUCLE

• statement_list se repite hasta que search_condition sea verdadero

https://dev.mysql.com/doc/refman/8.0/en/flow-control-statements.html
3. ESTRUCTURAS DE CONTROL DE FLUJO

• REPEAT

• EJEMPLO

https://dev.mysql.com/doc/refman/8.0/en/repeat.html
3. ESTRUCTURAS DE CONTROL DE FLUJO

• REPEAT

• EJEMPLO
3. ESTRUCTURAS DE CONTROL DE FLUJO

• LOOP

• EJEMPLO

https://dev.mysql.com/doc/refman/8.0/en/loop.html
3. ESTRUCTURAS DE CONTROL DE FLUJO

• LOOP

• EJEMPLO

https://dev.mysql.com/doc/refman/8.0/en/loop.html
4. MANEJO DE ERRORES

• DECLARE… HANDLER

• Las acciones posibles que podemos

seleccionar como handler_action son:
• CONTINUE: La ejecución del programa
continúa.
• EXIT: Termina la ejecución del programa.
• UNDO: No está soportado en MySQL.
4. MANEJO DE ERRORES

• DECLARE… HANDLER

• Ejemplo indicando el número de error de MySQL:

• En este ejemplo estamos declarando un handler que se ejecutará cuando se produzca el error 1051 de
MySQL, que ocurre cuando se intenta acceder a una tabla que no existe en la base de datos. En
este caso la acción del handler es CONTINUE lo que quiere decir que después de ejecutar las
instrucciones especificadas en el cuerpo del handler el procedimiento almacenado continuará su
ejecución.

https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
4. MANEJO DE ERRORES

• DECLARE… HANDLER

• Ejemplo para SQLSTATE:

• También podemos indicar el valor de la variable SQLSTATE. Por ejemplo, cuando se intenta
acceder a una tabla que no existe en la base de datos, el valor de la variable SQLSTATE es 42S02.
4. MANEJO DE ERRORES

• DECLARE… HANDLER

• Ejemplo para SQLWARNING:

• Es equivalente a indicar todos los valores de SQLSTATE que empiezan con 01.
4. MANEJO DE ERRORES

• DECLARE… HANDLER

• Ejemplo para NOT FOUND:

• Es equivalente a indicar todos los valores de SQLSTATE que empiezan con 02. Lo usaremos cuando
estemos trabajando con cursores para controlar qué ocurre cuando un cursor alcanza el final del data
set. Si no hay más filas disponibles en el cursor, entonces ocurre una condición de NO DATA con un
valor de SQLSTATE igual a 02000. Para detectar esta condición podemos usar un handler para
controlarlo.
4. MANEJO DE ERRORES

• DECLARE… HANDLER

• Ejemplo para SQLEXCEPTION:

• Es equivalente a indicar todos los valores de SQLSTATE que empiezan por 00, 01 y 02.
4. MANEJO DE ERRORES

• EJEMPLO DECLARE CONTINUE

HANDLER

• ¿Qué valor devolvería la sentencia SELECT

@x? Ejecutar 2 veces

• Error cuando hay duplicados de PK = 1062

4. MANEJO DE ERRORES

• EJEMPLO DECLARE CONTINUE

HANDLER

• ¿Qué valor devolvería la sentencia SELECT

@x? Ejecutar 2 veces

• Error cuando hay duplicados de PK = 1062

• Si se produce dicho error en alguna

inserción continúa con la siguiente
sentencia.
4. MANEJO DE ERRORES

• EJEMPLO DECLARE EXIT HANDLER

• ¿Qué valor devolvería la sentencia SELECT

@x?
4. MANEJO DE ERRORES

• Transacciones y Procedimientos

• Podemos utilizar el manejo de errores para

decidir si hacemos ROLLBACK de una
transacción.

• En el siguiente ejemplo vamos a capturar los

errores que se produzcan de tipo
SQLEXCEPTION y SQLWARNING
4. MANEJO DE ERRORES

• Transacciones y Procedimientos

• En lugar de tener un manejador para cada tipo

de error, podemos tener uno común para todos
los casos.
EJERCICIO 9

• Utiliza la BD Clientes-pagos.

• Crea un procedimiento que reciba por parámetro los datos de un cliente y los inserte en la tabla.
Controla que se intente insertar varios usuarios con el mismo DNI. Si eso pasase muestra el
mensaje: Se ha producido un error. 1062 : (23000)
EJERCICIO 9

• Utiliza la BD Clientes-pagos.

• Crea un procedimiento que reciba por parámetro los datos de un cliente y los inserte en la tabla.
Controla que se intente insertar varios usuarios con el mismo DNI. Si eso pasase muestra el
mensaje: Se ha producido un error. 1062 : (23000)
EJERCICIO 10

• Utiliza la BD Clientes-pagos.

• Realiza un procedimiento en el que se inserte en la misma BD diferentes pagos de los clientes,

dado un DNI del cliente, motivo de la deuda, importe. Se deberá controlar que el cliente esté
registrado en la BD.
EJERCICIO 10

• Utiliza la BD Clientes-pagos.

• Realiza un procedimiento en el que se inserte en la misma BD diferentes pagos de los clientes,

dado un DNI del cliente, motivo de la deuda, importe. Se deberá controlar que el cliente esté
registrado en la BD.
EJERCICIO 11

• Utiliza la BD Clientes-pagos.

• Crea un procedimiento que reciba por parámetro los datos de un cliente y los inserte en la tabla.
Controla que se intente insertar varios usuarios con el mismo DNI. Si eso pasase se debe
insertar en una tabla llamada log_errores (codError, mensaje, nombreUsuario, fechaHora).

• Hacerlo con SQLSTATE 23000

EJERCICIO 11

• Utiliza la BD Clientes-pagos.

• Crea un procedimiento que reciba por parámetro los datos de un cliente y los inserte en la tabla.
Controla que se intente insertar varios usuarios con el mismo DNI. Si eso pasase se debe
insertar en una tabla llamada log_errores (codError, mensaje, nombreUsuario, fechaHora).

• Hacerlo con SQLSTATE 23000

5. FUNCIONES

• Una función almacenada es un conjunto de instrucciones SQL que se almacena asociado a una base
de datos.

• Conjunto de instrucciones SQL que después de ejecutarse devuelven un valor.

• Es un objeto que se crea con la sentencia CREATE FUNCTION y se invoca con la sentencia
SELECT o dentro de una expresión.

• Una función puede tener cero o muchos parámetros de entrada y siempre devuelve un valor,
asociado al nombre de la función.
5. FUNCIONES

• Funciones vs Procedimientos

• Los procedimientos almacenados se llaman independientemente, mientras que las funciones son
llamadas dentro de otra sentencia SQL.

• Las funciones siempre deben devolver un valor, en cambio los procedimientos no.
5. FUNCIONES

• Las funciones devuelven siempre un dato a través de una sentencia RETURN.

• El dato se corresponde con un tipo de datos declarado para la función.

• Las funciones no pueden trabajar con parámetros OUT o INOUT únicamente con parámetros
de entrada IN, por eso no se especifica el tipo.
5. FUNCIONES

• Las funciones son llamadas a ejecución, al igual que las funciones internas de MySQL,
escribiendo su nombre y la lista de parámetros pasados a la función encerrados entre paréntesis.
• Por tanto, no usa una sentencia de llamada como la sentencia CALL en el caso de los
procedimientos.

• Las funciones podrán ser llamadas desde cualquier sentencia SQL como SELECT, UPDATE,
INSERT, DELETE.
• Los procedimientos nunca pueden ser llamados a ejecución dentro de otra sentencia.

• Las funciones se llaman usando: NombreFunción(parámetros)

5. FUNCIONES

• SINTAXIS

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
5. FUNCIONES

• SINTAXIS

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
5. FUNCIONES

• Parámetros de entrada

• En una función todos los parámetros son de entrada, por lo tanto, no será necesario utilizar la palabra
reservada IN delante del nombre de los parámetros.

• Ejemplo
• A continuación, se muestra la cabecera de la función contar_productos que tiene un parámetro de entrada
llamado gama.
5. FUNCIONES

• Resultados de salida

• Una función siempre devolverá un valor de salida asociado al nombre de la función.

• En la definición de la cabecera de la función hay que definir el tipo de dato que devuelve con la palabra
reservada RETURNS y en el cuerpo de la función debemos incluir la palabra reservada RETURN para
devolver el valor de la función.

• Ejemplo
• En este ejemplo se muestra una definición incompleta de una función donde se puede ver el uso de las palabras
reservadas RETURNS y RETURN.
• OJO: si se retorna un varchar o char habrá que indicar su tamaño.
5. FUNCIONES

• Resultados de salida

• Ejemplo
• En este ejemplo se muestra una definición incompleta de una función donde se puede ver el uso de las
palabras reservadas RETURNS y RETURN.
• OJO: si se retorna un varchar o char habrá que indicar su tamaño.
5. FUNCIONES
• Características

• Después de la definición del tipo de dato que devolverá la función con la palabra reservada RETURNS, tenemos que
indicar las características de la función.

• Las opciones disponibles son las siguientes:

• DETERMINISTIC – Indica que la función siempre devuelve el mismo resultado cuando se utilizan los mismos parámetros
de entrada.
• NOT DETERMINISTIC – Indica que la función no siempre devuelve el mismo resultado, aunque se utilicen los mismos
parámetros de entrada. Esta es la opción que se selecciona por defecto cuando no se indica una característica de forma
explícita.
• CONTAINS SQL – Indica que la función contiene sentencias SQL, pero no contiene sentencias de manipulación de datos
(NO DML). Algunos ejemplos de sentencias SQL que pueden aparecer en este caso son operaciones con variables (Ej: SET
@x = 1) o uso de funciones de MySQL (Ej: SELECT NOW();) entre otras. Pero en ningún caso aparecerán sentencias de
escritura o lectura de datos.
5. FUNCIONES

• Características

• Después de la definición del tipo de dato que devolverá la función con la palabra reservada
RETURNS, tenemos que indicar las características de la función.

• Las opciones disponibles son las siguientes:

• NO SQL – Indica que la función no contiene sentencias SQL.
• READS SQL DATA – Indica que la función no modifica los datos de la base de datos y que contiene
sentencias de lectura de datos, como la sentencia SELECT.
• MODIFIES SQL DATA – Indica que la función sí modifica los datos de la base de datos y que contiene
sentencias como INSERT, UPDATE o DELETE.
5. FUNCIONES

• Características

• Para poder crear una función en MySQL es necesario indicar al menos una de estas tres
características:
• DETERMINISTIC
• NO SQL
• READS SQL DATA

• Si no se indica al menos una de estas características obtendremos el siguiente mensaje de error.

5. FUNCIONES
• Características

• Es posible configurar el valor de la variable global log_bin_trust_function_creators a 1, para indicar a MySQL que queremos
eliminar la restricción de indicar alguna de las características anteriores cuando definimos una función almacenada.

• Para consultar el estado de esa variable:

• select @@log_bin_trust_function_creators;

• Esta variable está configurada con el valor 0 por defecto y para poder modificarla es necesario contar con el privilegio SUPER.
• SET GLOBAL log_bin_trust_function_creators = 1;

• También, es posible modificarla en el archivo de configuración de MySQL (pero no vamos a tocar los archivos de configuración).
5. FUNCIONES

• Declaración de variables

• Al igual que en los procedimientos, en las funciones es posible declarar variables locales con la
palabra reservada DECLARE.

• La sintaxis para declarar variables locales con DECLARE es la siguiente:

• RECUERDA: El ámbito de una variable local será el bloque BEGIN y END del procedimiento o
la función donde ha sido declarada.
5. FUNCIONES

• Declaración de variables

• EJEMPLO

• En este ejemplo estamos declarando una variable local con el nombre total que es de tipo INT
UNSIGNED.

https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html
5. FUNCIONES

• Declaración de variables

• EJEMPLO

• Escribe una función que escriba en pantalla el texto Hola Mundo

5. FUNCIONES

• Declaración de variables

• EJEMPLO

• Escribe una función que recibe una cadena de caracteres con el nombre de una persona y devuelve un
mensaje de saludo a esa persona.
5. FUNCIONES

• Declaración de variables

• EJEMPLO

• Escribe una función que recibe una cadena de caracteres con el nombre de una persona y devuelve un
mensaje de saludo a esa persona.
5. FUNCIONES

• Declaración de variables

• EJEMPLO

• Con la función anterior, saluda a todos los clientes.

5. FUNCIONES

• Declaración de variables

• EJEMPLO

• Con la función anterior, saluda a todos los clientes.

EJERCICIO 12

• Crea una función que cuente el número de clientes que tiene una letra (pasada como
parámetro) en su nombre y retorne dicha cuenta.

• Usar la BD filmStore

• Se llamará numero_letras
EJERCICIO 12

• Crea una función que cuente el número de clientes que tiene una letra (pasada como
parámetro) en su nombre y retorne dicha cuenta.

• Usar la BD filmStore

• Se llamará numero_letras

LLAMADA
EJERCICIO 13

• Ahora vamos a utilizar la función numero_letras y vamos a contar el número de clientes que
contienen la primera letra de cada uno.

• Es decir:

• Quiero saber cuantos clientes contienen la M, R, K, P y C

EJERCICIO 13

• Ahora vamos a utilizar la función numero_letras y vamos a contar el número de clientes que
contienen la primera letra de cada uno.

• Es decir:

• Quiero saber cuantos clientes contienen la M, R, K, P y C

EJERCICIO 14

• ¿Qué hace la siguiente sentencia?

5. FUNCIONES

• Funciones propias del SGBD

• En la documentación oficial tenemos un listado con todas las funciones que contiene el SGBD

• También podemos encontrar las funciones clasificadas según su uso: – Cadenas de texto – Fecha y
tiempo – Numéricas-...

https://dev.mysql.com/doc/refman/8.0/en/functions.html
5. FUNCIONES

• EJEMPLO

• Escriba una función llamada contar_productos que reciba como entrada el nombre de la gama y
devuelva el número de productos que existen dentro de esa gama.

• BD Jardinería
5. FUNCIONES

• EJEMPLO

• Escriba una función llamada contar_productos

que reciba como entrada el nombre de la gama
y devuelva el número de productos que existen
dentro de esa gama.

• BD Jardinería
EJERCICIOS – FUNCIONES SIN SENTENCIAS SQL
1. Escribe una función que reciba un número entero de entrada y devuelva TRUE si el número es par o FALSE en caso contrario.
2. Escribe una función que devuelva el valor de la hipotenusa de un triángulo a partir de los valores de sus lados.
3. Escribe una función que reciba como parámetro de entrada un valor numérico que represente un día de la semana y que devuelva una
cadena de caracteres con el nombre del día de la semana correspondiente. Por ejemplo, para el valor de entrada 1 debería devolver la
cadena lunes.
4. Escribe una función que reciba tres números reales como parámetros de entrada y devuelva el mayor de los tres.
5. Escribe una función que devuelva el valor del área de un círculo a partir del valor del radio que se recibirá como parámetro de
entrada.
6. Escribe una función que devuelva como salida el número de años que han transcurrido entre dos fechas que se reciben como
parámetros de entrada. Por ejemplo, si pasamos como parámetros de entrada las fechas 2018-01-01 y 2008-01-01 la función tiene que
devolver que han pasado 10 años. (utiliza timestampdiff)
7. Escribe una función que reciba una cadena de entrada y devuelva la misma cadena pero sin acentos. La función tendrá que
reemplazar todas las vocales que tengan acento por la misma vocal pero sin acento. Por ejemplo, si la función recibe como parámetro
de entrada la cadena María la función debe devolver la cadena Maria
EJERCICIOS – FUNCIONES CON SENTENCIAS SQL

1. Escribe una función para la base de datos jardinería que devuelva el número total de productos
que hay en la tabla productos.
2. Escribe una función para la base de datos jardinería que devuelva el valor medio del precio de los
productos de un determinado proveedor que se recibirá como parámetro de entrada. El parámetro
de entrada será el nombre del fabricante.
3. Escribe una función para la base de datos jardinería que devuelva el valor máximo del precio de
los productos de un determinado fabricante que se recibirá como parámetro de entrada. El
parámetro de entrada será el nombre del fabricante.
4. Escribe una función para la base de datos tienda que devuelva el valor mínimo del precio de los
productos de un determinado fabricante que se recibirá como parámetro de entrada. El parámetro
de entrada será el nombre del fabricante.
EJERCICIOS – MANEJO DE ERRORES EN MYSQL
1. Crea una base de datos llamada test que contenga una tabla llamada alumno. La tabla debe tener cuatro
columnas:
• id: entero sin signo (clave primaria).
• nombre: cadena de 50 caracteres.
• apellido1: cadena de 50 caracteres.
• apellido2: cadena de 50 caracteres.

Una vez creada la base de datos y la tabla deberá crear un procedimiento llamado insertar_alumno con las
siguientes características. El procedimiento recibe cuatro parámetros de entrada (id, nombre, apellido1, apellido2) y
los insertará en la tabla alumno. El procedimiento devolverá como salida un parámetro llamado error que tendrá
un valor igual a 0 si la operación se ha podido realizar con éxito y un valor igual a 1 en caso contrario.
• Deberá manejar los errores que puedan ocurrir cuando se intenta insertar una fila que contiene una clave primaria
repetida. (1062)
EJERCICIOS – TRANSACCIONES CON
PROCEDIMIENTOS ALMACENADOS
1. Crea una base de datos llamada cine que contenga dos tablas con las siguientes columnas.
• Tabla cuentas:
• id_cuenta: entero sin signo (clave primaria).
• saldo: real sin signo. (restricción saldo>=0)
• Tabla entradas:
• id_butaca: entero sin signo (clave primaria).
• nif: cadena de 9 caracteres.

Una vez creada la base de datos y las tablas deberá crear un procedimiento llamado comprar_entrada con las siguientes
características. El procedimiento recibe 3 parámetros de entrada (nif, id_cuenta, id_butaca) y devolverá como salida un
parámetro llamado error que tendrá un valor igual a 0 si la compra de la entrada se ha podido realizar con éxito y un valor
igual a 1 en caso contrario. Utilizar una transacción
• Error 1264: insertar un valor en una columna que excede el rango del tipo de datos
• Error 1062:PK duplicada
6. TRIGGERS

• Un trigger es un objeto de la base de datos que está asociado con una tabla y que se activa cuando
ocurre un evento sobre la tabla.

• Los eventos que pueden ocurrir sobre la tabla son:

• INSERT: El trigger se activa cuando se inserta una nueva fila sobre la tabla asociada.
• UPDATE: El trigger se activa cuando se actualiza una fila sobre la tabla asociada.
• DELETE: El trigger se activa cuando se elimina una fila sobre la tabla asociada.
6. TRIGGERS

• Es un código que se ejecuta en el servidor de la base de datos, y no en la máquina cliente.

• Un disparador nunca se llama directamente, en cambio, cuando una aplicación o usuario

intenta insertar, actualizar, o elimina una fila en una tabla, la acción definida en el disparador se
ejecuta automáticamente (se dispara).

• Un trigger se puede invocar antes o después de que una fila se inserta, actualiza o elimina.
6. TRIGGERS
• Usos y Ventajas

• Implementar restricciones definidas en el diseño de la base de datos para que los usuarios introduzcan sólo valores válidos. (FK
antigua)

• Automatizar acciones suministrando avisos y sugerencias cuando haya que reparar alguna acción.

• Actualizar los valores de una tabla, insertar registros en una tabla o llamar a otros procedimientos almacenados.

• El mantenimiento de la aplicación se reduce, los cambios a un disparador se refleja automáticamente en todas las aplicaciones
que tienen que ver con la tabla sin la necesidad de recompilar o enlazar.

• Logs automáticos de cambios a las tablas.

• Una aplicación puede guardar un registro corriente de cambios, creando un disparador que se active siempre que una tabla se modifique.
6. TRIGGERS

• Utilidad

• Mantener la integridad de las tablas de la BBDD, como:

• VALIDACIÓN DE DATOS
• Los triggers pueden controlar ciertas acciones, por ejemplo: pueden rechazar o modificar ciertos valores que no cumplan
determinadas reglas, para prevenir que datos inválidos sean insertados en la base.

• CALCULAR ATRIBUTOS DERIVADOS

• AUDITORÍA
• Los triggers se usan para llenar tablas de auditoría, en donde se registren ciertos tipos de transacciones o accesos hacia tablas.
6. TRIGGERS

• Desventajas

• Pueden aumentar la sobrecarga del servidor de base de datos.

• Se ejecutan y son invisibles desde las aplicaciones cliente, por lo tanto, es difícil averiguar qué sucede
en la capa de base de datos.

• Hay que definir con anticipación la tarea que realizará el trigger.

6. TRIGGERS

• DEFINER=user: Indica al gestor de bases de

datos qué usuario tiene privilegios en su
cuenta para la invocación de los triggers
cuando surjan los eventos DML.

• Por defecto esta característica tiene el valor

CURRENT_USER que hace referencia al
usuario actual que esta creando el Trigger.
6. TRIGGERS

• trigger_name: Indica el nombre del trigger.

• Por defecto existe una nomenclatura práctica

para nombrar un trigger, la cual da mejor
legibilidad en la administración de la base de
datos.

• nombre de tabla+momento de ejecución

(AFTER o BEFORE) seguido de la inicial de
la operación DML +Trigger – Ejemplo:
clientes_BI_Trigger
6. TRIGGERS

• trigger_time: { BEFORE | AFTER }

• Especifica si el Trigger se ejecuta antes o

después del evento DML.

• trigger_event: { INSERT | UPDATE |

DELETE }

• Especifica que sentencia DML ejecuta el

Trigger
6. TRIGGERS

• ON tbl_name

• Especifica la tabla que motiva el disparo del

trigger cuando se produce algún evento sobre
la tabla.

• FOR EACH ROW

• Indica que el trigger se lanza por cada fila

afectada por el evento.
6. TRIGGERS
• trigger_order: { FOLLOWS | PRECEDES }
other_trigger_name
• Puede haber múltiples triggers con el mismo evento y
tiempo de ejecución para una misma tabla
• Por ejemplo puede haber dos BEFORE UPDATE triggers para
una misma tabla.
• Por defecto, triggers con el mismo evento y tiempo de
ejecución se activan en el orden que fueron creadas.
• Para modificar ese orden hay que especificar en la clausula
trigger_order FOLLOWS or PRECEDES y el nombre de
un trigger que ya exista y además tenga el mismo evento y
tiempo de ejecución.
• Con FOLLOWS el nuevo trigger se activa después del
trigger indicado
• Con PRECEDES el nuevo trigger se activa antes del trigger
indicado
6. TRIGGERS

• trigger_body

• Indica la sentencia a ejecutar cuando se activa

el trigger.

• Para ejecutar varias sentencias se agrupan

dentro de un bloque BEGIN ... END.

• Además, permite que se usen las mismas

sentencias que en los procedimientos y en las
funciones.
6. TRIGGERS

• OLD – NEW

• Si tenemos un Trigger después de haber actualizado un registro. Como compruebo los valores
asociados a cada una de las columnas antes de la sentencia?

• EJEMPLO:
• Cambiar el precio de una película. El precio no puede ser menor al que tenía antes.
6. TRIGGERS

• OLD – NEW

• Para hacer referencia en un trigger a las columnas de la misma tabla que dispara el trigger, no
podemos usar simplemente el nombre de esa columna.

• Debemos usar los identificadores OLD y NEW.

• OLD.col_name se refiere a una columna de una fila existente antes de ser actualizada o eliminada.

• NEW.col_name se refiere a una columna de una nueva fila que va a ser insertada o de una fila existente después de ser
actualizada.
6. TRIGGERS

• OLD – NEW

• Es decir:
• OLD indica el valor antiguo de la columna
• NEW el valor nuevo que pudiese tomar

• Si usa la sentencia UPDATE se refiere a un valor OLD y NEW, ya que modifica registros existentes por los
valores.

• En cambio, si usa INSERT sólo usa NEW, ya que su naturaleza es únicamente de insertar nuevos valores a
las columnas.

• Y con DELETE usa OLD debido a que borra valores existentes.

6. TRIGGERS

• BEFORE – AFTER

• Estas cláusulas indican si el Trigger se ejecuta BEFORE (antes) o AFTER (después) del evento DML.

• Hay ciertos eventos que no son compatibles con estas sentencias.

• Si tiene un Trigger AFTER que se ejecuta en una sentencia UPDATE, es ilógico editar valores nuevos NEW,
sabiendo que el evento ya ocurrió.

• Si el Trigger se ejecuta AFTER una sentencia INSERT, no se podrá modificar ni el valor NEW ni el OLD,
ya que la inserción sucedió.
6. TRIGGERS

• BEFORE – AFTER

• EJEMPLO
6. TRIGGERS

• Eliminar un Trigger

• Especificaciones de un Trigger creado

• Mostrar los Trigger que hay en la BD

6. TRIGGERS

• EJEMPLO

• Se define un trigger antes de realizar una actualización, que comprueba que el valor esté entre 0 y
100.
6. TRIGGERS

• Almacenamiento

• Los triggers o disparadores se almacenan en la tabla TRIGGERS de la base de datos

information_schema, para verlos ejecute:

• select * from information_schema.triggers;

EJERCICIO 15

• En la BD clientes y pagos_pendientes, estamos detectando nuevos clientes. Queremos saber

cuándo y quién los registró.

• Vamos a crear una tabla para registrar el historial de acciones sobre una BD. Para ello crea una
tabla log_clientes con la siguiente estructura:
• id int
• accion varchar (200)
• fecha DateTime (por defecto tendrá el valor current_timestamp)
• usuario varchar(50)
EJERCICIO 15

• En la BD clientes y pagos_pendientes, estamos detectando nuevos clientes. Queremos saber

cuándo y quién los registró.

• Vamos a crear una tabla para registrar el historial de acciones sobre una BD. Para ello crea una
tabla log_clientes con la siguiente estructura:
• id int
• accion varchar (200)
• fecha DateTime (por defecto tendrá el valor current_timestamp)
• usuario varchar(50)
EJERCICIO 16
• En la BD clientes y pagos_pendientes, estamos detectando nuevos clientes. Queremos saber cuándo y quién los
registró.

• Vamos a crear una tabla para registrar el historial de acciones sobre una BD. Para ello crea una tabla log_clientes
con la siguiente estructura:
• id int
• accion varchar (200)
• fecha DateTime (por defecto tendrá el valor current_timestamp)
• usuario varchar(50)

• MODIFICA EL EJERCICIO PARA GUARDAR EN LA ACCIÓN EL NOMBRE DE USUARIO QUE SE

INSERTÓ
EJERCICIO 16
• En la BD clientes y pagos_pendientes, estamos detectando nuevos clientes. Queremos saber cuándo y quién los
registró.

• Vamos a crear una tabla para registrar el historial de acciones sobre una BD. Para ello crea una tabla log_clientes
con la siguiente estructura:
• id int
• accion varchar (200)
• fecha DateTime (por defecto tendrá el valor current_timestamp)
• usuario varchar(50)

• MODIFICA EL EJERCICIO PARA GUARDAR EN LA ACCIÓN EL NOMBRE DE USUARIO QUE SE

INSERTÓ
EJERCICIO 17

• Carga la bd - clientes pagos

• Guarda un historial de todos los clientes de la bd, para que antes de borrarlos se almacene su
información en ella.
EJERCICIO 17

• Carga la bd - clientes pagos

• Guarda un historial de todos los clientes de la bd, para que antes de borrarlos se almacene su
información en ella.
EJERCICIO 18

• Una librería almacena los datos de sus libros en una tabla denominada "libros" y controla las
acciones que los empleados realizan sobre dicha tabla almacenando en la tabla "control" el
nombre del usuario y la fecha, cada vez que se modifica el "precio" de un libro.

• Carga la bd - librería

• Controla quién ha modificado el precio

EJERCICIO 18

• Una librería almacena los datos de sus libros en una tabla denominada "libros" y controla las
acciones que los empleados realizan sobre dicha tabla almacenando en la tabla "control" el
nombre del usuario y la fecha, cada vez que se modifica el "precio" de un libro.

• Carga la bd - librería

• Controla quién ha modificado el precio

EJERCICIO 19

• Una librería almacena los datos de sus libros en una tabla denominada "libros" y controla las
acciones que los empleados realizan sobre dicha tabla almacenando en la tabla "control" el
nombre del usuario y la fecha, cada vez que se modifica el "precio" de un libro.

• Carga la bd - librería

• Controla quién ha modificado el precio (guardalo en la tabla control) y evita que el precio sea
menor a 10.
EJERCICIO 19

• Una librería almacena los datos de sus libros en una tabla denominada "libros" y controla las
acciones que los empleados realizan sobre dicha tabla almacenando en la tabla "control" el
nombre del usuario y la fecha, cada vez que se modifica el "precio" de un libro.

• Carga la bd - librería

• Controla quién ha modificado el precio (guardalo en la tabla control) y evita que el precio sea
menor a 10.
EJERCICIO 20

• Carga la bd - clientes pagos sinFK

• Se deberá controlar que los pagos que se ingresen se realicen para un cliente registrado.

• Para lanzar el error puedes utilizar SQLSTATE 45000

• SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'mensaje a mostrar'
• Cancela la operación que se estaba realizando y muestra un mensaje.
• Lleva el valor 45000 ya que este valor significa 'excepción definida por el usuario' (“unhandled user-
defined exception”)
EJERCICIO 20

• Carga la bd - clientes pagos sinFK

• Se deberá controlar que los pagos que se ingresen se realicen para un cliente registrado.

• Para lanzar el error puedes utilizar SQLSTATE 45000

• SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'mensaje a mostrar'
• Cancela la operación que se estaba realizando y muestra un mensaje.
• Lleva el valor 45000 ya que este valor significa 'excepción definida por el usuario' (“unhandled user-
defined exception”)
6. TRIGGERS

• EJEMPLO

• Crea una base de datos llamada test que contenga una tabla llamada alumnos con las siguientes
columnas.

• Tabla alumnos:
• id (entero sin signo)
• nombre (cadena de caracteres)
• apellido1 (cadena de caracteres)
• apellido2 (cadena de caracteres)
• nota (número real)
6. TRIGGERS
• EJEMPLO

• Una vez creada la tabla escriba dos triggers con las siguientes características:

• Trigger 1: trigger_check_nota_before_insert
• Se ejecuta sobre la tabla alumnos.
• Se ejecuta antes de una operación de inserción.
• Si el nuevo valor de la nota que se quiere insertar es negativo, se guarda como 0.
• Si el nuevo valor de la nota que se quiere insertar es mayor que 10, se guarda como 10.

• Trigger2 : trigger_check_nota_before_update
• Se ejecuta sobre la tabla alumnos.
• Se ejecuta antes de una operación de actualización.
• Si el nuevo valor de la nota que se quiere actualizar es negativo, se guarda como 0.
• Si el nuevo valor de la nota que se quiere actualizar es mayor que 10, se guarda como 10.
6. TRIGGERS

• EJEMPLO

• Una vez creados los triggers escribe varias sentencias de inserción y actualización sobre la tabla
alumnos y verifica que los triggers se están ejecutando correctamente.
6. TRIGGERS

• EJEMPLO

• Una vez creados los triggers escribe varias sentencias de inserción y actualización sobre la tabla
alumnos y verifica que los triggers se están ejecutando correctamente.
EJERCICIO 21

• Carga la bd CIRCO.

• Haz que no se pueda añadir un nuevo animal si el tipo es 'León' y el número de años es mayor
que 20
EJERCICIO 21

• Carga la bd CIRCO.

• Haz que no se pueda añadir un nuevo animal si el tipo es 'León' y el número de años es mayor
que 20
EJERCICIO 22

• Carga la bd CIRCO.

• Haz todo lo necesario para que el campo ganancias de la tabla ATRACCIONES se actualice
cuando se añadan, borren o modifiquen datos en la tabla ATRACCION_DIA.
EJERCICIO 22

• Carga la bd CIRCO.

• Haz todo lo necesario para que el campo ganancias de la tabla ATRACCIONES se actualice
cuando se añadan, borren o modifiquen datos en la tabla ATRACCION_DIA.
EJERCICIO 22

• Carga la bd CIRCO.

• Haz todo lo necesario para que el campo ganancias de la tabla ATRACCIONES se actualice
cuando se añadan, borren o modifiquen datos en la tabla ATRACCION_DIA.
EJERCICIO 22

• Carga la bd CIRCO.

• Haz todo lo necesario para que el campo ganancias de la tabla ATRACCIONES se actualice
cuando se añadan, borren o modifiquen datos en la tabla ATRACCION_DIA.
EJERCICIOS - 1

• Crea una base de datos llamada test que contenga una tabla llamada alumnos con las siguientes
columnas.
• Tabla alumnos:
• id (entero sin signo)
• nombre (cadena de caracteres)
• apellido1 (cadena de caracteres)
• apellido2 (cadena de caracteres)
• email (cadena de caracteres)

• Escriba un procedimiento llamado crear_email que dados los parámetros de entrada: nombre,
apellido1, apellido2 y dominio, cree una dirección de email y la devuelva como salida.
EJERCICIOS - 1
• Procedimiento: crear_email
• Entrada:
• nombre (cadena de caracteres)
• apellido1 (cadena de caracteres)
• apellido2 (cadena de caracteres)
• dominio (cadena de caracteres)
• Salida:
• email (cadena de caracteres)
• Devuelva una dirección de correo electrónico con el siguiente formato:
• El primer carácter del parámetro nombre.
• Los tres primeros caracteres del parámetro apellido1.
• Los tres primeros caracteres del parámetro apellido2.
• El carácter @.
• El dominio pasado como parámetro. Recuerda que el email debe estar en minúscula
EJERCICIOS - 1

• Una vez creada la tabla escriba un trigger con las siguientes características:
• Trigger: trigger_crear_email_before_insert
• Se ejecuta sobre la tabla alumnos.
• Se ejecuta antes de una operación de inserción.
• Si el nuevo valor del email que se quiere insertar es NULL, entonces se le creará automáticamente una
dirección de email y se insertará en la tabla.
• Si el nuevo valor del email no es NULL se guardará en la tabla el valor del email.

• Nota: Para crear la nueva dirección de email se deberá hacer uso del procedimiento
crear_email.
EJERCICIOS - 2
• Modifica el ejercicio anterior y añade un nuevo trigger que las siguientes características:
• Trigger: trigger_guardar_email_after_update:
• Se ejecuta sobre la tabla alumnos.
• Se ejecuta después de una operación de actualización.
• Cada vez que un alumno modifique su dirección de email se deberá insertar un nuevo registro en una tabla llamada
log_cambios_email.
• La tabla log_cambios_email contiene los siguientes campos:
• id: clave primaria (entero autonumérico)
• id_alumno: id del alumno (entero)
• fecha_hora: marca de tiempo con el instante del cambio (fecha y hora)
• old_email: valor anterior del email (cadena de caracteres)
• new_email: nuevo valor con el que se ha actualizado

• RESUMEN: Si cambia el email hay que guardarlo en la tabla, no puede ser el mismo del anterior.
EJERCICIOS - 3
• Modifica el ejercicio anterior y añade un nuevo trigger que tenga las siguientes características:
• Trigger: trigger_guardar_alumnos_eliminados:
• Se ejecuta sobre la tabla alumnos.
• Se ejecuta después de una operación de borrado.
• Cada vez que se elimine un alumno de la tabla alumnos se deberá insertar un nuevo registro en una tabla llamada
log_alumnos_eliminados.
• La tabla log_alumnos_eliminados contiene los siguientes campos:
• id: clave primaria (entero autonumérico)
• id_alumno: id del alumno (entero)
• fecha_hora: marca de tiempo con el instante del cambio (fecha y hora)
• nombre: nombre del alumno eliminado (cadena de caracteres)
• apellido1: primer apellido del alumno eliminado (cadena de caracteres)
• apellido2: segundo apellido del alumno eliminado (cadena de caracteres)
• email: email del alumno eliminado (cadena de caracteres)