UD8 Programacion BD

2023
Bases de datos
Fecha última revisión:

05/04/2023
JCCA
[Unidad 8
Programación de
B.D con MySQL]
Desarrolla procedimientos almacenados, evaluando y utilizando las

sentencias del lenguaje incorporado en el sistema gestor de bases de
datos.
Unidad 8: Programación de bases de datos Bases de datos
Sumario
1.- Introducción.................................................................................................................................. 4
1.1.- Procedimientos, funciones y triggers en MySQL....................................................................4
1.2.- Ventajas de su utilización.......................................................................................................5
2.- Variables y tipos de datos.............................................................................................................. 6
2.1 Variables de sesión...................................................................................................................7
3.- Cursores......................................................................................................................................... 8
4.- Procedimientos............................................................................................................................10
4.1.- DELIMETER...........................................................................................................................12
4.2.- Parámetros de entrada, salida y entrada/salida...................................................................13
5.- Funciones.....................................................................................................................................15
5.1.- Características de la función................................................................................................ 17
6.- Estructuras de control..................................................................................................................18
6.1.- Condicional IF.......................................................................................................................18
6.2.- Condicional CASE................................................................................................................. 19
6.3.- Instrucciones repetitivas: bucles..........................................................................................20
6.3.1.- LOOP.............................................................................................................................20
6.3.2.- REPEAT … UNTIL........................................................................................................... 20
6.3.3.- WHILE........................................................................................................................... 22
7.- Manejo de errores....................................................................................................................... 22
8.- Realizar transacciones con procedimientos almacenados...........................................................24
9.- Triggers........................................................................................................................................ 25
10.- Bibliografía.................................................................................................................................28
2
OBJETIVOS
En esta unidad se pretende alcanzar los siguientes objetivos:
• Identificar las diversas formas de automatizar tareas.
• Identificar las herramientas disponibles para editar guiones.
• Definir y utilizar guiones para automatizar tareas.
• Utilizar estructuras de control de flujo.
• Aplicar las funciones proporcionadas por el sistema gestor.
• Definir funciones de usuario.
• Definir disparadores.
• Utilizar cursores.
• Desarrollar progresivamente actitudes profesionales como la importancia del trabajo en
equipo, la sistematicidad, el orden y la limpieza, la búsqueda de soluciones creativas y el
uso de un lenguaje técnico cada vez más preciso.
3
1.- Introducción
En las unidades anteriores se ha abordado el lenguaje SQL y la ejecución de sus instrucciones de

forma aislada. No obstante, lo habitual en cualquier software que trabaje con una base de datos es
ejecutar secuencias de instrucciones que realicen varias comprobaciones, tareas secuenciales o
repetitivas, etc. El código fuente del lenguaje de programación de dicho software debe enviar
constantemente sentencias SQL al servidor de base de datos
Los sistemas gestores de bases de datos implementan mecanismos para potenciar la

automatización de los procesos de gestión de los datos. De este modo, es el propio SGBD el
encargado de mantener, en la medida de lo posible, los datos que soporta, sus actualizaciones y
cambios, así como sus controles de integridad y de coherencia.
La ejecución de sentencias SQL de forma independiente implica una comunicación continua entre
la aplicación y la base de datos, con el consiguiente tráfico de red. Por ese motivo, ISO definió el
estándar SQL/PSM (SQL/persistent stored modules, SQL/módulos almacenados persistentes),
integrado en el estándar ANSI/SQL desde 1999.
Un programa almacenado o rutina es un conjunto de instrucciones almacenadas dentro del

servidor de bases de datos y que se ejecutan en él. Este conjunto se identifica por un nombre. Los
programas almacenados en MySQL cumplen en gran medida el estándar ANSI de especificación de
ANSI SQL:2003 SQL/PSM
1.1.- Procedimientos, funciones y triggers en MySQL

Las rutinas (procedimientos, funciones y triggers) almacenadas son un conjunto de comando SQL
que pueden guardarse en el servidor. Una vez que se hace los clientes no necesitar lanzar cada
comando individualmente, sino que pueden en su lugar llamar al procedimiento almacenado como
un único comando.
En esta unidad vamos a estudiar los procedimientos, funciones y triggers de MySQL, que son
objetos que contienen código SQL y se almacenan asociados a una base de datos.
• Procedimientos almacenados: El más común de las rutinas almacenados. Resuelven un
determinado problema cuando son llamadas y pueden aceptar varios parámetros de
entrada y devolver varios de salida.
• Funciones almacenadas: Similares a los procedimientos salvo que sólo devuelven un valor
como parámetro de salida. La ventaja que presentan las funciones es que pueden ser
utilizadas dentro de instrucciones SQL y por tanto aumentan considerablemente las
capacidades de este lenguaje.
• Triggers o desencadenadores o disparadores: Son rutinas que se activan (“disparan”) ante
un determinado suceso ocurrido dentro de la base de datos.
4
El esquema general de una rutina almacenada se resume en el siguiente esquema:
Nombre rutina + parámetros (entrada, salida o entrada/salida)
Declaración e inicialización de variables
Procesamiento de datos
Bloques BEGIN/END con instrucciones de control (codicionales
y repetitivas)
Fin
Con la instrucción RETURN para devolver un valor en el caso
de funciones almacenadas
1.2.- Ventajas de su utilización

