Ejercicios correspondientes a la hoja número siete. Se han realizado sobre el script primerospasos.sql, que puedes descargar haciendo clic aquí
Ejercicio 27. Realizar
un procedimiento que actualice el salario de los empleados, con una cantidad
por hijo que se pase al procedimiento, para aquellos trabajadores que su comisión es
nula. Durante el proceso de actualización debe garantizarse que ningún otro
usuario pueda cambiar los datos que están siendo modificados. Utilizar un
manejador de tipo SQLEXCEPTION (que incluya la operación ROLLBACK) para tratar
cualquier situación de error distinta de la excepción NOT FOUND que también
deberá manejarse. Al final del proceso,
confirmar la transacción si la ejecución ha sido correcta.
Ejemplo de funcionamiento. Antes de llamar al procedimiento
de actualización:
+-------+---------+----------+-------+
|
numem | salario | comision | numhi |
+-------+---------+----------+-------+
|
110 | 310 | NULL
| 3 |
|
120 | 350 | 110
| 1 |
|
130 | 290 | 110
| 2 |
|
150 | 440 | NULL
| 0 |
|
160 | 310 | 110
| 2 |
|
180 | 480 | 50
| 2 |
|
190 | 300 | NULL
| 4 |
…
….
Después de ejecutar CALL EJERCICIO3_1(30);
+-------+---------+----------+-------+
|
numem | salario | comision | numhi |
+-------+---------+----------+-------+
|
110 | 400 | NULL
| 3 |
|
120 | 350 | 110
| 1 |
|
130 | 290 | 110
| 2 |
|
150 | 440 | NULL
| 0 |
|
160 | 310 | 110
| 2 |
|
180 | 480 | 50
| 2 |
|
190 | 420
| NULL | 4 |
...
...
delimiter $$
drop procedure if exists veintisiete $$
create procedure veintisiete(in subidaSueldo int, out
numError int, out textoError varchar(100))
modifies sql data
begin
declare
ultimaFila int default 0;
declare
vEmpleado int;
declare
vHijos int;
declare emp
cursor for
select
numem, numhi
from
empleados
where
comision is null and numhi > 0
for
update;
declare
continue handler for not found
begin
set ultimaFila = 1;
rollback; -- Deshago cambios de la
transacción
end;
declare
continue handler for sqlexception
begin
set numError = -1;
set textoError = 'Ocurrió un error
durante la consulta';
rollback;
end;
set numError = 0; -- Inicio numero de error
a 0
start transaction;
open emp;
empleadosCursor:
loop
fetch
emp into vEmpleado, vHijos;
if ultimaFila = 1 then -- Controlo lectura de
cursor
leave empleadosCursor;
end if;
update empleados
set salario = salario +
(subidaSueldo * vHijos)
where numem = vEmpleado;
end loop
empleadosCursor;
close emp;
if numError
= 0 then
set textoError = 'Consulta de actualización
realizada correctamente';
commit;
end if;
end $$
delimiter ;
Ejercicio 28. “Representación gráfica de los salarios de los
empleados”. Realizar un procedimiento que utilice un cursor de actualización
para rellenar la columna estrellas de la tabla empleados con un asterisco por
cada 100 unidades de salario. Antes de realizar el ejercicio añade la columna
estrellas a la tabla empleados (ALTER TABLE empleados ADD COLUMN estrellas
VARCHAR(10)). Incluir los dos manejadores de error del ejercicio anterior.
Ejemplo de funcionamiento:
+-------+---------+-----------+
|
NUMEM | SALARIO | ESTRELLAS |
+-------+---------+-----------+
|
110 | 400 | ****
|
|
120 | 350 | ***
|
|
130 | 290 | **
|
|
150 | 440 | ****
|
|
160 | 310 | ***
|
|
180 | 480 | ****
|
|
190 | 420 | ****
|
|
210 | 440 | ****
|
|
240 | 280 | **
|
|
250 | 450 | ****
|
|
260 | 900 | ********* |
|
270 | 380 | ***
|
...
...
alter table empleados add column estrellas varchar(10); --
Añado columna para las estrelllas
delimiter $$
drop procedure
if exists veintiocho $$
create
procedure veintiocho(out numError int, out textoError varchar(100))
begin
-- Declaración de
variables
declare ultimaFila
int default 0;
declare vEmpleado
int;
declare vSalario
int;
declare vEstrellas
int;
declare contador
int;
declare calidad
varchar(10);
-- Declaración de
cursor
declare emp cursor for
select numem, salario
from empleados
for update;
-- Declaracion de
control de errores
declare continue handler for not found
set ultimaFila = 1;
declare continue handler for sqlexception
begin
set
numError = -1;
set
textoError = 'Ocurrió un error al lanzar procedimiento';
rollback;
end;
set numError = 0;
-- Inicialización de errores a 0
-- Inicio
transacción
start transaction;
open emp;
salarioEmpleados:
loop
fetch emp
into vEmpleado, vSalario;
if
ultimaFila = 1 then
leave
salarioEmpleados;
end if;
set vEstrellas = vSalario / 100;
if vEstrellas > 10 then
set
vEstrellas = 10; -- La valoración de salario máximo es diez
end if;
set contador
= 0; -- Reinicio contador a 0 para cada empleado
while contador < vEstrellas do
if calidad is null then
set
calidad = '*'; -- Si valor nulo inicializo variable
else
set
calidad = concat(calidad, '*'); -- Si no concateno
end
if;
set
contador = contador + 1; -- Incremento en uno el contador aux
end while;
update empleados
set estrellas = calidad
where numem = vEmpleado;
set calidad = null; --
Vuelvo valor de calidad a nulo para el siguiente empleado
end loop salarioEmpleados;
close emp;
-- Si no ha ocurrido ningún error,
valido la operación
if numError = 0 then
set textoError = 'Operación
completada';
commit;
end if;
end $$
delimiter ;
Ejercicio 29. Realizar
un procedimiento que teniendo en cuenta el procedimiento transac6 del apartado 6.10.5 de los materiales implemente otro
procedimiento similar pero utilizando la estrategia optimista, que asume que es
muy poco probable que el valor de una fila que se acaba de leer con intención
de modificarla un tiempo más tarde cambie antes de poder hacerlo; en ese caso
como mínimo habrá que asegurarse de que la fila no ha sido modificada después
de haber sido leída y si así ha sido entonces la transacción no debe llevarse a
cabo aun pudiéndose realizar (ROLLBACK).
delimiter $$
drop procedure if exists veintinueve $$
create procedure veintinueve(pId int, pAlumno
varchar(30), out numError int, out textoError varchar(100))
modifies sql data
begin
declare vAlumno varchar(30);
declare vAlumno2 varchar(30);
declare continue handler for
sqlexception
begin
set
numError = -1;
set
textoError = 'Ocurrió un error';
rollback;
end;
set numError
= 0;
start
transaction;
select
alumno
into vAlumno
from alumnos
where id = pId;
select sleep(15) into sleep; -- Espacio
de tiempo que queda esperando
select alumno
into vAlumno2
from alumnos
where id = pId
for
update;
if vAlumno = vAlumno2 then
update alumnos
set alumno = pAlumno
where
id = pId;
if
numError = 0 then
commit;
else
rollback;
end
if;
else
rollback;
end if;
end $$
delimiter ;
0 comentarios:
Publicar un comentario