MySQL: read-only cursors

De wikijoan
Salta a la navegació Salta a la cerca

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

Són 4 exercicis. 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:

$ 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

Entregaràs dins d'un pdf les captures de pantalla i comentaris suficients per demostrar que has fet l'exercici de forma autònoma en el docker de la teva màquina. Dins del temps previst.


creat per Joan Quintana Compte, abril 2022