Ventajas de la utilización rutinas almacenados en comparación con los realizados en un
determinado lenguaje y que no residen en el servidor:
• Mayor seguridad y robustez en la base de datos. Es posible limitar los permisos de acceso
de usuario a las rutinas almacenadas y no a las tablas directamente. De este modo
evitamos problemas derivados de una aplicación mal programada que haga un mal uso de
las tablas.
• Mejor mantenimiento de las aplicaciones que acceden a las rutinas almacenadas y por
tanto disminución de la posibilidad de aparición de errores. En lugar de que cada aplicación
cliente disponga de sus propios programas para realizar operaciones de inserción de
consulta o actualización, las rutinas almacenadas permiten centralizar los métodos de
acceso y actualización anteriores presentando una interfaz común para todos los
programas.
• Mayor portabilidad de las aplicaciones (relacionada con el punto anterior) puesto que la
lógica de la aplicación ya queda implementada en las rutinas almacenados permitiendo al
programador centrarse sólo en su interfaz.
• Debido a la fuerte integración con el lenguaje SQL, no se necesita de ningún tipo de
conector o driver como ODBC (Open DataBase Connectivity) o JDBC (Java DataBase
Connectivity) para poder construir y ejecutar sentencias SQL. Bastará agrupar estas últimas
bajo una rutina almacenada que se llamará en el momento en el que se necesite.
• Reducción del tráfico de red. El cliente llama a un procedimiento del servidor enviándole
unos datos. Éste los recibe y tras procesarlos devuelve unos resultados. Por la red no viajan
nada más que los datos.
5
2.- Variables y tipos de datos

Las variables son elementos de datos con un nombre cuyo valor puede ir cambiando a lo largo de
la ejecución de la rutina.
La declaración de variables se realiza antes del comienzo de las instrucciones (y antes de los
cursores y manejadores de errores que se veremos mas adelante en esta unidad). Se utiliza la
sintaxis siguiente:
DECLARE nombre_variable1 [,nombre_variable2...] tipo_dato [DEFAULT valor];
Se pueden declarar varias variables del mismo tipo seguidas y separadas por comas. Al mismo
tiempo que se declaran las variables se pueden definir asignándoles un valor inicial mediante la
cláusula DEFAULT; si no se les asigna ninguno entonces las variables quedan definidas al valor
NULL.
En el momento de la declaración hay que indicar el tipo de datos1 de la variable; pueden utilizarse
cualquiera de los que hemos empleado en la unidad 5 para la creación de las tablas.
Todas las variables que se pueden utilizar deben ser escalares, es decir, un solo valor.
Para asignar valores a variables se utiliza la siguiente sintaxis:
SET nombre_variable1 = expresión1 [,nombre_variable2 = expresión2 ...]
En este caso y a diferencia de otros lenguajes es necesario especificar la sentencia SET para asignar
valores a las variables. Se puede en una sola instrucción realizar varias asignaciones.
Veamos algún ejemplo:
DECLARE variable1 INT;

DECLARE variable2, variable3 INT;
DECLARE variable4 INT DEFAULT 300;
DECLARE variable5 VARCHAR(20) DEFAULT "Avilés";
SET variable1 = 100;

SET variable2 = variable1 + variable4;
SET variable3 = 500, variable1 = variable 1 + 50;
Mediante las variables se pueden recuperar datos de una consulta SQL. Si la consulta devuelve un
único valor, podemos utilizar SET
Ejemplo:
DECLARE maximo INT;

SET maximo = (SELECT MAX(nSalario) FROM Templeado);
1 Unidad 5. Apartado 4
6
2.1 Variables de sesión.

Un tipo especial de variables son las variables de sesión o usuario ya que pueden ser manipuladas
dentro y fuera de las rutinas almacenadas.
Tienen las siguientes características:
✔ Este tipo de variables no necesitan declaración y podemos emplearlas directamente.
✔ Van precedidas del carácter @, es decir, empiezan por @.
✔ Para asignarles un valor empleamos la orden SET o INTO.
✔ Son de un tipo de datos variant y pueden almacenar texto, fechas y números.
✔ Su alcance es de una sesión y por tanto son accesibles desde cualquier rutina que se
ejecuta durante esa sesión, lo que las asemeja al concepto de variables globales. Tendrán
un valor diferente en sesiones diferentes, entendiéndose una sesión como una conexión a
Mysql.
✔ Podemos emplear variables de sesión en cualquier orden SQL (SELECT, INSERT, etc ...)
Ejemplo:
SET @nombre = “Juan”;

