BD UD7 Cursores

De MediaWiki
Ir a la navegación Ir a la búsqueda

Introducción

Nota: Todos los ejemplos están basados en esta base de datos.



  • Un cursor es un recurso de programación que nos ofrecen casi todos los gestores de bases de datos relacional y consiste en la posibilidad de recorrer fila a fila un conjunto de resultados provenientes de una sentencia SELECT.
La ventaja que tiene el uso de cursores es que vamos a poder guardar en variables locales cada fila de resultados y manejar esa información como queramos (para realizar otras consultas, operaciones de INSERT, UPDATE o DELETE).
Recordar cuando vimos procedimientos y funciones que en algunos ejercicios teníamos que limitar el resultado del SELECT a una única fila, ya que el INTO no podría hacerse. Con un cursor, la consulta va a poder devolver muchas filas, ya que nos permite obtener los datos de cada fila e ir pasando de una fila a la siguiente.
Sólo se debe hacer uso de este recurso si no es posible obtener el mismo resultado con una única operación SQL.


  • En nuestra base de datos de ejemplo, supongamos que queremos comprobar si las ganancias totales de cada atracción coinciden con la suma de las ganancias de los días en los que se celebró.
Esta operación la podríamos hacer
  • Empleando un cursor, de tal forma que iríamos recorriendo fila a fila cada una de las atracciones, y por cada atracción obtendríamos su nombre y ganancia total, que guardaríamos en variables locales, haciendo una consulta de la suma de las ganancias por cada día de la atracción guardada en la variable, comprobando si la suma coincide con la ganancia total.
  • Realizar una consulta SQL de la forma:
1 SELECT *
2 FROM ATRACCIONES
3 WHERE ganancias <> (SELECT SUM(ganancias)
4                    FROM ATRACCION_DIA
5                    WHERE ATRACCION_DIA.nombre_atraccion = ATRACCIONES.nombre)
Siempre es mejor la segunda opción ya que es mucho más rápida. Con el cursor tenemos que ir procesando fila a fila.
  • Pero ahora supongamos que queremos obtener una cadena con el formato: atraccion1:gananciatotal:gananciasumada, atraccion2:gananciatotal:gananciasumada
Con las atracciones que no cumplen que la suma sea igual...
En este caso ya no podremos hacer uso de la sentencia SELECT anterior y tendremos que hacer uso de un CURSOR.



  • Un cursor tiene las siguientes características:
  • Son de sólo lectura: Sólo sirven para leer datos. Es decir, sólo podremos declarar un cursor para leer los datos que provienen de una consulta SELECT y nunca podremos modificar los datos de la tabla a través del cursor.
  • De acceso secuencial: La información que va a procesar el cursor (el resultado de un SELECT) es secuencial. Vamos a recorrer fila a fila desde la primera a la última de forma secuencial, una detrás de otra y no podremos saltar a una fila cualquiera de forma directa, tendremos que pasar por todas las anteriores.
  • Puede crearse dentro de un procedimiento, función o trigger.



Manejo de cursores

  • Necesitamos realizar las siguientes operaciones:
  • DECLARACIÓN DEL CURSOR: Donde definimos el nombre del cursor y la consulta SQL SELECT que va a devolver el conjunto de resultados.
  • APERTURA DEL CURSOR: Para poder procesar la información que viene en el SELECT.
  • LECTURA DEL CURSOR: Esto se realiza con la orden FETCH que permite guardar la información de una fila en variables locales y pasa a la siguiente fila del conjunto de resultados. La lectura va en un bucle que se ejecuta hasta que se terminan de leer todas las filas.
  • CIERRE DEL CURSOR: Para liberar de la memoria el cursor creado.



  • Veamos en que sentencias SQL se traduce cada uno de los pasos anteriores:
  • DECLARACIÓN DEL CURSOR: Donde definimos el nombre del cursor y la consulta SQL SELECT que va a devolver el conjunto de resultados.
1 DECLARE nome_cursor CURSOR FOR 
2   sentencia_SELECT;
Notas:
  • Indicar que para salir del bucle vamos a 'capturar' una excepción de 'no hay más datos'. La definición de dicha captura tienen que ir después de DECLARE. Veremos la captura de excepciones en profundidad en la siguiente sección de esta Wiki.
  • La declaración del cursor tiene que ir después de la declaración de las variables y condiciones.
  • En la sentencia SELECT seleccionaremos las columnas que queremos guardar 'por cada fila' al recorrer el cursor.
  • Dentro de un procedimiento, trigger o función podemos tener varios cursores, pero todos ellos deben de tener un nombre diferente.
