6-7 practica 3

Puedes acceder a la teoría necesaria para hacer los ejercicios,  a los ejercicios y las bases de datos necesarias aquí, aquí, aquí y aquí.

19.-
/* 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. */
delimiter $$
drop procedure if exists ejercicio19 $$
create procedure ejercicio19(in e_numde_1 int, in e_numde_2 int)
begin
declare v_numde int;
declare v_nomde varchar(20);
declare v_nomem varchar(18);
declare v_salario int;
declare v_comision int;
declare v_ultima_fila int default 0;
declare v_total_empleado int;
declare v_total_sueldo int;
declare c_departamentos cursor for
select numde, nomde
from departamentos
where numde>=e_numde_1 
and numde<=e_numde_2
order by numde;
declare c_empleados cursor for
select nomem, salario, comision
from empleados
where numde=v_numde;
declare continue handler for not found 
set v_ultima_fila=1;
open c_departamentos;
cursor_departamentos: loop
fetch c_departamentos into v_numde, v_nomde;
if v_ultima_fila=1 then
leave cursor_departamentos;
end if;
select concat('departamento: ',v_numde,' - ',v_nomde) as 'datos departamentos';
set v_total_empleado=0;
set v_total_sueldo=0;
open c_empleados;
cursor_empleados: loop
fetch c_empleados into v_nomem, v_salario, v_comision;
if v_ultima_fila=1 then
leave cursor_empleados;
end if;
select concat('empleado: ',v_nomem,' - salario: ',v_salario,' - comision: ',
ifnull(v_comision, 0),' - total salario: ',(v_salario+ifnull(v_comision,0))) as 'datos empleados';
set v_total_empleado=v_total_empleado+1;
set v_total_sueldo=v_total_sueldo+(v_salario+ifnull(v_comision,0));
end loop cursor_empleados;
close c_empleados;
select concat('empleados: ',v_total_empleado,' - total sueldo: ',v_total_sueldo) as 'totales departamentos';
set v_ultima_fila=0;
end loop cursor_departamentos;
close c_departamentos;
end $$
delimiter ;   

20.-

/* 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 ejercicio20 $$

create procedure ejercicio20()

begin

declare v_emp_no int(4);

declare v_salario_medio float(6,2);

declare v_ultima_fila int default 0;

declare c_empleados cursor for

select emp_no

from empleados e1

where salario < (select avg(SALARIO)

from empleados

where oficio = e1.oficio)

for update;

declare continue handler for not found 

set v_ultima_fila=1;

start transaction;

open c_empleados;

empleados_cursor: loop

fetch c_empleados into v_emp_no;

if v_ultima_fila=1 then

leave empleados_cursor;

end if;

select avg(salario)

into v_salario_medio

from empleados

where oficio = 

(select oficio

from empleados

where emp_no = v_emp_no);

update empleados

set salario = salario + 0.5*(v_salario_medio-salario)

where emp_no = v_emp_no;

end loop empleados_cursor;

close c_empleados;

commit;

end $$

delimiter ;