SELECT @nombre
Podemos emplear variables para guardar información de una orden SQL, pero debemos
asegurarnos que solamente devuelve una fila. En este caso empleamos la cláusula INTO dentro del
SELECT.
Ejemplo:
USE bd_ud6_a1;
SELECT dnacimiento INTO @fechaNacimiento
FROM Talumno
WHERE pkid=2;
SELECT @fechaNacimiento;
Podemos emplear varias variables a la vez. Cada columna del SELECT (y en el orden en el que se
encuentran) se va a corresponder con una variable del INTO (en el mismo orden que el SELECT). El
número de columnas y variables debe de ser la misma.
Ejemplo
USE bd_ud6_a1;
SELECT cNombre, dnacimiento INTO @nombre, @fechaNacimiento
FROM Talumno
WHERE pkid=2;
SELECT @nombre,@fechaNacimiento;
7
3.- Cursores
También podemos recuperar los datos de una consulta SQL independientemente del número de
campos y registros que devuelva la consulta, mediante el uso de un cursor.
Los cursores tienen las siguientes propiedades:

• Asensitive: el servidor puede o no hacer una copia de su tabla de resultados.
• Read only: son de sólo lectura. No permiten actualizar los datos.
• Nonscrollable: sólo pueden ser recorridos en una dirección y no podemos saltarnos filas.
Cuando declaramos un cursor dentro de un procedimiento almacenado debe aparecer antes de las
declaraciones de los manejadores de errores (HANDLER) y después de la declaración de variables
locales.
Los cursores son estructuras de almacenamiento volátil que albergan el resultado de una
consulta y permiten recorrer todos sus registros de forma secuencial.
Para utilizar un cursor hay que seguir los siguientes pasos:

1) Declarar el cursor
DECLARE nombre_del_cursor CURSOR FOR sentencia_select;
2) Abrir el cursor
OPEN nombre_del_cursor;
3) Recorrer el cursor
Cada vez que se ejecuta la sentencia FETCH se lee un nuevo registro y el puntero del cursor avanza
una posición. Si la consulta devuelve varios campos sus valores se pueden almacenar en varias
variables, separadas por comas.
FETCH [[NEXT] FROM] nombre_del_cursor INTO variable1 [, variable2] ...
Para recorrer un cursor se utiliza un bucle2 que se ejecuta hasta que se terminan de leer todas las
filas. Cuando se está recorriendo un cursor y no quedan filas por recorrer se lanza el error NOT
FOUND, que se corresponde con el valor SQLSTATE ‘02000’. Por eso cuando estemos trabajando con
cursores será necesario declarar un handler para manejar este error, como por ejemplo:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE;
4. Cerrar el cursor
Cuando hemos terminado de trabajar con un cursor tenemos que cerrarlo.
CLOSE nombre_del_cursor
2 Ver sección 6.3
8
Recorriendo el cursor
Después del OPEN, el cursor se sitúa justo antes de la primera Al realizar la primera orden de FETCH podemos recuperar los
fila de resultados del SELECT datos de la primera fila.
Al realizar el siguiente FETCH podemos recuperar los datos de la Al realizar el siguiente FETCH podemos recuperar los datos de la
siguiente fila (fila 2) siguiente fila (fila3)
Al realizar el siguiente FETCH podemos recuperar los datos de la Al realizar el siguiente FETCH podemos recuperar los datos de la
siguiente fila (fila 4) siguiente fila (fila 5)
Al realizar el último FETCH se lanza la excepción que vamos a

capturar con la orden DECLARE HANDLER
9
Ejemplo:
4.- Procedimientos
Un procedimiento almacenado es un conjunto de instrucciones SQL que se almacena asociado a
una base de datos. Es un objeto que se crea con la sentencia CREATE PROCEDURE y se invoca con la
sentencia CALL. Un procedimiento puede tener cero o muchos parámetros de entrada y cero o
muchos parámetros de salida
Sintaxis
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
10
donde:
• sp_name, es el nombre de la rutina almacenada
• parameter, son los parámetros que en general se caracterizan por un tipo y un nombre. El
tipo puede ser de entrada (IN), salida (OUT) o entrada/salida (INOUT)
• routine_body, es el cuerpo de la rutina formado generalmente por sentencias SQL. En caso
de haber más de una deben ir dentro de un bloque delimitado por sentencias BEGIN y END.
• DETERMINISTIC, indica si es determinista o no, es decir, si siempre produce el mismo
resultado
• CONTAINS SQL/NO SQL, especifica si contiene sentencias SQL o no
• MODIFIES SQL DATA/READS SQL DATA, indica si las sentencias modifican o no los datos.
• SQL SECURITY, determina si debe ejecutarse con permisos del creador (DEFINER) o del que
invoca (INVOKER).
Puedes encontrar más información en la documentación oficial de MySQL.
Como mínimo debemos de indicar un nombre para el procedimiento y un conjunto de órdenes

