Bases de datos. SQL programado (VIII). Ejercicios (III)

Ejercicios de la hoja 3 de los temas 6/7.

Ejercicio 19. (usar primeros_pasos.sql - descarga aquí) Realiza un procedimiento que liste cada departamento junto con sus empleados de forma similar a cómo se ha hecho en el ejercicio cursor5 del apartado 6.2 del tema (en este último caso para centros junto con sus departamentos). Se listarán los datos de aquellos Departamentos cuyo número esté comprendido entre los dos números de departamento que se pasen como argumento al procedimiento. El formato y un ejemplo de ejecución (se han suprimido parte de la salida como los mensajes de filas devueltas)

drop procedure if exists diecinueve;

delimiter $$

create procedure diecinueve(in min integer, in max integer)

begin

     declare ultimaFila integer default 0; -- Variable para control de errores

     declare vNumde integer;

     declare vSalario integer;

     declare vComision integer;

     declare vNomem varchar(18);

     declare cEmpleados cursor for -- Declaro cursor para los campos de la siguiente consulta (hasta el where)

         select nomem, numde, salario, comision

         from empleados

         where numde between min and max;

     declare continue handler for not found set ultimaFila = 1; -- Control de error 1329

     open cEmpleados; -- Abro el cursor

         empleadosCursor: loop

              fetch cEmpleados into vNomem, vNumde, vSalario, vComision; -- Guardo los campos de fila en esas variables

              if ultimaFila = 1 then -- Si se ha llegado a la última fila, doy salida al bucle

                   leave empleadosCursor;

              end if;

              select  vNomem as 'Empleado', vNumde as 'Departamento', vSalario as 'Salario', vComision as 'Comision', vSalario+ifnull(vComision,0) as 'Salario total';

         end loop empleadosCursor; -- finalizo el bucle

     close cEmpleados; -- Cierro el cursor

end $$

delimiter ;

Ejercicio 20. (usar cargatablas.sql - descargar aquí) Escribir un procedimiento que suba el sueldo de todos los empleados que ganen menos que el salario medio de su oficio. La subida será del 50% de la diferencia entre el salario del empleado y la media de su oficio. La transacción no deberá quedarse a medias.

delimiter $$

drop procedure if exists veinte $$

create procedure veinte()

begin

     declare sentencia varchar(255);

     declare ultimaFila int default 0;

     declare empleado int;

     declare vOficio varchar(255);

     declare vSalario int;

     declare salarioMedio int;

     declare subida int;

     declare cEmp cursor for

         select emp_no, oficio, salario

         from empleados e

         where salario < (

              select avg(salario)

              from empleados

              where oficio = e.oficio

         )

         for update; -- Cursor de actualización que bloquea las operaciones desde otra terminal hasta que ésta se realice

     declare continue handler for not found set ultimaFila = 1;

     start transaction; -- Comienzo de la transacción para verificar que se realizan todos los pasos

         open cEmp;

              cursorEmpleados: loop

                   fetch cEmp into empleado, vOficio, vSalario;

                   if ultimaFila = 1 then

                        leave cursorEmpleados;

                   end if;

                   set salarioMedio = (

                        select avg(salario)

                        from empleados

                        where oficio = vOficio

                   );

                   set subida = (salarioMedio - vSalario) / 2; -- 50% de la diferencia entre ambos salarios

                   update empleados set salario = salario + subida where emp_no = empleado;

              end loop cursorEmpleados;

         close cEmp;

     commit; -- Final de la transacción

end $$

delimiter ;