Rutinas, ejercicio 8.

8.   Simular un autoincrementado. Rutina para asignar un IdPedido automáticamente a cada nuevo Pedido que no tenga un valor explícito para el campo IdPedido. El valor que se le debe asignar al IdPedido debe ser el entero inmediatamente superior al mayor valor de dicho campo en la tabla Pedidos.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` TRIGGER Before_Insert_Pedidos

BEFORE INSERT ON Pedidos

FOR EACH ROW

BEGIN

         DECLARE v_maxIdPedido INT;

         IF new.IdPedido IS NULL OR new.IdPedido=0 THEN

                     SELECT MAX(IdPedido) INTO v_maxIdPedido FROM Pedidos;

                    

                     SET new.IdPedido = v_maxIdPedido + 1;

         END IF;

END $$

DELIMITER ;

Tabla de referencia:

Rutinas, ejercicio 7.

7.   Rutina para actualizar automáticamente el IdPedido de la tabla DetallesPedidos en el caso de que se modifique el IdPedido de algún registro de la tabla Pedidos.

use Neptuno;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` TRIGGER After_Update_Pedidos

AFTER UPDATE ON Pedidos

FOR EACH ROW

BEGIN

         IF old.IdPedido != new.IdPedido THEN

                     UPDATE DetallesPedidos SET IdPedido = new.IdPedido

                                 WHERE IdPedido = old.IdPedido;

         END IF;

END $$

DELIMITER ;

Tabla de referencia:

Rutinas, ejercicio 6.

6. Rutina que calcula el valor de la comisión que le corresponde al empleado que ha gestionado un pedido concreto.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `calculaComisionPorPedido`(v_idPedido INT) RETURNS float

BEGIN

         DECLARE v_idEmpleado, v_comision INT;

         DECLARE v_comisionPorPedido, v_totalPedido FLOAT;

         -- Seleccionamos el idEmpleado de ese pedido

         SELECT IdEmpleado INTO v_idEmpleado FROM Pedidos

         WHERE IdPedido = v_idPedido;

         -- Seleccionamos el porcentaje de comisión asociada a ese empleado

         SELECT Comision INTO v_comision FROM Empleados

         WHERE IdEmpleado = v_idEmpleado;

         -- Calculamos la cuantía total del pedido

         SELECT totalPedido(v_idPedido) INTO v_totalPedido;

         -- Calculamos cuánto supone el porcentaje de comisión de esa cuantía

         SELECT v_totalPedido * v_comision / 100 INTO v_comisionPorPedido;

RETURN v_comisionPorPedido;

END

$$

DELIMITER ;

Tabla de referencia:

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.

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

Los trigger o "disparadores", son procedimientos que no usan sql dinámico, no devuelven ningún valor.

Los trigger están asociados a eventos de tablas del tipo insert, delete y update, y dependen del evento NEW y OLD.

Ejemplo de sintaxis de un trigger:

CREATE TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_body

Explicación traducida:

CREAR DISPARADOR nombre_disparador tiempo_disparador evento_disparador

    EN nombre_tabla PARA CADA FILA cuerpo_disparador

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

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

Las function o "funciones", son procedimientos que no usan sql dinámico, devuelven un valor de tipo dado, pero no un conjunto de estos.

Solo aceptan parámetros de entrada.

Ejemplo de sintaxis de una función:

CREATE

    [DEFINER = { user | CURRENT_USER }]

    FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    routine_body

func_parameter:

    param_name type

type:

    Any valid MySQL data type

routine_body:

    Valid SQL routine statement

Explicación traducida:

CREAR

    [DEFINER = { usuario | USUARIO_ACTUAL }]

    FUNCIÓN nombre_sp ([parametro_función[,...]])

    DEVOLUCIONES tipo

    cuerpo_rutina

parametro_función:

  nombre tipo_parametro

tipo:

    Cualquier tipo de datos MySQL válida

cuerpo_rutina:

    Declaración de rutina de SQL válido

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

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

Los procedures o procedimientos, son rutinas que tienen la ventaja de poder utilizar sql dinámico.