SQL entre las palabras clave BEGIN y END.
Todos los procedimientos o funciones se crean asociados a una base de datos que será la activa
en ese momento o la que pongamos como prefijo en el nombre del mismo.
Como hemos comentado, un procedimiento se crea dentro de una base de datos concreta. Si no
indicamos la base de datos, lo hará en la base de datos activa (recordar que en MysqlWorkBench
es la que se visualiza en negrilla). Cuando ejecutemos la orden CREATE PROCEDURE, podemos
cambiar antes la base de datos activa con la orden SQL: USE nombre_bd. Otra forma de indicar en
qué base de datos debe crearse el procedimiento es anteponiendo al nombre del mismo, el
nombre de la base de datos seguido por un punto, de la forma: CREATE PROCEDURE
nombreBD.nombreProcedimiento()
Debemos recordar que el procedimiento almacenado debemos ejecutarlo antes de poder

utilizarlo.
11
Para conocer toda la información sobre los procedimientos/funciones almacenados se

utilizaremos la siguiente sintaxis:
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE patrón]
El comando que permite ver información de los procedimientos y funciones así como las líneas de
código es el siguiente:
SHOW CREATE [PROCEDURE | FUNCTION] nombre
Borrado de procedimientos. Sintaxis:
DROP PROCEDURE [IF EXISTS] nombre_procedimiento
Llamada a un procedimiento almacenado:
CALL nombre_prodedimiento([parámetros]);
4.1.- DELIMETER
Para definir un procedimiento almacenado es necesario modificar temporalmente el carácter
separador que se utiliza para delimitar las sentencias SQL.
La palabra clave DELIMITER indica el carácter de comienzo y fin del procedimiento. Típicamente
sería un (;) pero dado que necesitamos un (;) para cada sentencia SQL dentro del procedimiento es
conveniente usar otro carácter, normalmente se usan $$. En los ejemplos que vamos a realizar en
esta unidad vamos a utilizar los caracteres $$ para delimitar las instrucciones SQL, pero seria
posible utilizar cualquier otro carácter.
Mysql toma como delimitador de sentencias SQL el punto y coma por defecto. Quiere decir que
cada vez que encuentra un punto y coma intenta ejecutar la orden SQL. Debemos de ver al
procedimiento almacenado como un todo, como si fuera una única instrucción que queremos que
Mysql ejecute entera. Si mantubiesemos el punto y coma para el procedimiento y quisiésemos
poner una orden SQL dentro del procedimiento almacenado, intentará ejecutarla y no la
interpretará como parte del procedimiento almacenado; por este motivo tenemos que cambiarlo.
Ejemplo de creación de procedimiento:
12
Una vez creado, aparecerá en la sección Stores Procedures en MysqlWorkBench.
Llamada al procedimiento anterior:
CALL bd_ud6_p1.hola_mundo();
Comentarios dentro de procedimientos (en general para toda rutina)
COMMENT 'comentario'
Esto nos permitirá, una vez creada la rutina, que nos muestre la
descripción/comentario al pulsar sobre ella.
Es muy recomendable su utilización, ya que ayuda a conocer lo que realiza

la rutina sin que tengamos que consultar su descripción.
Actividad 1 (1 a 4)
4.2.- Parámetros de entrada, salida y entrada/salida

• 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.
13
4.2.1.- Ejemplo de un procedimiento con parámetros de entrada

Escribe un procedimiento llamado datos_alumnado que reciba como entrada el id del alumno/a y
muestre todos los datos relacionados con el mismo. Este procedimiento no devuelve ningún
parámetro de salida, lo que hace es mostrar datos.
Ejemplo de llamada a este procedimiento: CALL bd_ud6_a1.datos_alumnado(4);
4.2.2.- Ejemplo de procedimiento con parámetro de salida

Escribe un procedimiento llamado numero_repetidores que devuelva el número de alumnado que
es repetidor.
Ejemplo de utilización de este procedimiento:

CALL bd_ud6_a1.numero_repetidores(@total);
SELECT @total AS "Nº de repetidores";
4.2.3.- Ejemplo de procedimiento con parámetros de entrada y de salida
Escribe un procedimiento que se llame calcular_max_min_media, que reciba como parámetro de

entrada el número de un departamento y devuelva como salida tres parámetros: el salario
máximo, el salario mínimo y la media de salarios de aquellos empleados que pertenezcan al
departamento dado como parámetro de entrada.
14
CALL bd_ud6_a2.calcular_max_min_media(10, @maximo, @minimo, @media);