Por ejemplo, vamos a declarar un cursor en el que seleccionaremos los artistas que no tengan jefe:
1 DECLARE v_noHayMasDatos INT DEFAULT FALSE;
2 
3 DECLARE c_artistas CURSOR FOR 
4   SELECT apellidos,nombre FROM ARTISTAS WHERE nif_jefe IS NULL;
5 
6 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_noHayMasDatos = TRUE;
Notas:
  • Linea 1: Declaramos una variable de tipo INT que vamos a utilizar para saber cuando acabamos de recorrer las filas del cursor. Recordar que la definición de variables tiene que ir antes de la definición del cursor.
  • Linea 3: Declaramos el cursor.
  • Linea 6: Capturamos la excepción que se produce cuando el cursor no encuentra más datos. Lo que indica dicha línea es, "cuando se produce la excepción, asigna a la variable v_noHayMasDatos el valor TRUE.


  • APERTURA DEL CURSOR: Para poder procesar la información que viene en el SELECT.
1 OPEN nombre_cursor;
Siguiendo nuestro ejemplo:
1 DECLARE v_noHayMasDatos INT DEFAULT FALSE;
2 
3 DECLARE c_artistas CURSOR FOR 
4   SELECT apellidos,nombre FROM ARTISTAS WHERE nif_jefe IS NULL;
5 
6 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_noHayMasDatos = TRUE;
7 
8 OPEN c_artistas;


  • LECTURA DEL CURSOR: Esto se realiza con la orden FETCH que permite guardar la información de una fila en variables locales y pasa a la siguiente fila del conjunto de resultados. La lectura va en un bucle que se ejecuta hasta que se terminan de leer todas las filas.
1 FETCH nombre_cursor INTO variable1[,variable2,...];
  • En la parte INTO deben ir tantas variables como columnas tengamos en la sentencia SELECT.
  • Cada variable guardará el dato de la columna que venga en el SELECT, de tal forma que la primera variable guardará el dato de la primera columna, la segunda variable, el dato de la segunda columna y así sucesivamente.
  • Cada variable debe estar definida con el mismo tipo de dato que la columna a la que está asociada.


Esta es la parte que va a necesitar de un bucle, en el que vamos 'fila a fila' recorriendo el resultado de ejecutar la sentencia SELECT que define el cursor.
El paso de una fila a la siguiente se realiza con la sentencia FETCH. Veamos en que consiste:
Como vemos tenemos que realizar un bucle, empleando cualquiera de las formas vistas en esta Wiki.


Nota: En SQLServer los conceptos son los mismos que estamos viendo. La diferencia es la forma en cómo SQLServer testea el fin de datos en el cursor, ya que chequea la función @@FETCH_STATUS como condición para salir del bucle.
Podéis consultar un ejemplo de uso en este enlace.


Siguiendo nuestro ejemplo:
 1 DECLARE v_noHayMasDatos INT DEFAULT FALSE;
 2 DECLARE v_apellidos varchar(100);
 3 DECLARE v_nombre varchar(45);
 4 
 5 DECLARE c_artistas CURSOR FOR 
 6   SELECT apellidos,nombre FROM ARTISTAS WHERE nif_jefe IS NULL;
 7 
 8 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_noHayMasDatos = TRUE;
 9 
10 OPEN c_artistas;
11 
12 read_loop: LOOP
13     FETCH c_artistas INTO v_apellidos,v_nombre
14 
15     IF v_noHayMasDatos THEN
16       LEAVE read_loop;
17     END IF;
18 
19     -- Por cada fila disponemos del apellido y nombre. Ahora podemos utilizar estos datos para lo que queramos
20 END LOOP;
Líneas 2,3: Definimos las variables que van a guardar los datos de cada fila. Los tipos de datos se corresponden con los tipos definidos en la tabla ARTISTAS para las columnas apellidos y nombre.
Líenas 12-20: Bucle con la orden FETCH y la condición de salida del bucle al terminar de recorrer las filas.
NOTA IMPORTANTE: Al estar dentro de un bucle debemos de asegurarnos que la condición de salida siempre se alcanza ya que sino estaríamos en un bucle infinito.
Debemos asegurarnos que el FETCH está dentro del bucle ya que es la orden que va a provocar la excepción y va a permitir salir del bucle.


Ahora dentro del bucle podemos hacer lo que queramos con los datos que se encuentran en las variables v_apellidos y v_nombre, por ejemplo


  • CIERRE DEL CURSOR: Para liberar de la memoria el cursor creado.
