Hace mucho tiempo que quería escribir sobre esto. Y aunque es recomendable no abusar de los bucles en MySQL, es más, a veces no son necesarios, y siempre hay que buscar una solución que no los use, a veces no la vamos a encontrar y será en esos casos cuando debamos utilizarlos.
Veamos un bucle muy sencillo, parecido a un for de los de toda la vida, en el que contamos del 1 al 9:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
cuando hagamos:
1 |
Veremos algo como esto:
+———+
| counter |
+———+
| 1 |
+———+
1 row in set (0.01 sec)+———+
| counter |
+———+
| 2 |
+———+
1 row in set (0.01 sec)+———+
| counter |
+———+
| 3 |
+———+
1 row in set (0.01 sec)+———+
| counter |
+———+
| 4 |
+———+
1 row in set (0.01 sec)+———+
| counter |
+———+
| 5 |
+———+
1 row in set (0.01 sec)+———+
| counter |
+———+
| 6 |
+———+
1 row in set (0.01 sec)+———+
| counter |
+———+
| 7 |
+———+
1 row in set (0.01 sec)+———+
| counter |
+———+
| 8 |
+———+
1 row in set (0.01 sec)+———+
| counter |
+———+
| 9 |
+———+
1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)
Vemos que el código que iteraremos está entre LOOP…END LOOP, lo que aparece justo antes (my_loop) es una etiqueta para nombrar ese bucle. Ahora bien, en este ejemplo simplemente incrementamos la variable counter, y con una condición IF hacemos que el bucle llegue a su fin cuando counter sea 10. Ese 10 no lo veremos porque abandonamos el bucle antes del SELECT.
Propongamos un ejemplo más complicado, vamos a registrar las puntuaciones obtenidas en un juego, este juego consistirá en una prueba que debemos realizar en el menor tiempo posible, a pata coja y con obstáculos, y tenemos dos tipos de falta, uno es apoyar la pierna levantada, y otra es chocar con un obstáculo, al final de la prueba se asignarán los puntos y se almacenarán en la tabla, para no tener que calcularlos cada vez.
1 2 3 4 5 6 7 8 9 |
Ahora introducimos algo de información para probar:
1 2 3 4 5 6 7 | INSERT INTO Runners VALUES (NULL, 'Michael', 123, 5, 2, NULL); INSERT INTO Runners VALUES (NULL, 'Sarah', 83, 3, 3, NULL); INSERT INTO Runners VALUES (NULL, 'John', 323, 1, 1, NULL); INSERT INTO Runners VALUES (NULL, 'Ramon', 100, 8, 4, NULL); INSERT INTO Runners VALUES (NULL, 'Andrew', 143, 4, 3, NULL); INSERT INTO Runners VALUES (NULL, 'Antoine', 199, 3, 2, NULL); INSERT INTO Runners VALUES (NULL, 'David', 101, 2, 1, NULL); |
Lo primero que vamos a hacer, será un procedimiento que incluya el bucle básico, con SELECTs, para ver que todo funciona y que lo estamos haciendo bien. (Debajo explicaré para qué es cada cosa):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | DROP PROCEDURE IF EXISTS cursorTest; DELIMITER $$ CREATE PROCEDURE cursorTest ( ) BEGIN -- Variables donde almacenar lo que nos traemos desde el SELECT DECLARE v_name VARCHAR(120); DECLARE v_time BIGINT; DECLARE v_penalty1 BIGINT; DECLARE v_penalty2 BIGINT; -- Variable para controlar el fin del bucle DECLARE fin INTEGER DEFAULT 0; -- El SELECT que vamos a ejecutar DECLARE runners_cursor CURSOR FOR SELECT Name, Time, Penalty1, Penalty2 FROM Runners; -- Condición de salida DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1; OPEN runners_cursor; get_runners: LOOP FETCH runners_cursor INTO v_name, v_time, v_penalty1, v_penalty2; IF fin = 1 THEN LEAVE get_runners; END IF; SELECT v_name, v_time, v_penalty1, v_penalty2; END LOOP get_runners; CLOSE runners_cursor; END$$ DELIMITER ; |
Tenemos que tener en cuenta que en este cursor recorreremos el resultado de un SELECT y en cada fila podremos almacenar el valor de cada campo en variables (por eso declaramos v_name, v_time, v_penalty y v_penalty1). Al final, cada fila será como un SELECT Name, Time, Penalty1, Penalty2 INTO v_name, v_time, v_penalty1, v_penalty2 WHERE … y en cada iteración, tendremos unos valores para esas variables, correspondiendo con filas obtenidas de forma consecutiva. Para esto es el DECLARE xxx CURSOR FOR SELECT …
Tenemos que poner también una condición de finalización, normalmente, cuando no haya más filas, por eso el DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1, en ese caso, cuando no encontremos más filas, pondremos un 1 en la variable fin.
Dentro del bucle, analizaremos el valor de la variable fin para ver si finalizamos (LEAVE xxxxx) o ejecutamos una iteración.
Demos un paso más, vamos a crear una función que asigne las puntuaciones a cada uno de los corredores con una fórmula. Por ejemplo la siguiente: siendo Time el tiempo en segundos que se tarda en realizar la prueba, 500-Time serán los puntos iniciales, a los que tenemos que restar 5*penalty1 y 3*penalty2. Por tanto:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Ahora el código para calcular los puntos de los jugadores puede ser:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | DROP PROCEDURE IF EXISTS calculate_all_points; DELIMITER $$ CREATE PROCEDURE calculate_all_points ( ) BEGIN -- Variables donde almacenar lo que nos traemos desde el SELECT DECLARE v_name VARCHAR(120); DECLARE v_time BIGINT; DECLARE v_penalty1 BIGINT; DECLARE v_penalty2 BIGINT; DECLARE v_runner_id BIGINT; -- Variable para controlar el fin del bucle DECLARE fin INTEGER DEFAULT 0; -- El SELECT que vamos a ejecutar DECLARE runners_cursor CURSOR FOR SELECT Runner_id, Name, Time, Penalty1, Penalty2 FROM Runners; -- Condición de salida DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1; OPEN runners_cursor; get_runners: LOOP FETCH runners_cursor INTO v_runner_id, v_name, v_time, v_penalty1, v_penalty2; IF fin = 1 THEN LEAVE get_runners; END IF; UPDATE Runners SET Points=calculate_runner_points(v_time, v_penalty1, v_penalty2) WHERE Runner_id=v_runner_id; END LOOP get_runners; CLOSE runners_cursor; END$$ DELIMITER ; |
Pero claro, como dije al principio, tenemos que mirar siempre, si hay alguna solución posible que no utilice bucles, sobre todo porque cuando empezamos a utilizarlos nos emocionamos (igual que dice que también nos emocionamos con las expresiones regulares) y vemos la solución con bucles más inmediata que sin bucles, pero claro, con bucles todo irá mucho más lento. Podríamos haber hecho:
Aunque podemos hacer algunas cosas más con el ejemplo del bucle, por ejemplo, si el tiempo es mayor de 250, se intercambien los penalties, editando directamente el código del bucle, metiendo una sentencia IF, aunque eso mismo lo podemos hacer también desde la función que calcula los puntos.
Otro pequeño ejemplo (bueno, no tan pequeño) que me viene a la cabeza es que tenemos un sistema de usuarios en el que cada usuario tiene información en tres tablas: una para login, password e información de acceso; otra para información de perfil y otra de permisos. En este caso, en todas las tablas excepto en la de permisos habrá una entrada por usuario, pero en los permisos estableceremos el elemento sobre el que un usuario tiene permiso y qué tipo de permiso tiene, y como podemos tener permiso sobre varios objetos, puede haber varias entradas por usuario.
También tenemos una tabla de mensajes entre usuarios.
Por otro lado tenemos las páginas, que serán objetos de nuestro sistema y serán sobre las que los usuarios podrán ver, editar, crear derivados y borrar (los diferentes permisos del sistema), eso sí, para las páginas existe una jerarquía, por lo que podremos tener páginas «hijas». Pero cuando creamos una página en el sistema:
- Al menos tendrán que tener permiso total sobre ella los administradores del sistema (marcados en la tabla de acceso)
- Si un usuario tenía permiso de edición sobre una página padre, podrá editar la nueva página hija
- Si un usuario podía crear derivadas en la página padre, podrá hacerlo en la hija
- Si un usuario podía editar y crear derivadas en la padre, podrá borrar en la hija
- Además, tenemos que enviar un mensaje (meter el mensaje en la tabla), al usuario con los permisos que tendrá en la nueva página
- Tenemos para ello las funciones y procedimientos:
- puede_crear_derivadas(usuario, pagina) – Que devolverá TRUE si el usuario puede crear páginas derivadas
- puede_editar(usuario, pagina) – Que hará lo mismo que la anterior pero con el nuevo permiso
- nuevo_permiso(usuario, pagina, permiso) – Insertará un nuevo permiso en la tabla de permisos
- mensaje(from, to, mensaje) – Enviará un usuario a un usuario.
Las funciones puede_crear_derivadas() y puede_editar() en principio son fáciles de entender, pero su funcionamiento interno es mucho más complejo, las ha hecho un compañero de trabajo y no tenemos ganas de meternos a ver qué ha liado. Lo mismo pasa con nuevo_permiso() (que puede insertar entradas en la tabla o modificar las existentes) o con mensaje(), que enviará notificaciones y además creará una tarea para mandar el mensaje por e-mail, por lo que nuestro procedimiento para crear una página quedaría así:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | DROP PROCEDURE IF EXISTS crear_pagina; DELIMITER $$ CREATE PROCEDURE crear_pagina ( IN in_nombre VARCHAR(120), IN in_parent BIGINT ) BEGIN -- Variables donde almacenar lo que nos traemos desde el SELECT DECLARE v_user_id BIGINT; DECLARE v_crear_derivadas TINYINT; DECLARE v_object_id BIGINT; DECLARE v_mens TEXT; -- Variable para controlar el fin del bucle DECLARE fin INTEGER DEFAULT 0; -- El SELECT que vamos a ejecutar DECLARE users_cursor CURSOR FOR SELECT User_id FROM Users; -- Condición de salida DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1; INSERT INTO Paginas (Nombre, Parent) VALUES (in_nombre, in_parent); SELECT LAST_INSERT_ID() INTO v_object_id; OPEN users_cursor; get_users: LOOP FETCH users_cursor INTO v_user_id; IF fin = 1 THEN LEAVE get_users; END IF; SET v_mens = CONCAT('Nuevos permisos sobre la pagina: ',in_nombre,': '); IF puede_crear_derivadas(v_user_id, in_parent) THEN CALL nuevo_permiso(v_user_id, v_object_id, 'derivadas'); SET v_mens = CONCAT(v_mens, 'Crear derivadas '); SET v_crear_derivadas=1; ELSE SET v_crear_derivadas=0; END IF; IF puede_editar(v_user_id, in_parent) THEN CALL nuevo_permiso(v_user_id, v_object_id, 'editar'); SET v_mens = CONCAT(v_mens, 'Editar '); IF v_crear_derivadas=1 THEN CALL nuevo_permiso(v_user_id, v_object_id, 'borrar'); SET v_mens = CONCAT(v_mens, 'Borrar '); END IF; END IF; CALL mensaje(1, v_user_id, v_mens); END LOOP get_users; CLOSE users_cursor; END$$ DELIMITER ; |
Seguro que en el futuro se me ocurren ejemplos algo mejores, es más, se aceptan sugerencias en los comentarios, intentaré recrear los ejemplos y resolverlos en futuros posts.
Foto: Kellie Bollaret (Flickr CC) Licencia CC-by
Pingback: Bucles y cursores en MySQL con ejemplos « DbRunas – Noticias y Recursos sobre Bases de Datos /
y se puede recorrer un cursor dentro de otro cursor???
Sin problema. Eso sí, con el jaleo que hay que montar para cada bucle, estaría bien crear un procedimiento para cada uno, más que nada para que quede ordenado y no haya demasiado caos.
HOla Gaspar me gustaria ver un ejemplo… de los dos bucles anidados (Loop). Mi duda es que cuando cierro el de dentro se cierran los dos
Excelente descripción y uso de Cursores con Mysql
Muchas gracias
Muchas gracias por tu comentario ! Un abrazo !
Muy bueno, estimado, no me demore nada en implementarlo
Me alegro mucho. Muchas gracias por tu comentario !
Pingback: Un buen 2015 para el blog. Los posts que han marcado el año y consultas SQL – Poesía Binaria /
Pingback: Cómo expulsar todas las sesiones MySQL de un usuario en particular desde un script – Poesía Binaria /
Gazpar, puedo enviarte un caso real que estoy usando con cursores anidados. Dónde te lo hago llegar ?
Ya tienes mi correo para enviármelo. Muchas gracias
Tengo un caso particular: en una tabla tengo un campo tipo text al cual le llevo una formula que se va estructurando en un Proceso quedando como resultado algo similar a esto:
1er – ‘(6371.59)*(0.083)/((18.6)/(18.6))’
2do – ‘((1)/(52))*(((173.90)/(5)/(6.5))+((173.90)/(5)/(21))+(((173.90)*(2.00))/(5)/(25)))’
en ese campo quedan valores con múltiples operadores matemáticos. Cuando ya tengo todos los registros debo calcular en un campo tipo «decimal» de la misma tabla el resultado de esas operaciones del campo TEXT, se debe ejecutar la operación matemática correspondiente para el primero da 528.84
y para el segundo da como resultado 0.18825692
El problema que tengo es que para poder hacer el calculo matemático del campo tipo text y llevarlo al campo tipo decimal me tocó realizar un cursor y son muchos registros a recorrer y me está tomando demasiado tiempo.
set @query = concat(‘ Update tabla
set cantidad = ‘ , rtrim(strTexto),
‘ where ID = ‘, strID, ‘ ;’);
Prepare stmts from @query;
EXECUTE stmts;
DEALLOCATE PREPARE stmts;
Alguna sugerencia?
Lo dejo por aquí para cuando tenga un rato, aunque tal vez otro usuario pueda echarte una mano mientras tanto.
Pingback: El 2016 para Poesía Binaria. Estadísticas, agradecimientos, redes y SQL. Y sólo es el principio – Poesía Binaria /
he realizado esto con tu ejemplo. se supone que debe actualizar un campo imagen con su respectivo colos segun sea el dato de la variable porcentaje.
pero no hace nada y no manda ningun error que estoy haciendo mal.
BEGIN
DECLARE Fin BOOLEAN DEFAULT true;
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE porcentaje DOUBLE(10,2);
DECLARE VarProyecto VARCHAR(10);
DECLARE Varobjetivo INT;
DECLARE VarNumero INT;
DECLARE Mensaje VARCHAR(100);
DEClARE cursor1 CURSOR FOR
SELECT actividades.proyecto, actividades.Objetivo, actividades.numero, actividades.PorcDiasTrans FROM actividades;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Fin = TRUE;
OPEN cursor1;
c1_loop: LOOP
FETCH cursor1 INTO VarProyecto, Varobjetivo, VarNumero, porcentaje;
IF Fin THEN
LEAVE c1_loop;
END IF;
IF porcentaje > 99 THEN
UPDATE actividades SET actividades.Imagen = «rojo.png»;
ELSEIF porcentaje > 69 AND porcentaje < 100 THEN
UPDATE actividades SET actividades.Imagen = "amarilo.png";
ELSEIF porcentaje < 70 THEN
UPDATE actividades SET actividades.Imagen = "verde.png";
ELSEIF porcentaje < 1 THEN
UPDATE actividades SET actividades.Imagen = "negro.png";
END IF;
END LOOP c1_loop;
CLOSE cursor1;
END
Tu condición para salir es que fin sea true, y la inicializas en true, asi que cuando llega por primera vez al if Fin then, se sale del ciclo sin siquiera hacer una iteración, debes inicializar fin en false.
Pingback: El 2017 para Poesía Binaria. Posts, cambios, retos y lenguajes – Poesía Binaria /