SELECT @maximo, @minimo, @media;
4.2.4.- Ejemplo de procedimiento con parámetro de entrada/salida
Escribe un procedimiento que se llame num_dep_empleado que reciba como parámetro de

entrada un número de empleado y en ese mismo parámetro devuelva el número de departamento
al que pertenece dicho empleado.
SET @np=7521;
CALL bd_ud6_a2.num_dep_empleado(@np);
SELECT @np AS "Nº de Dpto.";
Actividad 1 (5 a 11)
5.- Funciones
Las funciones almacenadas son similares a los procedimientos salvo que sólo devuelven un valor.
Por tanto gran parte de lo que se ha expuesto en el apartado de procedimientos es válido aquí.
La devolución del valor se produce utilizando la cláusula RETURN y no mediante variables OUT o
INOUT. No aparece en la sintaxis, pues no se puede utilizar, los parámetros IN, OUT, INOUT. Todos
los parámetros pasados a la función se definen implícitamente del tipo IN.
La ventaja que presentan las funciones es que pueden ser utilizadas dentro de instrucciones SQL y
por tanto aumentan considerablemente las capacidades de este lenguaje.
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.
15
Sintaxis:
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
Ejemplo:
SELECT test.conversor(332.77) AS "EUROS";
CALL bd_ud6_a2.salario_empleado(7499);
16
5.1.- Características de la función

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. 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.
• 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.
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 un error.
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. 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;
Para consultar el valor de esta variable:

SELECT @@log_bin_trust_function_creators;
Puedes encontrar más información en la documentación oficial de MySQL.
Parámetros de entrada: recuerda, 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.
Actividad 2
17
6.- Estructuras de control

MySQL admite las construcciones IF, CASE, ITERATE, LEAVE LOOP, WHILE y REPEAT para el control
de flujo dentro de los programas almacenados. También admite RETURN dentro de las funciones
almacenadas.
Muchas de estas construcciones contienen otras declaraciones, como se indica en las

especificaciones gramaticales de las siguientes secciones. Tales construcciones pueden anidarse.
Por ejemplo, una sentencia IF puede contener un bucle WHILE, que a su vez contiene una
sentencia CASE.
MySQL no admite bucles FOR.
Puede encontrar más información en la documentación oficial de MySQL.
6.1.- Condicional IF
IF condición_bússqueda THEN lista_instrucciones

[ELSEIF condición_bússqueda THEN lista_instrucciones ....]
[ELSE lista_instrucciones]
END IF
Si una condición_búsqueda dada se evalúa como verdadera, se ejecuta la cláusula correspondiente

THEN o ELSEIF lista_instrucciones. Si ninguna condición_búsqueda coincide, se ejecuta la cláusula
ELSE lista_de_instrucciones.
Actividad 3 (1 y 2)
18
6.2.- Condicional CASE

Similar al condicional IF anterior. Todo lo que se puede expresar con la sentencia IF se puede
expresar con la sentencia CASE. Esta última se suele utilizar cuando el número de expresiones o
condiciones a evaluar es elevado y por tanto la lectura del código se hace más fácil y legible.
Existen 2 formas posibles si lo que se evalúa es una expresión o una condición
CASE expresión
WHEN valor1 THEN
instrucciones
[WHEN valor2 THEN
instrucciones ...]
[ELSE
instrucciones]
END CASE;
CASE
WHEN condición1 THEN
instrucciones
[WHEN condición2 THEN
instrucciones ...]
[ELSE
instrucciones]
END CASE;
19
Actividad 3 (3 y 4)
6.3.- Instrucciones repetitivas: bucles
6.3.1.- LOOP
[etiqueta:] LOOP
instrucciones
END LOOP [etiqueta];
Todas las instrucciones comprendidas entre las palabras reservadas LOOP Y END LOOP (bucle), se
ejecutan un número de veces hasta que la ejecución del bucle se encuentra con la instrucción
LEAVE etiqueta. En ese momento se abandona el bucle.
La sentencia ITERATE etiqueta se utiliza para forzar que la ejecución del bucle termine en el
momento donde se encuentra con esta instrucción y continúe por el principio del bucle
6.3.2.- REPEAT … UNTIL
[etiqueta:] REPEAT
instrucciones
UNTIL expresión
END REPEAT [etiqueta];
Las instrucciones se ejecutarán hasta que sea cierta la expresión. Por lo menos el conjunto de
instrucciones se ejecuta una vez pues la evaluación de la expresión se hace posterior a la ejecución
de las instrucciones
20
El siguiente ejemplo ilustra la utilización de cursores y la estructura repetitiva LOOP.
El siguiente ejemplo ilustra la utilización de cursores y la estructura repetitiva REPEAT.
21
6.3.3.- WHILE
[etiqueta:] WHILE condición DO
instrucciones
END WHILE [etiqueta];
Ejecuta el conjunto de instrucciones mientras sea cierta la condición.
Equivale a la instrucción iterativa anterior pero con LOOP:

