Area para GBD06

EJERCICIO 1

Crea un procedimiento que muestre los vehículos (marca, modelo y color) que no estén reparados y los datos de los clientes y vehículos que han entrado a reparar hoy. (En nuestro caso ninguno).

DELIMITER |

CREATE PROCEDURE proc1()

BEGIN

SELECT marca, modelo, color FROM vehiculos INNER JOIN reparaciones USING (matricula) WHERE reparado=0;

SELECT * FROM clientes INNER JOIN vehiculos USING (codcliente) INNER JOIN reparaciones USING (matricula) WHERE fechaentrada=CURDATE();

END |

DELIMITER ;

EJERCICIO 2

• Realiza un procedimiento que reciba la matrícula de un vehículo y escriba las características del automóvil y el número de reparaciones que ha sufrido ese automóvil, los empleados que han realizado esas reparaciones y los datos de los vehículos de la misma marca.

DELIMITER |

CREATE PROCEDURE proc2(IN x varchar(8))

BEGIN

DECLARE y varchar(30);

SELECT marca,modelo,color,count(reparado) as 'Num_reparaciones'

FROM vehiculos INNER JOIN reparaciones USING (matricula)

WHERE reparado=1 AND matricula=x;

SELECT empleados.* FROM reparaciones INNER JOIN intervienen USING (idreparacion) INNER JOIN empleados USING (codempleado)

WHERE matricula=x GROUP BY codempleado;

SELECT * FROM vehiculos where marca=(SELECT marca FROM vehiculos WHERE matricula=x);

END |

DELIMITER ;

Hacer una llamada al procedimiento creado.

CALL proc2('1313 DEF')

EJERCICIO 3

Modifica el procedimiento anterior añadiendo un HANDLER que controle que si esa matrícula no está en la base de datos, el resto de instrucciones no se ejecuten.

Añadimos el siguiente handler :

DECLARE EXIT HANDLER FOR SQLSTATE '02000' SELECT 'Sin datos';

EJERCICIO 4

Crea una función que actualice el estado de las reparaciones que estén finalizadas en una fecha que se indique y que devuelva cuantas reparaciones han finalizado en esa fecha.

DELIMITER |

DROP FUNCTION IF EXISTS fun1 |

CREATE FUNCTION fun1(f date)

RETURNS int

BEGIN

DECLARE x int;

UPDATE reparaciones SET reparado='1' WHERE fechasalida=f;

SELECT count(reparado) as finalizadas into x FROM reparaciones WHERE fechasalida=f and reparado=1;

return x;

END |

DELIMITER ;

LLAMADA→  SELECT fun1(‘2011-01-04’);

RESULTADO→ 3

EJERCICIO 5

Crea un procedimiento para dar de alta una nueva reparación para un vehículo y un cliente que no tenemos registrado. Llama al procedimiento ReparacionClienteNuevo.

Incluye un HANDLER que controle que si insertamos un cliente y/o un vehículo que ya existen, el resto de sentencias continúen ejecutándose, y se añade como mínimo la nueva reparación.

Para probar el procedimiento toma como referencia los datos siguientes: (tomados de un ejercicio de la unidad anterior)

Un cliente nuevo nos ha traído su vehículo al taller el día 03/03/2011. En recepción se registran los siguientes datos:

• Del cliente.- Código: 00011, Nombre y apellidos: Tomás Gómez Calle, Teléfono: 22334455.
• Del vehículo.- Matrícula: 3131 FGH, Modelo: Renault Scénic, matriculado el 17/03/2009, 105.000 km;
• De la reparación.- Sustitución de las lámparas delanteras.

DELIMITER |

DROP PROCEDURE IF EXISTS ReparacionClienteNuevo|

CREATE PROCEDURE ReparacionClienteNuevo(codcli varchar(5),nombre varchar(25),apellidos varchar(50),telefono varchar(9),matricula varchar(8),modelo varchar(50),marca varchar(25),fmatricula date,kms int(10),averia varchar(200))

BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT 'Cliente o vehiculos duplicado';

INSERT INTO clientes VALUES (codcli,null,apellidos,nombre,NULL,telefono);

INSERT INTO vehiculos VALUES (matricula,marca,modelo,null,fmatricula,codcli,kms);

INSERT INTO reparaciones VALUES (null,matricula,curdate(),kms,averia,null,0,null);

END |

DELIMITER ;

llamada PROCEDIMIENTO→

CALL ReparacionClienteNuevo('00011','Tomás','Gómez Calle','22334455','3131 FGH','Scénic','Renault','2009-03-17',105000,'Sustitución de las lámparas delanteras')

El resultado es que como existe tanto el cliente como el vehículo solo ejecutaría el alta en la reparación.

EJERCICIO 6

Creación de funciones:

1. Diseña una función que calcule el importe de los recambios sustituidos en una reparación.

DELIMITER |

DROP FUNCTION IF EXISTS calcula_importe|

CREATE FUNCTION calcula_importe(idrep int)

RETURNS double

BEGIN

DECLARE importe_recambios double;

SELECT sum(precioreferencia)into importe_recambios  FROM reparaciones INNER JOIN incluyen USING (idreparacion) INNER JOIN recambios USING (idrecambio)

WHERE idreparacion=idrep;

RETURN importe_recambios;

END |

DELIMITER ;

1. Crea una función que devuelva el importe de las actuaciones que se llevan a cabo en una reparación (para calcular el importe multiplica las horas por el importe de cada actuación)..

DELIMITER |

DROP FUNCTION IF EXISTS calcula_importe_actu|

CREATE FUNCTION calcula_importe_actu(idrep int)

RETURNS double

BEGIN

DECLARE importe_actuaciones double;

