MySQL: read-only cursors
Contingut
Introducció
Treballem amb la base de dades HR.
Si volem incrementar el salari de tots els empleats un 10% és tan senzill com fer:
mysql> update employees set salary=salary*1.1;
En el cas de què vulguis fer aquest update i tornar a deixar la taula com estava, ho pots fer de la següent manera:
start transaction; #comencem una transacció update employees set salary=salary*1.1; #fem el update select * from employees; #veiem els canvis que s'han realitzat rollback; #tirem endarrere els canvis
Ara bé, si volem incrementar el salari un 10% els empleats dels empleats que tenen un fill no és tan senzill. Podem intentar:
$ update employees set salary=salary*1.1 where employee_id IN (select e.employee_id from employees e INNER JOIN dependents d USING(employee_id)); ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clause
És a dir, no podem fer el update en el mateix moment que estem fent una select amb la mateixa taula. Això és així per seguretat, i no ho podem fer pel mateix motiu que no podem fer cursors actualitzables. A MySQL els cursors són read-only.
El que sí que funciona és fer una vista, i actualitzar sobre la vista:
create view empleats_amb_fills as select e.employee_id,e.first_name,e.last_name,salary from employees e INNER JOIN dependents d USING(employee_id); update empleats_amb_fills set salary=salary*1.1; select * from empleats_amb_fills
A Oracle existeixen els CURSOR FOR UPDATE, però a MySQL els cursors són read only. A Oracle podem estudiar el següent exemple:
En aquest exemple (llenguatge PL/SQL d'Oracle) llegim en un cursor la taula customers, i a mida que recorrem (LOOP) els resultats actualitzem el camp credit_limit. Això no ho podem fer a MySQL. Anem a veure una alternativa per poder fer-ho.
Solució alternativa per fer cursors actualitzables
Per solucionar aquest tema, el que farem és generar les sentències udpate que ens permetran actualitzar els valors, a posteriori. Generarem una llista de comandes update, que les volcarem per la pantalla o a un fitxer.
Treballem amb la base de dades HR. Comencem per un exemple senzill i després farem un exemple més complicat.
NOTA: per tal de visualitzar de forma clara el resultat, convé utilitzar l'opció silent: -ss
$ mysql -u alumne -pkeiL2lai -ss HR
Primer exemple
procediment modificarSalari:
DROP PROCEDURE IF EXISTS modificarSalari;
DELIMITER $$
CREATE PROCEDURE modificarSalari (
)
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE vemployee_id INT;
DECLARE vsalary DECIMAL(8,2);
DEClARE curEmpleats CURSOR FOR select e.employee_id,salary from employees e INNER JOIN dependents d USING(employee_id);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN curEmpleats;
getEmpleats: LOOP
FETCH curEmpleats INTO vemployee_id, vsalary;
IF finished = 1 THEN
LEAVE getEmpleats;
END IF;
SELECT CONCAT('update employees set salary=',vsalary*1.1,' where employee_id=',vemployee_id,';');
END LOOP getEmpleats;
CLOSE curEmpleats;
END$$
DELIMITER ;
CALL modificarSalari();
I amb això el que obtenim és una llista de sentències SQL que ens serveixen per actualitzar el salari dels empleats que tenen fills:
update employees set salary=26400.000 where employee_id=100; update employees set salary=18700.000 where employee_id=101; update employees set salary=18700.000 where employee_id=102; update employees set salary=9900.000 where employee_id=103; update employees set salary=6600.000 where employee_id=104; update employees set salary=5280.000 where employee_id=105; update employees set salary=5280.000 where employee_id=106; ...
Segon exemple
Ara el que farem és implementar una regla més complicada: incrementarem el sou d'un empleat seguint aquestes dues regles:
1) s'incrementa el sou amb el % relatiu de la diferència entre el max_salary i el min_salary del job al qual pertany l'empleat. Per exemple, per al cas del job Stock Manager, la diferència entre 8550-5500 és 3000, i s'incrementa el sou en un (3000/8500)*100 = 35,29%.
2) sobre aquest valor, s'incrementa el sou un 10% si l'empleat té un fill.
Primer de tot treballem la consulta que ens dona informació sobre el salari min i màx del job de l'empleat, i de si aquest té o no té fills:
select e.employee_id, e.job_id, e.salary, min_salary, max_salary,d.employee_id from employees e INNER JOIN jobs j USING(job_id) LEFT OUTER JOIN dependents d ON e.employee_id=d.employee_id;
proceciment modificarSalari_v2:
DROP PROCEDURE IF EXISTS modificarSalari_v2;
DELIMITER $$
CREATE PROCEDURE modificarSalari_v2 (
)
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE vemployee_id INT;
DECLARE vsalary DECIMAL(8,2);
DECLARE vsalary_new DECIMAL(8,2);
DECLARE vjob_salary_min DECIMAL(8,2);
DECLARE vjob_salary_max DECIMAL(8,2);
DECLARE vfill DECIMAL(8,2);
DECLARE inc DECIMAL (4,2);
DEClARE curEmpleats CURSOR FOR select e.employee_id, e.salary, min_salary, max_salary,d.employee_id from employees e INNER JOIN jobs j USING(job_id) LEFT OUTER JOIN dependents d ON e.employee_id=d.employee_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN curEmpleats;
getEmpleats: LOOP
FETCH curEmpleats INTO vemployee_id, vsalary, vjob_salary_min, vjob_salary_max, vfill;
IF finished = 1 THEN
LEAVE getEmpleats;
END IF;
SET inc = (vjob_salary_max-vjob_salary_min)/vjob_salary_max;
SET vsalary_new = vsalary*(1+inc);
IF vfill IS NOT NULL THEN
SET vsalary_new = vsalary_new*1.1;
END IF;
#SELECT vsalary;
SELECT CONCAT('update employees set salary=',vsalary_new,' where employee_id=',vemployee_id,'; # ', vsalary);
END LOOP getEmpleats;
CLOSE curEmpleats;
END$$
DELIMITER ;
CALL modificarSalari_v2(); update employees set salary=13968.90 where employee_id=206; # 8300.00 update employees set salary=19668.00 where employee_id=205; # 12000.00 update employees set salary=7260.00 where employee_id=200; # 4400.00 update employees set salary=39600.00 where employee_id=100; # 24000.00 update employees set salary=28050.00 where employee_id=101; # 17000.00 update employees set salary=28050.00 where employee_id=102; # 17000.00 update employees set salary=15147.00 where employee_id=109; # 9000.00 update employees set salary=13800.60 where employee_id=110; # 8200.00 update employees set salary=12959.10 where employee_id=111; # 7700.00 update employees set salary=13127.40 where employee_id=112; # 7800.00 update employees set salary=11612.70 where employee_id=113; # 6900.00 update employees set salary=19668.00 where employee_id=108; # 12000.00 update employees set salary=11154.00 where employee_id=203; # 6500.00 update employees set salary=15840.00 where employee_id=103; # 9000.00 update employees set salary=10560.00 where employee_id=104; # 6000.00 update employees set salary=8448.00 where employee_id=105; # 4800.00 update employees set salary=8448.00 where employee_id=106; # 4800.00 update employees set salary=7392.00 where employee_id=107; # 4200.00 update employees set salary=20020.00 where employee_id=201; # 13000.00 update employees set salary=10296.00 where employee_id=202; # 6000.00 update employees set salary=17270.00 where employee_id=204; # 10000.00 update employees set salary=5285.50 where employee_id=115; # 3100.00 update employees set salary=4944.50 where employee_id=116; # 2900.00 update employees set salary=4774.00 where employee_id=117; # 2800.00 update employees set salary=4433.00 where employee_id=118; # 2600.00 update employees set salary=4262.50 where employee_id=119; # 2500.00 update employees set salary=17787.00 where employee_id=114; # 11000.00 update employees set salary=23100.00 where employee_id=145; # 14000.00 update employees set salary=22275.00 where employee_id=146; # 13500.00 update employees set salary=14190.00 where employee_id=176; # 8600.00 update employees set salary=12600.00 where employee_id=177; # 8400.00 update employees set salary=10500.00 where employee_id=178; # 7000.00 update employees set salary=9300.00 where employee_id=179; # 6200.00 update employees set salary=6200.00 where employee_id=192; # 4000.00 update employees set salary=6045.00 where employee_id=193; # 3900.00 update employees set salary=4320.00 where employee_id=126; # 2700.00 update employees set salary=10800.00 where employee_id=120; # 8000.00 update employees set salary=11070.00 where employee_id=121; # 8200.00 update employees set salary=10665.00 where employee_id=122; # 7900.00 update employees set salary=8775.00 where employee_id=123; # 6500.00
Tasques a realitzar
Treballaràs dins del docker (recorda d'utilitzar l'opció -ss).
$ docker start mysql_m02bd
$ docker exec -it mysql_m02bd mysql -u alumne -pkeiL2lai -ss HR
'''1.''' En el teu docker (base de dades HR), crearàs i executaràs el segon exemple (procediment ''modificarSalari_v2''). Aquest procediment genera una llista per pantalla de les comandes (''updates'') que hauràs d'executar.
'''2.''' En comptes de generar la sortida per pantalla, generaràs un fitxer que contingui els scripts a executar. Necessites les següents comandes:
<pre>
$ mysql> help
...
notee (\t) Don't write into outfile.
system (\!) Execute a system shell command.
source (\.) Execute an SQL script file. Takes a file name as an argument.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
...
mysql> \T script_updates.sql Logging to file 'script_updates.sql' mysql> CALL modificarSalari_v2(); mysql> \t Outfile disabled.
Comprovem el contingut del script que hem generat (ha de contenir tots els updates)
mysql> system cat script_updates.sql mysql> select employee_id, salary from employees where employee_id=206;
I ara ja podem executar aquest script:
mysql> source script_updates.sql ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> CALL modificarSalari_v2()' at line 1 Outfile disabled. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql>' at line 1
no fem cas als 2 errors, són deguts a la primera línia i a la última línia que han quedat gravats en el script.
Comprovem que s'ha fet el canvi:
mysql> select employee_id, salary from employees where employee_id=206;
3. Restauració de les dades (des del docker, mysql). En la carpeta etc/ tens el fitxer HR.sql
mysql> source /etc/HR.sql mysql> select employee_id, salary from employees where employee_id=206; 206 8300.00
que és la dada original.
4. Restauració de les dades (des del docker, prompt del SO)
$ docker start mysql_m02bd mysql_m02bd $ docker exec -it mysql_m02bd /bin/bash bash-4.4# ls /etc/HR.sql
Per importar el script farem:
bash-4.4# mysql -h localhost -u alumne -pkeiL2lai -D HR < /etc/HR.sql
Per comprovar quin és el salari del employee_id=206:
bash-4.4# mysql -h localhost -u alumne -pkeiL2lai -D HR -ss -e "select employee_id, salary from employees where employee_id=206" 206 8300.00
Entrega
(TBD)
creat per Joan Quintana Compte, abril 2022