etiqueta: LOOP
IF !condición THEN LEAVE etiqueta; END IF;
resto de instrucciones;
END LOOP etiqueta;
Actividad 3 (5 a 8)
7.- Manejo de errores

En general, si una sentencia SQL falla dentro de una rutina almacenada se produce una situación
de error, se interrumpe la ejecución de la rutina en ese punto y finaliza, salvo en el caso de que la
rutina que falla hubiera sido llamada por otra; en ese caso la ejecución continua por la rutina que
llamó a este rutina que ha causado el error. Ocurriría lo mismo si en lugar de una rutina que es
llamada desde otra rutina nos encontráramos con un bloque interno dentro de otro bloque más
externo; si se produjera error en el bloque anidado interno, la ejecución de la rutina se
interrumpiría en el bloque interno para continuar por el externo.
22
Como hemos ya hemos visto en puntos anteriormente, este comportamiento se puede controlar
definiendo los manejadores de error o handlers.
Una handler es un bloque de instrucciones SQL que se ejecuta cuando se verifica una condición
tras una excepción (error) generada por el servidor
DECLARE {CONTINUE | EXIT | UNDO} HANDLER FOR

{SQLSTATE sqlstate_code | MySQL error code | nombre_condición}
instrucciones_del_manejador
Las acciones posibles que podemos seleccionar para el manejador puede ser de tres tipos:
• CONTINUE: La excepción o error generado no interrumpe el código de la rutina, es decir la
ejecución de la rutina continua.
• EXIT: Permite poner fin al bloque de instrucciones o rutina en el que se genera la
excepción.
• UNDO: No está soportado en MySQL
La condición de error del manejador puede expresarse también de tres formas:

• Con un código de error MySQL, propio de MySQL, que dispone de un conjunto particular de
números de error. Esta es la mejor opción trabajando con MySQL.
Referencia de mensajes de error de MySQL
• Con un código estándar ANSI SQLSTATE. A diferencia del anterior, SQLSTATE no es definido
por MySQL sino que es un estándar y por tanto el mismo error tiene asociado el mismo
SQLSTATE independientemente del gestor de bases de datos utilizado.
Referencia valores de código ANSI SQLSTATE
• Una expresión. Predefinidas en MySQL: SQLEXCEPTION SQLWARNING NOT FOUND
Puede encontrar más información en la documentación oficial de MySQL.
23
✗ SQLWARNING es un subconjunto de los códigos SQLSTATE, representando todos los códigos

que empiezan por 01.
✗ NOT FOUND es un subconjunto de los códigos SQLSTATE, representando todos los códigos
que comienzan por 02.
✗ SQLEXCEPTION es un subconjunto de los códigos SQLSTATE, representando todos los
códigos que no comienzan ni por 00, ni por 01 ni por 02
Actividad 4
8.- Realizar transacciones con procedimientos almacenados.

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.
En lugar de tener un manejador para cada tipo de error, podemos tener uno común para todos los
casos:
24
Actividad 5
9.- Triggers
Los triggers, también llamados desencadenadores o disparadores, son rutinas almacenadas que
se ejecutan (“disparan”) automáticamente en respuesta a algún suceso que ocurre en la base de
datos. En MySQL ese tipo de suceso se corresponde con alguna instrucción DML (INSERT, UPDATE,
DELETE) sobre alguna tabla.
Suponen un mecanismo para asegurar la intregridad de los datos. Se emplean también como un
método para realizar operaciones de auditoría sobre la base de datos. No hay que abusar de su
utilización pues ello puede traer consigo una sobrecarga del sistema y por tanto un bajo
rendimiento del mismo.
CREATE [DEFINER={cuenta_usuario | CURRENT_USER}] TRIGGER nombre_trigger