SELECT sum(importe*horas)into importe_actuaciones FROM reparaciones INNER JOIN realizan USING (idreparacion) INNER JOIN actuaciones USING (referencia)

WHERE idreparacion=idrep;

RETURN importe_actuaciones;

END |

DELIMITER ;

En ambas funciones Pasar como variable el Id de la reparación.

1. Diseñar una consulta que calcule el importe total (mano de obra y recambios) de las reparaciones que se le hayan realizado al vehículo de matrícula '1313 DEF'.

DELIMITER |

DROP FUNCTION IF EXISTS total_final|

CREATE FUNCTION total_final(w varchar(8))

RETURNS double

BEGIN

DECLARE final,idrep int default 0;

DECLARE valor double default 0;

DECLARE x,y double default 0;

DECLARE cursor_1 CURSOR FOR

SELECT idreparacion FROM reparaciones where matricula=w;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET final = 1;

OPEN cursor_1;

bucle:LOOP

        FETCH cursor_1 into idrep;

        IF final<>1 THEN

                SELECT calcula_importe_actu(idrep) into x;

                SELECT calcula_importe(idrep) into y;

                SET valor=valor+x+y;

        ELSE

                LEAVE bucle;

        END IF;

END LOOP bucle;

CLOSE cursor_1;

RETURN valor;

END |

DELIMITER ;

EJERCICIO 7

Crea una función que reciba como parámetro de entrada el número correspondiente a un mes y devuelva el importe total facturado ese mes. Utiliza para ello las dos funciones obtenidas en la práctica anterior.

NOTAS:

• Por ejemplo para Enero, número del mes 1.
• Utilizar un cursor para recorrer cada fila de la consulta de los IdReparacion que se obtengan en ese mes.
• Controla mediante un HANDLER que la consulta haya devuelto alguna fila.

DELIMITER |

DROP FUNCTION IF EXISTS total_mes|

CREATE FUNCTION total_mes(i int)

RETURNS double

BEGIN

DECLARE final,idrep int default 0;

DECLARE valor double default 0;

DECLARE x,y double;

DECLARE cursor_2 CURSOR FOR

SELECT idreparacion FROM reparaciones WHERE month(fechaentrada)=i;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET final = 1;

OPEN cursor_2;

bucle:LOOP

IF final<>1 THEN

                FETCH cursor_2 into idrep;

                SELECT calcula_importe_actu(idrep) into x;

                SELECT calcula_importe(idrep) into y;

                SET valor=valor+x+y;

        ELSE

                LEAVE bucle;

        END IF;

END LOOP bucle;        

CLOSE cursor_2;

RETURN valor;

END |

DELIMITER ;

EJERCICIO 8

Crea un trigger que, antes de insertar una fila en la tabla Incluyen, compruebe si existen unidades en Stock en la tabla RECAMBIOS llevando a cabo las siguientes acciones:

• Si hay suficientes unidades actualiza el Stock restando las unidades que se van a insertar.
• Si no hay suficientes unidades en Stock cancela la inserción de las unidades.

DELIMITER |

DROP TRIGGER IF EXISTS tr1|

CREATE TRIGGER tr1 BEFORE INSERT ON talleresfaber.incluyen FOR EACH ROW

BEGIN

DECLARE x int default 0;

SELECT stock into x FROM recambios WHERE idrecambio=new.idrecambio;

IF x>0 THEN

       UPDATE recambios SET stock=stock-1 WHERE idrecambio=new.idrecambio;

END IF;

END |

DELIMITER ;

EJERCICIO 9

1. Crea una tabla denominada PedidoRecambios que contenga 3 columnas:

• IdRecambio.
• Descripcion.
• Stock.

Con los mismos tipos de datos que tienen esas columnas en la tabla RECAMBIOS.

CREATE TABLE pedidos_recambios(

idrecambio varchar(10) not null primary key,

descripcion        varchar(100),

stock smallint(6) not null

);

1. Crea un trigger asociado a la tabla RECAMBIOS que después de actualizar el Stock de un recambio, si el número de unidades en Stock del recambio modificado es inferior a 4 unidades, inserte una fila en la tabla PedidoRecambios con los datos resultantes.

DELIMITER |

DROP TRIGGER IF EXISTS tr2|

CREATE TRIGGER tr2 AFTER UPDATE ON talleresfaber.recambios FOR EACH ROW

BEGIN

DECLARE x int default 0;

SELECT stock into x FROM recambios WHERE idrecambio=new.idrecambio;

IF x<4 then

INSERT INTO pedidos_recambios VALUES (new.idrecambio,new.descripcion,new.stock);

END IF;

END |

DELIMITER ;

EJERCICIO 10

Utilizando funciones de librerías disponibles en MySQL obtener:

1. Un listado con dos columnas: en la primera, en mayúsculas apellidos y nombre de todos los clientes (entre los apellidos y el nombre incluir una coma como separador) y en la segunda, la ciudad en la que cada cliente tiene su domicilio (únicamente la ciudad, no la dirección).

SELECT CONCAT(apellidos,',',nombre) as Nombre, SUBSTRING_INDEX(direccion, ',', -1) as Ciudad FROM clientes;

1. Un listado con 2 columnas: en la primera la fecha de alta de los empleados con el formato dd/mm/aaaa y en la segunda aparecerá 'Contrato temporal ' para aquellos empleados que lleven contratados en el taller menos de 2 años, y 'Contrato fijo' para el resto.

SELECT DATE_FORMAT(fechaalta,'%d/%m/%Y') as fecha_alta, if(DATEDIFF(curdate(),fechaalta)>=730,'Contrato Fijo','Contrato Temporal') as tipo_contrato  from empleados;