Rutinas, ejercicio 5.

5.     Crea una rutina que elimine, de la tabla “acumulado”, los datos correspondientes al año en el que se ejecuta la rutina y, posteriormente, inserte en dicha tabla los puntos acumulados en ese año por cada uno de los tipos de sanción.

Para ello, utiliza un cursor que recorra los registros de “tipoSancion” y, por cada uno de ellos, ejecute una consulta con la que se inserte en la tabla “acumulado” los puntos acumulados por dicho tipo de sanción: 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `puntosPorTipoSancion`()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE v_idTipoSancion, sumaPuntos INT;

  DECLARE cur CURSOR FOR SELECT idtipoSancion FROM tipoSancion;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

         DELETE FROM acumulado WHERE anyo = YEAR(CURRENT_DATE);

  OPEN cur;

  read_loop: LOOP

    FETCH cur INTO v_idTipoSancion;

    IF done THEN

      LEAVE read_loop;

    END IF;

         SELECT SUM(puntos) INTO sumaPuntos FROM sancion

         WHERE idtipoSancion = v_idTipoSancion AND YEAR(fecha) = YEAR(CURRENT_DATE);

         INSERT INTO acumulado VALUES (YEAR(CURRENT_DATE), v_idTipoSancion, sumaPuntos);

  END LOOP;

  CLOSE cur;

END

Tabla de referencia:

Rutinas, ejercicio 4.

4.     Crea un script que ejecute el procedimiento anterior el día 1 de enero de cada año.

CREATE EVENT   regalarPuntosAnyo

    ON SCHEDULE EVERY 1 YEAR

         STARTS '2014-01-01 00:00:01'

    DO CALL regalarPunto;

Podéis comprobar la creación correcta del evento con SHOW EVENTS

Tabla de referencia:

Rutinas, ejercicio 3.

3.   Crea un script en el que se le otorgue un punto extra a aquellos conductores que no han tenido ninguna sanción en el año anterior al que se ejecuta el script.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `regalarPunto`()

BEGIN

          UPDATE conductor SET puntos = puntos + 1

         WHERE dni NOT IN (

                     SELECT DISTINCT conductor_dni FROM vehiculo,sancion

                     WHERE vehiculo.matricula = vehiculo_matricula AND

                     fecha BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR) AND CURRENT_DATE

         );

END $$

DELIMITER $$

Resultado: La llamada a la rutina debe indicar que se actualizan 78 filas

Tabla de referencia:

Rutinas, ejercicio 2.

2.     Crea un script para que, cada vez que se inserta una sanción, se le reste automáticamente, al conductor del vehículo denunciado, los puntos correspondientes a dicha sanción.

USE dgt;

DELIMITER $$

CREATE TRIGGER afterInsertSancion AFTER INSERT

    ON sancion FOR EACH ROW

BEGIN

         DECLARE v_dni CHAR(9);

         SELECT conductor_dni INTO v_dni FROM vehiculo WHERE matricula = new.vehiculo_matricula;

          UPDATE conductor SET puntos = puntos - new.puntos

         WHERE dni = v_dni;

   

END$$

DELIMITER ;

Resultado:

INSERT INTO `dgt`.`sancion` (`vehiculo_matricula`, `puntos`, `idtipoSancion`) VALUES ('7592HFT', 2, 3);

Debe hacer que el conductor con DNI '56953022N' pase de tener 12 puntos a tener 10

Tabla de referencia:

Rutinas, ejercicio 1.

1.     Crea un script al que le enviamos un DNI de un conductor y nos devuelve la suma de puntos que ha perdido debido a sanciones: 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `totalPuntosConductor`(v_dni char(10)) RETURNS int(11)

BEGIN

         DECLARE v_puntos INT;

         SELECT SUM(puntos) INTO v_puntos FROM sancion, vehiculo

         WHERE vehiculo_matricula = matricula AND

         conductor_dni = v_dni;

         RETURN v_puntos;

END $$

DELIMITER;

Resultado:

La llamada a la rutina con el dni '95270924K' debe devolver un valor de 5

Tabla de referencia:

Rutinas almacenadas para la administración de mysql (CURSORES).

Los CURSOR o "cursores", almacenan los registros devueltos por una sentencia sql y recorren su contenido.

Ejemplo de sintaxis de un cursor:

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE a CHAR(16);

  DECLARE cur CURSOR FOR SELECT i FROM tablaPrueba;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP

    FETCH cur INTO a;

    IF done THEN

      LEAVE read_loop;

    END IF;

       ….

  END LOOP;

  CLOSE cur;

END;

Explicación traducida:

CREAR PROCEDURE demo_cursor()

COMENZAR

  DECLARAR hecho ENTERO FALSO POR DEFECTO;

  DECLARAR a CARACTERES(16);

  DECLARAR cursor CURSOR PARA SELECCIONAR i DESDE tablaPrueba;

  DECLARAR CONTINUAR CONTROLADOR PARA SET NO ENCONTRADO hecho = VERDADERO;

  ABRIR cursor;

  leer_bucle: BUCLE

    BUSQUEDA cursor DENTRO DE a;

    SI hecho ENTONCES

      DEJAR leer_bucle;

    FIN DEL IF;

       ….

  FIN DEL BUCLE ;

  CIERRE DEL cursor;

FIN;

Las partes escritas en mayúsculas, deben ir así siempre al realizar la sentencia correctamente. 

Rutinas almacenadas para la administración de mysql (EVENTOS)

Los EVENT, "eventos" o disparadores temporales, son rutinas asociadas a un esquema determinado y las cuales son ejecutadas con los permisos del propio creador del evento.

Se ejecutan para intervalos de tiempo con el comando EVERY, y para un instante con el comando AT.

Los eventos están disponibles en la versión 5.1 de Mysql.

Ejemplo sintaxis:

CREATE EVENT   event_name

    ON SCHEDULE schedule

    DO event_body;

schedule:

    AT timestamp [+ INTERVAL interval] ...

  | EVERY interval

    [STARTS timestamp [+ INTERVAL interval] ...]

    [ENDS timestamp [+ INTERVAL interval] ...]

interval:

    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Explicación traducida:

CREAR EVENTO   nombre_evento

    EN HORARIO horario

    REALIZAR cuerpo_evento;

horario:

    EN timestamp [+ INTERVALO intervalo] ...

  | CADA intervalo

    [COMIENZA timestamp [+ INTERVALO intervalo] ...]

    [FINALIZA timestamp [+ INTERVALO intervalo] ...]

intervalo:

    cantidad {AÑO | Cuartos | mes | día | Hora | MINUTO |

               SEMANA | SEGUNDO | MES AÑO | HORAS DEL DÍA | MINUTO día |

               SEGUNDO DIA | MINUTO HORA | SEGUNDA HORA | MINUTO SEGUNDO}

Las partes escritas en mayúsculas, deben ir así siempre al realizar la sentencia correctamente.