1 CLOSE nombre_cursor;


Siguiendo nuestro ejemplo:
 1 DECLARE v_noHayMasDatos INT DEFAULT FALSE;
 2 DECLARE v_apellidos varchar(100);
 3 DECLARE v_nombre varchar(45);
 4 
 5 DECLARE c_artistas CURSOR FOR 
 6   SELECT apellidos,nombre FROM ARTISTAS WHERE nif_jefe IS NULL;
 7 
 8 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_noHayMasDatos = TRUE;
 9 
10 OPEN c_artistas;
11 
12 read_loop: LOOP
13     FETCH c_artistas INTO v_apellidos,v_nombre
14 
15     IF v_noHayMasDatos THEN
16       LEAVE read_loop;
17     END IF;
18 
19     -- Por cada fila disponemos del apellido y nombre. Ahora podemos utilizar estos datos para lo que queramos
20 END LOOP;
21 
22 CLOSE c_artistas;




  • Indicar que no es obligatorio el uso de LOOP y podremos hacer uso de REPEAT o WHILE, pero teniendo en cuenta que al realizar el último FETCH capturamos la excepción y debemos de adaptar el comportamiento a cada tipo de bucle.
Mod BD Prog Cursor 8.jpg



Ejercicios propuestos Cursores

  • Ejercicio 1: Crea un procedimiento de nombre atracciones_checkGanancias en el que queremos comprobar si las ganancias totales de cada atracción coinciden con la suma de las ganancias de los días en los que se celebró. El procedimiento debe devolver una cadena con el formato: atraccion1:gananciatotal:gananciasumada, atraccion2:gananciatotal:gananciasumada con las atracciones que no cumplen que la suma sea igual...



  • Ejercicio 2: Crea un procedimiento de nombre artistas_addSuplementoPorCuidados, que compruebe a cuantos animales cuida cada uno de los artistas. Aquellos artistas que cuidan más de un número de animales indicados por un parámetro se les dará un plus a su nómina igual al número de animales que cuida multiplicado por 100 euros. Muestra el nombre y complemento de cada artista así como la suma de todos los complementos.
El resultado debe aparecer como una única consulta (no valen varios SELECT).
Para ello haz uso de una tabla temporal que vaya guardando los datos (el nombre completo y el suplemento de la ganancia) y posteriormente haz un SELECT de dicha tabla. Para crear una tabla temporal haz uso de la sentencia CREATE TEMPORARY TABLE. Dicha orden debe ir después de la orden DECLARE CONTINUE HANDLER del cursor.
Recuerda borrar la tabla temporal al salir del procedimiento con la orden DROP TEMPORARY TABLE.



Debe mostrar en una única consulta (un único SELECT) la lista de pistas y nuevo aforo a partir de la información que devuelve el procedimiento animales_AddAforo. Las pistas no pueden aparecer repetidas.
Para ello haz uso de una tabla temporal que vaya guardando los datos (el nombre completo y el suplemento de la ganancia) y posteriormente haz un SELECT de dicha tabla. Para crear una tabla temporal haz uso de la sentencia CREATE TEMPORARY TABLE. Dicha orden debe ir después de la orden DECLARE CONTINUE HANDLER del cursor.
Recuerda borrar la tabla temporal al salir del procedimiento con la orden DROP TEMPORARY TABLE.
Si habéis seguido esta wiki, fijarse que existe un Trigger (pistas_checkAforo_UPDATE) que comprueba que el aforo esté entre 10 y 1000.
Si intentáis ejecutar el procedimiento la atracción SUPER está en 1000 por lo que dará un error. Sin embargo, el resto de atracciones pudieron ser actualizadas.
Si queremos que esto no pase será necesario hacer uso de las transacciones, como veremos posteriormente en esta Wiki. Para probar recordar bajar el valor del aforo de la pista SUPER para que no supere el límite.



  • Ejercicio 4 (sin solución): Crea un procedimiento de nombre artistas_getJefes que muestre a cada artista (nif) con su jefe (apellidos,nombre). En caso de que no tenga jefe deberá mostrar la cadena SIN JEFE. Recuerda hacer uso del a función IFNULL. Este procedimiento se podría realizar sin hacer uso de cursores, pero para practicar, vas a crear un curso que recorra todos los artistas y por cada artista que busque su jefe.