{BEFORE | AFTER}
{UPDATE | INSERT | DELETE}
ON tabla FOR EACH ROW
cuerpo_del_trigger
Donde:
• DEFINER ={cuenta_usuario | CURRENT_USER } indica con qué privilegios se ejecutan las
instrucciones del trigger. La opción por defecto es CURRENT_USER, que indica que las
instrucciones se ejecutan con los privilegios del usuario que lanzó la instrucción de creación
del trigger. La opción cuenta_usuario por otro lado hace que el trigger se ejecute con los
privilegios de dicha cuenta.
• Nombre del trigger. Sigue las mismas normas que para nombrar cualquier objeto de la base
de datos.
• BEFORE | AFTER. Señala cuando se ejecuta el trigger, antes (before) o después (alter) de la
instrucción DML que lo provocó.
• UPDATE | INSERT | DELETE. Define la operación DML asociada al trigger.
• ON tabla. Define la tabla base asociada al trigger.
• FOR EACH ROW. Indica que el trigger se ejecutará por cada fila de la tabla afectada por la
operación DML. Esto es, si tenemos asociado un trigger a la operación de borrado de una
tabla y se eliminan con una sola instrucción 6 filas de ésta última, el trigger se ejecutará 6
veces, una por cada fila eliminada. Otros gestores de bases de datos (así como futuras
implementaciones de MySQL) consideran también el otro estándar
https://dev.mysql.com/doc/refman/5.7/en/declare-handler.htmlde ANSI, la cláusula FOR
EACH STATEMENT. Con esta segunda opción, el trigger se ejecutaría por cada operación
DML realizada; en el ejemplo anterior, la instrucción de borrado daría lugar a que sólo se
ejecutara el trigger una sola vez en lugar de 6 (filas afectadas) con esta futura cláusula.
25
• Cuerpo del trigger: El conjunto de instrucciones que forman esta rutina almacenada. Si son
más de una irán en un bloque BEGIN … END. No pueden contener una instrucción CALL de
llamada a un procedimiento almacenado
Puede encontrar más información en la documentación oficial de MySQL
En el ejemplo anterior, crea una fila nueva en la tabla Talumnos como consecuencia de la
instrucción de la línea 24 y otra también idéntica en la tabla Talumnos_replica debido a la
ejecución automática del conjunto de instrucciones del trigger.
Consideraciones:
Debido a que un disparador está asociado con una tabla en particular, no se pueden tener
múltiples disparadores con el mismo nombre dentro de una tabla. También se debería tener en
cuenta que el espacio de nombres de los disparadores puede cambiar en el futuro de un nivel de
tabla a un nivel de base de datos, es decir, los nombres de disparadores ya no sólo deberían ser
únicos para cada tabla sino para toda la base de datos. Para una mejor compatibilidad con
desarrollos futuros, se debe intentar emplear nombres de disparadores que no se repitan dentro
de la base de datos.
26
Adicionalmente al requisito de nombres únicos de disparador en cada tabla, hay otras limitaciones
en los tipos de disparadores que pueden crearse. En particular, no se pueden tener dos
disparadores para una misma tabla que sean activados en el mismo momento y por el mismo
evento. Por ejemplo, no se pueden definir dos BEFORE INSERT o dos AFTER UPDATE en una misma
tabla. Es improbable que esta sea una gran limitación, porque es posible definir un disparador que
ejecute múltiples sentencias empleando el constructor de sentencias compuestas BEGIN ... END
luego de FOR EACH ROW. (Más adelante en esta sección puede verse un ejemplo).
También hay limitaciones sobre lo que puede aparecer dentro de la sentencia que el disparador
ejecutará al activarse:
• El disparador no puede referirse a tablas directamente por su nombre, incluyendo la

misma tabla a la que está asociado. Sin embargo, se pueden emplear las palabras clave
OLD y NEW. OLD se refiere a un registro existente que va a borrarse o que va a
actualizarse antes de que esto ocurra. NEW se refiere a un registro nuevo que se
insertará o a un registro modificado luego de que ocurre la modificación.
• El disparador no puede invocar procedimientos almacenados utilizando la sentencia

CALL. (Esto significa, por ejemplo, que no se puede utilizar un procedimiento
almacenado para eludir la prohibición de referirse a tablas por su nombre).
• El disparador no puede utilizar sentencias que inicien o finalicen una transacción, tal
como START TRANSACTION, COMMIT, o ROLLBACK.
Las palabras clave OLD y NEW permiten acceder a columnas en los registros afectados por un
disparador. (OLD y NEW no son sensibles a mayúsculas). En un disparador para INSERT, solamente
puede utilizarse NEW.nom_col; ya que no hay una versión anterior del registro. En un disparador
para DELETE sólo puede emplearse OLD.nom_col, porque no hay un nuevo registro. En un
disparador para UPDATE se puede emplear OLD.nom_col para referirse a las columnas de un
registro antes de que sea actualizado, y NEW.nom_col para referirse a las columnas del registro
luego de actualizarlo.
Una columna precedida por OLD es de sólo lectura. Es posible hacer referencia a ella pero no
modificarla. Una columna precedida por NEW puede ser referenciada si se tiene el privilegio
SELECT sobre ella. En un disparador BEFORE, también es posible cambiar su valor con SET
NEW.nombre_col = valor si se tiene el privilegio de UPDATE sobre ella. Esto significa que un
disparador puede usarse para modificar los valores antes que se inserten en un nuevo registro o se
empleen para actualizar uno existente.
En un disparador BEFORE, el valor de NEW para una columna AUTO_INCREMENT es 0, no el

