Publi

Bucles y cursores en MySQL con ejemplos

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
DELIMITER $$
CREATE PROCEDURE simple_loop ( )
BEGIN
  DECLARE counter BIGINT DEFAULT 0;
 
  my_loop: LOOP
    SET counter=counter+1;

    IF counter=10 THEN
      LEAVE my_loop;
    END IF;

    SELECT counter;

  END LOOP my_loop;
END$$
DELIMITER ;

cuando hagamos:

1
CALL simple_loop();

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
CREATE TABLE Runners (
    Runner_id BIGINT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(120) NOT NULL,
    Time BIGINT NOT NULL,
    Penalty1 BIGINT NOT NULL,
    Penalty2 BIGINT NOT NULL,
    Points BIGINT,
    PRIMARY KEY (Runner_id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

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
DROP FUNCTION IF EXISTS calculate_runner_points;
DELIMITER $$
CREATE FUNCTION calculate_runner_points (
  In_time BIGINT,
  In_penalty1 BIGINT,
  In_penalty2 BIGINT
) RETURNS BIGINT
BEGIN
  DECLARE points BIGINT;
 
  SET points = 500 - In_time - In_penalty1*5 - In_penalty2*3;
 
  RETURN points;
END$$
DELIMITER ;

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:

1
UPDATE Runners SET Points=calculate_runner_points(Time, Penalty1, Penalty2);

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

También podría interesarte....

There are 18 comments left Ir a comentario

  1. Pingback: Bucles y cursores en MySQL con ejemplos « DbRunas – Noticias y Recursos sobre Bases de Datos /

  2. marcelo /
    Usando Google Chrome Google Chrome 38.0.2125.111 en Windows Windows NT

    y se puede recorrer un cursor dentro de otro cursor???

    1. Gaspar Fernández / Post Author
      Usando Mozilla Firefox Mozilla Firefox 33.0 en Linux Linux

      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.

      1. Uber /
        Usando Mozilla Firefox Mozilla Firefox 24.0 en Windows Windows XP

        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

  3. Miguel Vásquez /
    Usando Google Chrome Google Chrome 46.0.2490.86 en Windows Windows 7

    Excelente descripción y uso de Cursores con Mysql

    Muchas gracias

    1. Gaspar Fernández / Post Author
      Usando Mozilla Firefox Mozilla Firefox 42.0 en Ubuntu Linux Ubuntu Linux

      Muchas gracias por tu comentario ! Un abrazo !

  4. armando /
    Usando Google Chrome Google Chrome 47.0.2526.80 en Windows Windows 8

    Muy bueno, estimado, no me demore nada en implementarlo

    1. Gaspar Fernández / Post Author
      Usando Mozilla Firefox Mozilla Firefox 42.0 en Ubuntu Linux Ubuntu Linux

      Me alegro mucho. Muchas gracias por tu comentario !

  5. Pingback: Un buen 2015 para el blog. Los posts que han marcado el año y consultas SQL – Poesía Binaria /

  6. Pingback: Cómo expulsar todas las sesiones MySQL de un usuario en particular desde un script – Poesía Binaria /

  7. Waldo Gomez Alvarez /
    Usando Google Chrome Google Chrome 51.0.2704.106 en Windows Windows 7

    Gazpar, puedo enviarte un caso real que estoy usando con cursores anidados. Dónde te lo hago llegar ?

    1. Gaspar Fernández / Post Author
      Usando Mozilla Firefox Mozilla Firefox 47.0 en Ubuntu Linux Ubuntu Linux

      Ya tienes mi correo para enviármelo. Muchas gracias

  8. Fredy /
    Usando Google Chrome Google Chrome 54.0.2840.99 en Windows Windows NT

    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?

    1. Gaspar Fernández / Post Author
      Usando Mozilla Firefox Mozilla Firefox 50.0 en Ubuntu Linux Ubuntu Linux

      Lo dejo por aquí para cuando tenga un rato, aunque tal vez otro usuario pueda echarte una mano mientras tanto.

  9. Pingback: El 2016 para Poesía Binaria. Estadísticas, agradecimientos, redes y SQL. Y sólo es el principio – Poesía Binaria /

  10. mario athanasiadis /
    Usando Mozilla Firefox Mozilla Firefox 51.0 en Mac OS X Mac OS X 10

    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

    1. Erick Mendoza Padilla /
      Usando Google Chrome Google Chrome 73.0.3683.103 en Windows Windows NT

      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.

  11. Pingback: El 2017 para Poesía Binaria. Posts, cambios, retos y lenguajes – Poesía Binaria /

Leave a Reply