Solución Ejercicios propuestos Cursores

  • Ejercicio 1: Crea un procedimiento de nombre atracciones_checkGanancias en el que queremos comprobar si las ganancias totales de cada atracción coinciden con la suma de las ganancias de los días en los que se celebró. El procedimiento debe devolver una cadena con el formato: atraccion1:gananciatotal:gananciasumada, atraccion2:gananciatotal:gananciasumada con las atracciones que no cumplen que la suma sea igual...
 1 USE CIRCO;
 2 DROP PROCEDURE IF EXISTS atracciones_checkGanancias;
 3 
 4 DELIMITER $$
 5 CREATE PROCEDURE atracciones_checkGanancias()
 6 COMMENT 'Devuelve las atracciones cuya suma total de ganancias no coincide con la suma de las ganancias diarias.'
 7 BEGIN
 8     -- Declaración de variables
 9     DECLARE v_final INTEGER DEFAULT 0;
10     DECLARE v_atraccion varchar(50);
11     DECLARE v_ganTotales int;
12     DECLARE v_ganTotalesPorDia int;
13     DECLARE v_cadenaSalida varchar(1000) default '';	-- Cuidado con el valor (null) por defecto para concatenar.
14     
15     -- Declaración del cursor
16     DECLARE c_checkGanancias CURSOR FOR 
17         SELECT nombre, ganancias
18         FROM ATRACCIONES;
19 
20     DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_final = TRUE;
21     OPEN c_checkGanancias;
22 
23     read_loop: LOOP
24         FETCH c_checkGanancias INTO v_atraccion,v_ganTotales;
25 
26         IF v_final THEN
27             LEAVE read_loop;
28         END IF;
29         
30         SELECT SUM(ganancias)
31         INTO v_ganTotalesPorDia
32         FROM ATRACCION_DIA
33         WHERE nombre_atraccion = v_atraccion;
34 
35         IF (v_ganTotalesPorDia<>v_ganTotales) THEN
36             SET v_cadenaSalida = IF (v_cadenaSalida<>'',CONCAT(v_cadenaSalida,', '),'');	-- Para separar una atracción de otra
37             SET v_cadenaSalida = CONCAT(v_cadenaSalida,v_atraccion,':',v_ganTotales,':',v_ganTotalesPorDia);
38         END IF;
39 
40 
41     END LOOP;
42 
43     CLOSE c_checkGanancias;   
44     SELECT v_cadenaSalida as listaatracciones;
45 END$$
46 DELIMITER ;
Ejemplo de llamada:
1 call atracciones_checkGanancias();



  • Ejercicio 2: Crea un procedimiento de nombre artistas_addSuplementoPorCuidados, que compruebe a cuantos animales cuida cada uno de los artistas. Aquellos artistas que cuidan más de un número de animales indicados por un parámetro se les dará un plus a su nómina igual al número de animales que cuida multiplicado por 100 euros. Muestra el nombre y complemento de cada artista así como la suma de todos los complementos.
El resultado debe aparecer como una única consulta (no valen varios SELECT).
Para ello haz uso de una tabla temporal que vaya guardando los datos (el nombre completo y el suplemento de la ganancia) y posteriormente haz un SELECT de dicha tabla. Para crear una tabla temporal haz uso de la sentencia CREATE TEMPORARY TABLE. Dicha orden debe ir después de la orden DECLARE CONTINUE HANDLER del cursor.
Recuerda borrar la tabla temporal al salir del procedimiento.
 1 USE CIRCO;
 2 DROP PROCEDURE IF EXISTS artistas_addSuplementoPorCuidados;
 3 
 4 DELIMITER $$
 5 CREATE PROCEDURE artistas_addSuplementoPorCuidados(p_numAnimales tinyint)
 6 COMMENT 'Muestra un suplemento para aquellos artistas que cuidan a más animales de lo indicado así como la suma de los complementos'
 7 BEGIN
 8     -- Declaración de variables
 9     DECLARE v_final INTEGER DEFAULT 0;