Los procedures, utilizan parámetros de entrada, salida o entrada-salida y devuelven uno o varios datos.

Para invocar un procedure usamos el comando call.

Ejemplo de sintaxis de un procedure:

CREATE

    PROCEDURE sp_name ([proc_parameter[,...]])

    routine_body

proc_parameter:

    [ IN | OUT | INOUT ] param_name type

type:

    Any valid MySQL data type

routine_body:

    Valid SQL routine statement

Explicación traducida:

CREAR

    PROCEDIMIENTO sp_name ([parametro_procedimiento[,...]])

    cuerpo_rutina

parámetros_procedimiento:

    [ ENTRADA | SALIDA |  ENTRADA/SALIDA] parametro_nombre tipo

tipo:

    Cualquier tipo de datos MySQL válida

cuerpo_rutina:

   Declaración de rutina de SQL válido


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

TIPOS DE DATOS EN SQL

BINARY	1 byte.	Para consultas sobre tabla adjunta de productos de bases de datos que definen un tipo de datos Binario.
BIT	1 byte.	Valores Si/No ó True/False.
BYTE	1 byte.	Un valor entero entre 0 y 255.
COUNTER	4 bytes.	Un número incrementado automáticamente (de tipo Long).
CURRENCY	8 bytes.	Un entero escalable entre 922.337.203.685.477,5808 y 922.337.203.685.477,5807.
DATETIME	8 bytes.	Un valor de fecha u hora entre los años 100 y 9999.
SINGLE	4 bytes.	Un valor en punto flotante de precisión simple con un rango de - 3.402823*1038 a -1.401298*10-45 para valores negativos, 1.401298*10- 45 a 3.402823*1038 para valores positivos, y 0.
DOUBLE	8 bytes.	Un valor en punto flotante de doble precisión con un rango de - 1.79769313486232*10308 a -4.94065645841247*10-324 para valores negativos, 4.94065645841247*10-324 a 1.79769313486232*10308 para valores positivos, y 0.
SHORT	2 bytes.	Un entero corto entre -32,768 y 32,767.
LONG	4 bytes.	Un entero largo entre -2,147,483,648 y 2,147,483,647.
LONGTEXT	1 byte por carácter.	De cero a un máximo de 1.2 gigabytes.
LONGBINARY	Según se necesite.	De cero 1 gigabyte. Utilizado para objetos OLE.
TEXT	1 byte por carácter.	De cero a 255 caracteres.

TIPOS DE CAMPOS EN SQL

Alfanuméricos: Contienen cifras y letras. Presentan una longitud limitada (255 caracteres)

Numéricos: Existen de varios tipos, principalmente, enteros (sin decimales) y reales (con decimales).

Boléanos: Poseen dos formas: Verdadero y falso (Sí o No).

Fechas: Almacenan fechas facilitando posteriormente su explotación. Almacenar fechas de esta forma posibilita ordenar los registros por fechas o calcular los días entre una fecha y otra.

Memos: Son campos alfanuméricos de longitud ilimitada. No pueden ser indexados. 

Autoincrementables: Son campos numéricos enteros que incrementan en una unidad su valor para cada registro incorporado. Su utilidad resulta más que evidente: Servir de identificador ya que resultan exclusivos de un registro.

RELACIONES

Relación uno a muchos (1,n). Se crea, si uno de los campos relacionados es una clave primaria. Cada campo de la tabla puede estar ligado a varios campos de otra tabla y cada campo de la segunda sólo estará ligado a un único campo de la primera tabla. Este tipo de relación es la más usada. 

Relación uno a uno (1,1). Se crea cuando varios campos relacionados son claves primarias. En estas relaciones, un campo de la tabla estará relacionado con otro de la segunda tabla y al revés.

Relación muchos a muchos (n,m). No existe clave primaria. Los campos de la primera tabla podrán estar ligados con varios campos de la segunda y al revés. Esto supone la repetición de los campos de cada una de las tablas. Para ello es necesario crear una tabla intermedia que esté relacionada con las dos de uno a varios.