BD UD7 Cursores
Revisión del 09:24 4 may 2020 de Wiki (discusión | contribuciones) (→Solución Ejercicios propuestos Cursores)
Sumario
Introducción
- Nota: Todos los ejemplos están basados en esta base de datos.
- Más información en este enlace-
- 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.
- Notas:
- 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.
- Notas:
- 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.
- El proceso para implementar un cursor siempre es el mismo, por lo que podemos hacer uso de un Snippets como ya vimos en la sección de documentación de esta Wiki.
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.
- Ejercicio 3: Crea un procedimiento de nombre animales_addAforoPorTipo al que se le pase un tipo de animal y el incremento de aforo y por cada pista en la que trabaje el animal de ese tipo, se llame al procedimiento pistas_addAforo ya implementado en la sección de procedimientos de esta Wiki. No se debe de llamar varias veces a la misma pista.
- 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);
- Ejercicio 3: Crea un procedimiento de nombre animales_addAforoPorTipo al que se le pase un tipo de animal y el incremento de aforo y por cada pista en la que trabaje el animal de ese tipo, se llame al procedimiento pistas_addAforo ya implementado en la sección de procedimientos de esta Wiki. No se debe de llamar varias veces a la misma pista.
- 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).