10     DECLARE v_nif CHAR(9);
11     DECLARE v_numAnimales TINYINT default 0;
12     DECLARE v_complementoTotal INT DEFAULT 0;
13     DECLARE v_apellidos VARCHAR(100);
14     DECLARE v_nombre VARCHAR(45);
15 
16     -- Declaración del cursor
17     DECLARE c_complemento CURSOR FOR 
18         SELECT nif_artista,COUNT(*)
19         FROM ANIMALES_ARTISTAS
20         GROUP BY nif_artista
21         HAVING COUNT(*) > p_numAnimales;
22         
23     DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_Final = TRUE;
24 
25     CREATE TEMPORARY TABLE T_TEMPORAL (nombre_completo varchar(150), suplemento decimal(6,2));
26     OPEN c_complemento;
27 
28 
29     read_loop: LOOP
30         FETCH c_complemento INTO v_nif, v_numAnimales;
31         IF v_final THEN
32             LEAVE read_loop;
33         END IF;
34         
35         IF (v_numAnimales > p_numAnimales) THEN
36             SELECT apellidos, nombre
37             INTO v_apellidos, v_nombre
38             FROM ARTISTAS
39             WHERE nif = v_nif;
40             
41             INSERT INTO T_TEMPORAL 
42             VALUES (CONCAT(v_apellidos,', ',v_nombre),v_numAnimales*100);
43             SET v_complementoTotal = v_complementoTotal + v_numAnimales*100;
44             
45         END IF;
46 
47     END LOOP;
48 
49     INSERT INTO T_TEMPORAL 
50     VALUES ('Suplemento total',v_complementoTotal);
51 
52     SELECT nombre_completo, suplemento
53     FROM T_TEMPORAL;
54 
55     CLOSE c_complemento;   
56     
57     DROP TEMPORARY TABLE T_TEMPORAL;
58 
59 END$$
60 DELIMITER ;
Ejemplo de llamada:
1 call artistas_addSuplementoPorCuidados(2);



Debe mostrar en una única consulta (un único SELECT) la lista de pistas y nuevo aforo a partir de la información que devuelve el procedimiento animales_AddAforo. Las pistas no pueden aparecer repetidas.
Para ello haz uso de una tabla temporal que vaya guardando los datos (el nombre completo y el suplemento de la ganancia) y posteriormente haz un SELECT de dicha tabla. Para crear una tabla temporal haz uso de la sentencia CREATE TEMPORARY TABLE. Dicha orden debe ir después de la orden DECLARE CONTINUE HANDLER del cursor.
Recuerda borrar la tabla temporal al salir del procedimiento con la orden DROP TEMPORARY TABLE.
Si habéis seguido esta wiki, fijarse que existe un Trigger (pistas_checkAforo_UPDATE) que comprueba que el aforo esté entre 10 y 1000.
Si intentáis ejecutar el procedimiento la atracción SUPER está en 1000 por lo que dará un error. Sin embargo, el resto de atracciones pudieron ser actualizadas.
Si queremos que esto no pase será necesario hacer uso de las transacciones, como veremos posteriormente en esta Wiki. Para probar recordar bajar el valor del aforo de la pista SUPER para que no supere el límite.


 1 USE CIRCO;
 2 DROP PROCEDURE IF EXISTS animales_addAforoPorTipo;
 3 
 4 DELIMITER $$
 5 CREATE PROCEDURE animales_addAforoPorTipo(p_tipo varchar(9), p_incAforo smallint)
 6 COMMENT 'Hace uso del método animales_AddAforo para incrementar el aforo de las pistas donde trabajan los animales del tipo indicado'
 7 BEGIN
 8     -- Declaración de variables
 9     DECLARE v_final INTEGER DEFAULT 0;
10     DECLARE v_nombrePista VARCHAR(50);
11     DECLARE v_nuevoAforo SMALLINT;
12 
13     -- Declaración del cursor
14     DECLARE c_pistas CURSOR FOR 
15         SELECT DISTINCT nombre_pista
16         FROM ANIMALES
17         WHERE tipo = p_tipo;
18         
19     DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_Final = TRUE;
20 
21     CREATE TEMPORARY TABLE T_TEMPORAL (nombrePista varchar(50), nuevoAforo SMALLINT);
22     OPEN c_pistas;
23 
24 
25     read_loop: LOOP
26         FETCH c_pistas INTO v_nombrePista;
27         IF v_final THEN
28             LEAVE read_loop;
29         END IF;
30         
31         SET v_nuevoAforo = p_incAforo;
32         CALL pistas_addAforo(v_nombrePista,v_nuevoAforo);	-- En v_nuevoAforo está el aforo incrementado
33         
34         INSERT INTO T_TEMPORAL (nombrePista,nuevoAforo)
35         VALUES (v_nombrePista,v_nuevoAforo);
36     
37     END LOOP;
38 
39     SELECT nombrePista,nuevoAforo
40     FROM T_TEMPORAL
41     ORDER BY nombrePista;
42 
43     CLOSE c_pistas;   
44     
45     DROP TEMPORARY TABLE T_TEMPORAL;
46 
47 END$$
48 DELIMITER ;


Ejemplo de llamada:
1 call animales_addAforoPorTipo('León',2);







Enlace a la página principal de la UD7

Enlace a la página principal del curso




-- Ángel D. Fernández González -- (2020).