número secuencial que se generará en forma automática cuando el registro sea realmente
insertado.
27
Dicho todo lo anterior, diremos que dentro del trigger puedo acceder a dos 'alias' de la tabla a la
que afecta el trigger con las mismas columnas que la tabla original:
NEW: Tabla que posee los datos de cada columna con los nuevos valores.
OLD: Tabla que posee los datos de cada columna con los valores antiguos.
Operaciones
UPDATE:
NEW: Valores nuevos
OLD: Valores antiguos
INSERT
NEW: Valores nuevos
OLD: NO EXISTE
DELETE
NEW: NO EXISTE
OLD: Valores antiguos
Actividad 6
Prácticas
10.- Bibliografía
• Bases de datos. Editorial Garceta
• Bases de datos. Editorial Rama
• Bases de datos. Editorial Síntesis
• Bases de datos. Editorial Paraninfo
• Bases de datos. Materiales de FP del MEFP.
• https://mariadb.com/kb/en/documentation/
• https://mariadb.com/kb/es/usando-mariadb/
• https://dev.mysql.com/doc/refman/8.0/en/
28

UD8 Programacion BD

Cargado por

Copyright:

Formatos disponibles

UD8 Programacion BD

Cargado por

Información del documento

Descripción original:

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

UD8 Programacion BD

Cargado por

Copyright:

Formatos disponibles

2023

Fecha última revisión:

Desarrolla procedimientos almacenados, evaluando y utilizando las

En las unidades anteriores se ha abordado el lenguaje SQL y la ejecución de sus instrucciones de

Los sistemas gestores de bases de datos implementan mecanismos para potenciar la

Un programa almacenado o rutina es un conjunto de instrucciones almacenadas dentro del

1.1.- Procedimientos, funciones y triggers en MySQL

El esquema general de una rutina almacenada se resume en el siguiente esquema:

Nombre rutina + parámetros (entrada, salida o entrada/salida)

Declaración e inicialización de variables

1.2.- Ventajas de su utilización

2.- Variables y tipos de datos

DECLARE nombre_variable1 [,nombre_variable2...] tipo_dato [DEFAULT valor];

Para asignar valores a variables se utiliza la siguiente sintaxis:

SET nombre_variable1 = expresión1 [,nombre_variable2 = expresión2 ...]

Veamos algún ejemplo:

DECLARE variable1 INT;

SET variable1 = 100;

DECLARE maximo INT;

2.1 Variables de sesión.

SET @nombre = “Juan”;

Los cursores tienen las siguientes propiedades:

Para utilizar un cursor hay que seguir los siguientes pasos:

FETCH [[NEXT] FROM] nombre_del_cursor INTO variable1 [, variable2] ...

2 Ver sección 6.3

Al realizar el último FETCH se lanza la excepción que vamos a

Puedes encontrar más información en la documentación oficial de MySQL.

Como mínimo debemos de indicar un nombre para el procedimiento y un conjunto de órdenes

Debemos recordar que el procedimiento almacenado debemos ejecutarlo antes de poder

Para conocer toda la información sobre los procedimientos/funciones almacenados se

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE patrón]

SHOW CREATE [PROCEDURE | FUNCTION] nombre

Borrado de procedimientos. Sintaxis:

DROP PROCEDURE [IF EXISTS] nombre_procedimiento

Llamada a un procedimiento almacenado:

Ejemplo de creación de procedimiento:

Una vez creado, aparecerá en la sección Stores Procedures en MysqlWorkBench.

Llamada al procedimiento anterior:

Comentarios dentro de procedimientos (en general para toda rutina)

Es muy recomendable su utilización, ya que ayuda a conocer lo que realiza

4.2.- Parámetros de entrada, salida y entrada/salida

4.2.1.- Ejemplo de un procedimiento con parámetros de entrada

Ejemplo de llamada a este procedimiento: CALL bd_ud6_a1.datos_alumnado(4);

4.2.2.- Ejemplo de procedimiento con parámetro de salida

Ejemplo de utilización de este procedimiento:

4.2.3.- Ejemplo de procedimiento con parámetros de entrada y de salida

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

Ejemplo de utilización de este procedimiento:

CALL bd_ud6_a2.calcular_max_min_media(10, @maximo, @minimo, @media);

4.2.4.- Ejemplo de procedimiento con parámetro de entrada/salida

Escribe un procedimiento que se llame num_dep_empleado que reciba como parámetro de

Ejemplo de utilización de este procedimiento:

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

SELECT test.conversor(332.77) AS "EUROS";

5.1.- Características de la función

Es posible configurar el valor de la variable global log_bin_trust_function_creators a 1, para

SET GLOBAL log_bin_trust_function_creators = 1;

Para consultar el valor de esta variable:

Puedes encontrar más información en la documentación oficial de MySQL.