Bucles y cursores en MySQL con ejemplos

Gaspar Fernández

hace 10 años

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